Macro/easier way to filter and update

[ncbf87]

Hi guys,

Hope someone can help/inspire me for an easier way to do this.

I've 2 tables. Table 1 being client's data and Table 2 being standard codes used

We have a standard set of job titles and its respective codes (e.g. CEO = "0001") assigned for standardization purpose. And companies have different job titles for the same job (e.g IT Helpdesk = IT Support = Support Helpdesk = IT analyst).

Whilst I rely on clients to code the jobs correctly according to our manual (table 2), some may be wrong/updated/outdated/not filled.

What I'm doing quarterly using the dump of 400,000 data lines is to filter jobs and assign the standard codes (e.g. filter for all CEO/Chief Executive/Chief Executive Officer and assign "0001" to it if it's not already "0001").

Is there a way to automate this process?

I'm thinking along the lines of creating a form to enter possible job title/keywords to search, with anything that matches these criteria get assigned a code in the job title column.

Or even if this is easier in Microsoft Access, do advice.

Thanks guys!

Table 1
Company	Job Title	Job Code	Level
Company A	Accountant III		15
Company A	Junior Accountant		13
Company A	Senior Auditor		15
Company A	Junior Tax Accountant		14
Company B	Tax Analyst I		13
Company B	Financial Analyst I		15
Company C	CSR Advisor		16
Company C	Community Manager		15
Company C	Analyst, treasury		15
Company C	Mgr, Payroll		15

<tbody>
</tbody>

Table 2 standard codes
Job Title	Code	Level
Accountant III	1005	15
Accountant I	1008	13
Auditor II	1015	15
Tax Accountant I	1021	15
Tax Analyst I	1023	13
Financial Analyst I	1028	15
Corporate Social Responsibility Specialist	1843	15
Community Manager	1844	15
Treasury Analyst	5670	15
Payroll Manager	5675	15

<tbody>
</tbody>

 

[SQUIDD]

Hi

If i read it correctly, you simply want to put your standard codes into you list?

so you could use a formula in this case, several options should work.

I have assumed table 2 is in the range j1:l11 and table 1 is in a1:d11. expand as required.

in c2 put =IFNA(INDIRECT("k" &MATCH(B2,J:J,0)),"")

then copy this formula down the lenght of your data.

If i understand you correctly, this should work.

Dave

 

[ncbf87]

Hi

If i read it correctly, you simply want to put your standard codes into you list?

so you could use a formula in this case, several options should work.

I have assumed table 2 is in the range j1:l11 and table 1 is in a1:d11. expand as required.

in c2 put =IFNA(INDIRECT("k" &MATCH(B2,J:J,0)),"")

then copy this formula down the lenght of your data.

If i understand you correctly, this should work.

Dave

Hi SQUIDD,

Thanks for helping to think, however it's not just a simple match. Another example is an Account Manager is a standard job title. However in 100 companies, it can be called differently e.g. Account Mgr, Account Manager., Key Account Sales, Account director, etc.

What I always did previously is did a search of all possible job title I know, which in this case is "Account" using filter, and fill in the job code for a standard Account Manager.

OR Billing and Credit control analyst, in other companies are called billing analyst, credit control, credit analyst, credit collection officer, etc. I'll find a standard keyword to filter, and re-code it.

Hope that helps clarify,

Thanks!

 

[SQUIDD]

ok

Im with you now.

So you would really like to enter a list of possible job titles or a list of keywords and filter for them.
Then, everytime it hits a match, apply the code.

I think the code to do so will be easy to make, but it really depends how you want to go about it.

Would you not like to create a new sheet and build to the list of different job titles, in case new ones get added

so in the new sheet, in coulumn A for example.

in a1 you would have the job code and below would be a list of possible job titles.

same for B,C,D and so of for all the different jobs.

This way you can simply add a new title as and when, we would build the code to always include the additions.

Im guessing also, you can look for any new names after the code has run as any new names would not recieve a code(filter for blanks)

not sure how you wish to go about it all, but im happy to help you get there.

Dave

 

[ncbf87]

ok

Im with you now.

So you would really like to enter a list of possible job titles or a list of keywords and filter for them.
Then, everytime it hits a match, apply the code.

I think the code to do so will be easy to make, but it really depends how you want to go about it.

Would you not like to create a new sheet and build to the list of different job titles, in case new ones get added

so in the new sheet, in coulumn A for example.

in a1 you would have the job code and below would be a list of possible job titles.

same for B,C,D and so of for all the different jobs.

This way you can simply add a new title as and when, we would build the code to always include the additions.

Im guessing also, you can look for any new names after the code has run as any new names would not recieve a code(filter for blanks)

not sure how you wish to go about it all, but im happy to help you get there.

Dave

Hi Dave,

challenge with that is there're over 5000 standard unique job codes. If there're a possible 5 different titles/keywords for each, then that's 25,000. LOL

But anyways let's say we're to go with your idea, and every quarter I start with 50 unique jobs (God knows how many quarters it'll take to build a full list), how should I go about it.
1. there will be a file of unique job codes, and under each possible combination of keywords,
2. what formula to use to lookup that 1 code for that job that matches?

Thanks!

 

[SQUIDD]

Yes

I see your point, unaware of how many you had.

So this must take you absolutly ages to do manually.

so what do you normally do, filter out the word accounant and then apply the code manually??

Do you supply your clients with templates to fill out? or do they export their lists from other programs(just curious)

Dave

 

[ncbf87]

Yes

I see your point, unaware of how many you had.

So this must take you absolutly ages to do manually.

so what do you normally do, filter out the word accounant and then apply the code manually??

Do you supply your clients with templates to fill out? or do they export their lists from other programs(just curious)

Dave

Hi Dave,

We can start with 50 per quarter, because there're lots of variable it becomes a big challenge to make it perfect I do understand.

Normally I'll see what jobs are either in-demand, or rare. And will focus on those to get it re-coded. Actually there's another criteria to it which is levels, but this can be easily applied later once I get my head around that 1st criteria.
1. the dump will include employee salary
2. the dump is to create a market of salary (think linked-in, etc.) where employers will utilize to compare salary for different roles.
3. so if codes are wrong, then market is unreliable. As much as there will be errors and will never be 100% correct,I'm doing my best to reduce the error.

Clients have a manual to code jobs that they download from their HR system, but 50% of them do not update it every year, some others don't fill at all (but we still need the data so we code it for them), and some others can just be wrong coding.

Every quarter I will spend few days just on re-coding, whatever jobs that come to mind, and where requested.

e.g. Electrician, I will filter any job title that contains "Electri",
e.g. Human Resource Business Partner, I will filter any job title that contains "HRBP", "HR Business Partner", "Human Resource Business Partner", "HR Advisor", "Human Resource Advisor", etc.

 

[SQUIDD]

Ok, i see it will be almost impossible without human intervention then.

so back to you original ask.

you want an easier way to enter a key word and assign a code.

This could be done with a userform with perhaps a combo box finding unique values with your range and another combo box with all your codes, at which point you press a button that codes them all.

or as simple as the code below.

Sub find_key_words()
lastrow = Range("B" & Rows.Count).End(xlUp).Row ' change column  "B" to suit your range
keyword = InputBox("enter key word")
code = InputBox("enter code to apply")
    For a = 2 To lastrow
        If Range("b" & a) Like "*" & keyword & "*" Then ' change "b" to suit your range
            Range("c" & a) = code ' change "c" to suit you range where the code will be put
        End If
    Next
End Sub

let me know

Dave