Cleaning Up Data with Multiple Variation

[bertique]

Hi Guys,
Really appreciate your help on the issue that I am facing when working with my dataset.

There are variations of my current data in one column (as below). And I need to segregate the column between the Title (AM1) with the Role (Role AAAA etc). Tried using the text to column function, but due to the variation of entries formatting and Title convention - can't do it.

AM 1 ROLE AAAA
AM 2 ROLE AAA
AM 2 ROLE B
AM 2 ROLE B
AM 2, ROLE BBBBBB
AM 2-ROLE BBBB
AM 2,ROLE BB
AM 2 - ROLE

The outcome intended from above is as below (in 2 different column)

Title Role
AM1 ROLE AAAA
AM2 ROLE AAA
AM2 ROLE B
AM2 ROLE B
AM2 ROLE BBBBBB
AM2 ROLE BBBB
AM2 ROLE BB
AM2 ROLE
ACTING HEAD ROLE A
ACTING HEAD ROLE A
ACTING HEAD ROLE B
ACTING HEAD ROLE B
ACTING HEAD ROLE B
ACTING HEAD ROLE B
ACTING HEAD ROLE B
ACTING HEAD ROLE
SVP ROLE A
SVP ROLE A
SVP ROLE B
SVP ROLE B
SVP ROLE B
SVP ROLE B
SVP ROLE B
SVP ROLE

Thanks in advance for your help.

Cheers
Bert.

[humdingaling]

put formula in C3 and fill down

Please Login or Register  to view this content.

[bertique]

it works if all role start with "role" ...
what if we have different naming convention for the role?

ACTING HEAD HR
ACTING HEAD IT
ACTING HEAD COMPLIANCE
ACTING HEAD FINANCE
ACTING HEAD, OPS
ACTING HEAD-SALES
ACTING HEAD,BRANCH
ACTING HEAD - FINANCE SOLUTIONS

[humdingaling]

then update your example file accordingly to eliminate me guessing

[humdingaling]

something like thread should be what your after
http://www.excelforum.com/excel-form...ml#post4309348

[bertique]

ok have updated the dataset to reflect the situation.
the link that you provided were useful, but didn't address my issue. (thanks for giving the link).

[humdingaling]

changed your example to what was mentioned in the other post

even me reading your list i do not know all the desired outcomes without making some assumptions.....
remember the computer can only apply rules you put in place it does not make assumptions for you

[bertique]

thanks, i think i can use this as stop gap measure. it is just that a lot of works needed to be done to establish the "directory" as per your solution.

my apologies for not being clear/descriptive enough.

if you look at my example, there a few ways of writing the position title - position title is a combination of grade (AM2, etc) with title (projct admin etc). In my example, there are few scenarios - space in between AM and the number e.g. AM 2; no space in between title e.g. AM1; comma in between title and "outcome needed" e.g. AM1, Project Admin. The data was generated from the system which has many stakeholder i.e. different ways of writing the position title for employees.

my requirement/outcome needed:
1. how do i omit the different variation of "grade" in the position title and take only the position title? your example works, but we are talking at 15-20K entries, which i think would be hard to keep on updating the "directory"

thanks for your helps.

[humdingaling]

i think you already know what the problem with this

The data was generated from the system which has many stakeholder i.e. different ways of writing the position title for employees.

letting users free form text without any structure leads to you having to "dictionary" plausible outcomes
with no set way of entering data people are left to do whatever they want for position

essentially what you are after is some smart parsing. this generally requires some sort of logic
however it eludes me what logic can be applied to your data set as there is little to no framework on what can be entered and what defines a "grade"

again some sort of "directory" or dictionary to parse data would be required
http://www.datasciencecentral.com/pr...-big-data-sets
(the programming language used in this article is not excel but the principle is still the same)

you may not go with the type of dictionary i had in my previous example
you can go with "what to exclude" ie Grade
list of every variation you can think of for manager, mgr, assistant mgr, am, am1, am 1, etc


ps you probably should spell check the whole list first
ASISTANT MANAGER MARKETING
ASSISTANT BRANCH MANAGER

additional reading on techniques you can use
https://support.office.com/en-us/art...f-9cfc08a0a4e3

this is an exhaustive tasks that happens quite often
more often then not, people just manually brute force it once and use that as a master file
the more you can group things into manageable chunks the better/easier it becomes

[bertique]

thanks a lot humdingaling!

the link on data directory/dictionary is really useful. it might be pain the back, but i guess i may have to resort to this (because i can't control the entry point to the system). not the direct solution to my problem, but it was a useful one. and i learn more than just a solution from the references/sharing that you put up in this thread.

will consider this thread as solved.

thank you for your help. really appreciate it.