If Contains then Macro

[hmccullo]

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!

 

[CodeNinja]

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 NewYork Project: Burger King Burger King Philadelphia Texas McDonalds Houston Contains Project: Atlanta McDonalds Burger 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.