Assign Category based on a contact's job title using Vba

**divyasn31** · 12-05-2023, 01:29 AM

Hi All,

I have a workbook with 2 sheets. The first sheet (Sheet Name "Contacts") has the columns in the order: Company, First Name, Last Name, Job Title, Location. The second sheet (Sheet Name: Categories) has 2 columns in the order: Job Function and Keywords.

I need to assign the Job function to Sheet 1 (Contacts) based on the first occurrence of the keywords in Job Title of Sheet 2 (Categories) using VBA.

Here are few Examples:
Job Title 1: IT Security and Cloud Manager -> Job Function: Security
Job Title 2: Cloud and IT Security Manager-> Job Function: Cloud
Job Title 3: Technology and Security Director-> Job Function: Security
Job Title 4: Head of Application-> Job Function: DevOps
Job Title 5: Head of Application Security-> Job Function: Security

Job Functions List:
Job Function Keywords
Security Application Security
Security IT Security
Security CISO
Security Cloud Security
Security Cyber
Security Technology Security
Security Information Security
DevOps Application
DevOps DevOps
DevOps Software
DevOps IT Engineering
DevOps IT Delivery
DevOps Product
Cloud Cloud
Cloud IT Operations
Cloud IT Infrastructure
Cloud Platform

Sample Tool V2.0.xlsm

**GameChanger** · 12-05-2023, 01:54 AM

Have you got a sample workbook you can upload?

**divyasn31** · 12-05-2023, 07:41 PM

Hi, I have attached the sample data in this post

**GameChanger** · 12-05-2023, 09:03 PM

This macro should do it;

Please Login or Register  to view this content.

PS as you have multiple keywords for each Job Function I have treated each keyword as a single entity.
For example if your Keyword (singular) is "Technology Security" and your job title is "Technology AND Security Director"...it won't find it.

You could treat each word in the Keywords column as a single keyword, but that may cause you other problems. I think it would be better to be a bit more savvy with your Keyword entries.

**divyasn31** · 12-06-2023, 02:41 AM

Thanks for sharing that code. I will give a try. Will it work if I add "Technology and Security" in my keywords list?

**GameChanger** · 12-06-2023, 06:20 AM

No worries and yes, that will work

**divyasn31** · 12-06-2023, 04:17 PM

Thank you. The code worked

**divyasn31** · 12-12-2023, 06:36 PM

Hi, in the sheet "Categories", I have a Job Function "HR" and Keyword as "EX". If job title is say, "Head of EX and Transformation", the code should assign the Job function as HR. However, the code assigns the Job Function as HR, if the job title has the word "Executive" in it.

For example: Job Function for "Executive Director DevOps"- should be DevOps. But the code assigns HR as Job Function as the job title has EX in Executive
Job Function for "Director EX" should be HR

Is there a way to solve this?

**GameChanger** · 12-12-2023, 06:53 PM

Yep maybe change it to this;

Please Login or Register  to view this content.

**divyasn31** · 12-12-2023, 07:20 PM

Okay. How do I dd other punctuations in addition to space? Like: , / : - &
Also, I noticed that the code runs based on the order of keywords in the sheet "Categories". Is it possible for the code to work based on the first occurrence of the keywords from the entire list of keywords in the sheet "Categories".
For example:
"Head of Digital and Technology Risk" should be assigned to Digital and not Cyber Leaders
"Head of Technology Risk, Digital and Transformation" will be assigned to Cyber Leaders.

The keywords:
"Digital"has the Job function "Digital"
"Technology Risk" has the job function "Cyber Leaders"

**GameChanger** · 12-12-2023, 07:44 PM

Have you tried it with the punctuation you are talking about?
If you are talking about that punctuation in your Job Title, then that line will handle that case as it looks for your keyword PLUS a space on your job title.
The means if your keyword is EX and your job title has EX/, EX!, EX@, EX% or EX& it won't match it. It will only match with EX followed by a space eg 'EX HR'

It does not work on the order of keywords in categories, it works on the FIRST match in the job title.

If you think it doesn't work in this way, put up an example of where you think it doesn't give the right answer and I'll have a look at it.

**divyasn31** · 12-12-2023, 07:53 PM

Yes. I tried. I have attached the Sample tool I created for this and highlighted the incorrect one in red.

Is there a way to put an array of punctuations like this:
Const p As String = ", /&:-"

and whenever this punctuation appears, the punctuation can be replaced by a space in the code and then execute the rest of the logic

**GameChanger** · 12-12-2023, 08:15 PM

So you have changed the original code in this way (and maybe elsewhere that I haven't seen);

c1.Offset(, 1) = c2.Offset(, -2)
c1.Offset(, 2) = c2.Offset(, -1)

and got rid of keywords and replaced it with title?

So you need to explain to me WHY Cyber Leaders is wrong for that Job Tile in red and what you think it should be.

**divyasn31** · 12-12-2023, 08:22 PM

Sure. So the job title highlighted in red is "Executive Manager Digital, Technology Risk & Resilience". The first keyword is Digital (from the Categories sheet) and the second keyword is "Technology Risk". So this job title should be assigned to the job function "Digital". Hope it makes sense :-)

**GameChanger** · 12-12-2023, 08:32 PM

OK. So adding the space created this issue.

So you DON'T want a match if the Keyword is EX and the Job Title has 'EXECUTIVE' in it,
BUT you DO want a match if the keyword is DIGITAL and the Job Title has 'Digital,' in it.

Is that Correct?

**divyasn31** · 12-12-2023, 08:34 PM

Thats right.
Is there a way to put an array of punctuations like this:
Const p As String = ", /&:-"

and whenever this punctuation appears, the punctuation can be replaced by a space in the code and then execute the rest of the logic

**GameChanger** · 12-12-2023, 08:57 PM

Here is a function that will raplace those characters with a space;

Please Login or Register  to view this content.

Then in your code;

Please Login or Register  to view this content.

**divyasn31** · 12-12-2023, 10:46 PM

Thank you so much. It worked

**GameChanger** · 12-13-2023, 01:39 AM

No problems. Glad you got it sorted.

Assign Category based on a contact's job title using Vba

LinkBack

Thread Tools

Rate This Thread

Display

Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Re: Assign Category based on a contact's job title using Vba

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Assign rows based on category and age

Assign Category based on Name

Wants to assign specific category based on student score

[SOLVED] If Then Macro based on one column to assign category to another

Assign a category to a cell based on two conditions

[SOLVED] How Do I Assign Attributes to Entries Based on Category?

What can i use to display a category title based on a cells value?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions