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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

CodeNinja

Well-known Member
Joined
Feb 18, 2013
Messages
644
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,983
Messages
5,834,708
Members
430,311
Latest member
JAC0617

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