Excel 2007 : check for list of substrings with list of strings

[rugbyfan]

I have a master list of top keywords, and I want to search for variations of them in the remaining list of keywords.

Top Keyword List ------------ Remaining List
tall trees------------------------- fast blue cars
blue cars------------------------blue cars online
--------------------------------------trees grow tall
--------------------------------------grow tall trees
--------------------------------------blue cars rock
--------------------------------------really blue cars
--------------------------------------green tall trees

What I need is a formula that I can copy down next to the Remaining List, which checks if each of the values contains any of the substrings in the Top Keyword List, it can give a value of 1 or the substring etc

Maybe VBA is needed? Any help much appreciated..

[DonkeyOte]

It would help if you outlined the results - for ex. is the third item in your "Remaining List" to be considered a match or not ? [trees grow tall | tall trees ]

The answer to the above will (IMO) largely dictate whether or not you should be using VBA.

[rugbyfan]

It would help if you outlined the results - for ex. is the third item in your "Remaining List" to be considered a match or not ? [trees grow tall | tall trees ]

The answer to the above will (IMO) largely dictate whether or not you should be using VBA.

Initially I just wanted to find phrase matches, this would result in the example you chose not producing a match.

However, it would be very beneficial if it could find the phrase matches even if the words were not in order, ie so the above would produce a match.

[DonkeyOte]

Initially I just wanted to find phrase matches, this would result in the example you chose not producing a match.

For the above a "standard":

Please Login or Register  to view this content.

where A2&A3 are your top keywords and B2 the first string in remaining list

it would be very beneficial if it could find the phrase matches even if the words were not in order

For the above you might want to consider a UDF.

Below not really tested in depth...

Please Login or Register  to view this content.

called along the lines of:

Please Login or Register  to view this content.

with optional parameters for defining case sensitivity (assumed insensitive) & string delimiter (assumed space) - each of which can be explicitly specified as desired.

[rugbyfan]

Thanks Donkey0te. The first part works perfectly. I must confess it will take me some time to digest the VBA on there, but it will definitely be worth working through.
Thanks for your help.