If Contains then Macro

hmccullo

New Member
Joined
Apr 10, 2013
Messages
2
Hello,

I have a list of project names that I need to categorize into customers. I want to essentially be able to a vlookup, but instead of the vlookup being based on the entire cell value I want it to be able to recognize if it contains specific words. For instance if the data set was:

McDonalds NewYork
Texas McDonalds Houston
Atlanta McDonalds

I want to be able to have all of these project names attached to Mcdonald's in column B. Ideally I'd like to be able to have a list that has all of the key words I'm searching for and in the cell next to it the customer its associated with. Not all of them will be like for like meaning for Mcdonalds I will need to search for Mcdonalds, McDonald's, McD, etc.

Any ideas?

Thanks so much!
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

CodeNinja

Well-known Member
Joined
Feb 18, 2013
Messages
634
hmcculo,
Not sure if this is what you are looking for... There are a lot of ways to get the data that only contains "McDonalds". One way is a formulaic solution to showing all fields that contain just your requested data.

Column A contains your list of projects
Column B is left blank (just out of habit to make it a data set)
Cell D2 is the variable. When you change this (for instance to "McDonalds") the formula data changes.
C5:C10 contain the following formula: =IFERROR(INDEX($A$1:$A$6,AGGREGATE(15,6,(ROW($A$1:$A$6)/NOT(ISERR(SEARCH($D$2,$A$1:$A$6)))),ROWS(C5:C$5))),"")
That formula is entered in C5 and dragged down to C10.

The result looks like this:
A:
B:C:D:
Project Name:
McDonalds NewYorkProject:Burger King
Burger King Philadelphia
Texas McDonalds HoustonContains Project:
Atlanta McDonaldsBurger King Philadelphia
New York Burger King
New York Burger King

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>
You could also use the advanced filter to generate this data very quickly.

If this is not what you are looking for, please provide more detail.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top