Search for occurences of text string from a list

In B2 on your phrases sheet you could use:

=INDEX(Locations!A:A,MAX(INDEX(ROW(Locations!$A$2:$A$12)*(ISNUMBER(FIND(UPPER(Locations!$A$2:$A$12),UPPER(A2)))),0)))

But I think this will fail by the time you get to 7,000 rows of locations, so you'd probably need a UDF (User Defined Function) written in Excel.

Thing is, how do you want to handle multiple matches? For example, if you have the phrase "How do I get out of Basingstoke?" then in the locations it would match against both "Basingstoke" and "Stoke"

Hi Andrew

thanks for the help. In the scenario you suggest I would want the result to return "Basingstoke" and not "Stoke"

I will try the formula you suggest and see what happens

Howard

The formula will work for your sample file, but ISTR that there is a limit to how many elements you can have in an array formula, and I'm pretty sure that 7,000 is over that limit.

The other question about multiple matches is how you deal with strings such as, "Nightlife in Newcastle". Will your locations list have an entry just for "Newcastle" or will it have entries for "Newcastle upon Tyne" and "Newcastle under Lyme"?

Just tried it and it almost works, but it doesn't check for whole words, so for example "Bathing costumes in Exeter" returns "Bath"

Any other thoughts?

Howard

Originally Posted by Andrew-R

The formula will work for your sample file, but ISTR that there is a limit to how many elements you can have in an array formula, and I'm pretty sure that 7,000 is over that limit.

The other question about multiple matches is how you deal with strings such as, "Nightlife in Newcastle". Will your locations list have an entry just for "Newcastle" or will it have entries for "Newcastle upon Tyne" and "Newcastle under Lyme"?

Currently my list has "Newcastle upon Tyne" and "Newcastle under Lyme" as separate entries, and so woul not return any value for "Nightlife in Newcastle". I think the only realsitic workaround for this scenario is adding a value for Newcastle and then manually cleaning the data. But I am happy with that as an option.

I'm not sure if Fotis' suggestion will work with your full data set, or if it will run into the same limitations as my formula.

Anyway, the following UDF should do the job:

Please Login or Register  to view this content.

With this in a module in your workbook you can use a formula such as:

=listmatch(A2,Locations!$A$2:$A$12)

Hi Andrew

thanks for the code, but unfortunately we are still getting the problem of it not matching entire words, so that "bathing suits in exeter" matches "Bath".

I was thinking of adding a space before and after each place name - not very elegant I know, but would it work?

Howard

Sorry, Howard, that was my mistake. Change the line:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

That is awesome! Thank you so much for your help

Howard

Happy to help, but I'm not sure how fast it will be when it's used 50k times against 7k rows of data - you might want to get yourself a good book to read

A better idea would be to write a macro to go through your 50k rows, run my function against them, and write the return value into the appropriate cell. It won't be any quicker, but at least it won't constantly re-calculate and you can, instead, just run it when you're going on lunch, or at 5pm on a Friday, or just before you start a 6 month world-cruise, etc.

Tried it on 10k rows and it only took about 20 seconds, and that was checking against 3 different lists of locations, product types and brands.

The number of locations will make a big difference, but try it and see how it goes. If need be I can write a quicker function.