Comparing Two Columns and returning any word matches found

[tuddin]

I have two lists, one has 250 items, the other 4500, both contain a String

I need to compare any word within a cell in the short list, against any word within a cell in the long list.

The returned value needs to display both the short list item, as well as the long list item for manual comparison by an analyst.

If anyone could help me find a good way to perform this search, as well as how to best display it for the analyst to compare the data, it would be much appreciated.

Thanks,

 

[taurean]

Welcome to MrExcel.

If you could post a sample of your data [build up dummy data if you can't post actual data] and expected results then it will be easier to help you.

 

[tuddin]

EXAMPLE DATA:

Column 1 (4500 rows total)
- Walmart
- Starbucks
- Starbucks Coffee
- Ernst & Young
- Ernst and Young
- Ernst & Young, LLC
- Ernst & Young, L.L.C.
- McDonalds
- Taco Bell
- UBS
- UBS Bank
- UBS Equities
- LaSalle Bank
- ...


Column 2 (250 rows total)
- Starbucks
- UBS Bank
- Ernst & Young
- ...

Expected Return should compare between all the words within both columns, and return data that will show where all over lap exists.
--- Starbucks in Column 2 should show both entries from Column 1
--- UBS Bank in Column 2 should show all 3 entries from Column 1, as well as "LaSalle Bank"
--- Ernst & Young in Column 2 should show all four entries from Column 1

 

[taurean]

I think a formula solution will be difficult. Is VBA solution acceptable?

In above example, you have shown that & and AND to be matched. How many such interchange entries do you have?

 

[tuddin]

There are quite a few instances of "&" and "AND"

And A VBA solutions is fine, can you provide some direction on how to run a VBA solution as well?

Thanks,