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="width: 410"]
<tbody>[TR]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]Job Title[/TD]
[TD]Job Code[/TD]
[TD]Level[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]Accountant III[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]Junior Accountant[/TD]
[TD][/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]Senior Auditor[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]Junior Tax Accountant[/TD]
[TD][/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]Tax Analyst I[/TD]
[TD][/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]Financial Analyst I[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]CSR Advisor[/TD]
[TD][/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]Community Manager[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]Analyst, treasury[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Company C[/TD]
[TD]Mgr, Payroll[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 410"]
<tbody>[TR]
[TD]Table 2 standard codes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job Title[/TD]
[TD]Code[/TD]
[TD]Level[/TD]
[/TR]
[TR]
[TD]Accountant III[/TD]
[TD]1005[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Accountant I[/TD]
[TD]1008[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Auditor II[/TD]
[TD]1015[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Tax Accountant I[/TD]
[TD]1021[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Tax Analyst I[/TD]
[TD]1023[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Financial Analyst I[/TD]
[TD]1028[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Corporate Social Responsibility Specialist[/TD]
[TD]1843[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Community Manager[/TD]
[TD]1844[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Treasury Analyst[/TD]
[TD]5670[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Payroll Manager[/TD]
[TD]5675[/TD]
[TD]15[/TD]
[/TR]
</tbody>[/TABLE]

 

[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