Excel 2007 : Partial matching cells with locations

Why not just AutoFilter?

You could use a Conditional Format - if we assume A1:A10 hold Locations then having highlighted B1:Bn (where n is last row of strings) apply the following formula based Rule:

Please Login or Register  to view this content.

The above will then highlight all strings in B that contain one or more locations as listed in A

(the above assumes space delimited strings - modify as necessary)

As outlined the suggestion related to Conditional Formatting.

If you want to put in C you can do though you might want to wrap an ISNUMBER test around the formula - result of TRUE means 1+ locations found.
(without the ISNUMBER test: any number = location found - #N/A means not found)

Not sure I follow - do you want the row number of the location found, the location itself or something else entirely ?

It's also not clear what you want to happen should more than one location be found in any given string.

If you have a file to upload please do so here directly

Originally Posted by FAQ

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.

To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.

Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.

You can then close the window to return to the new post screen.

No attachment.

With that much data (>18k locations to search for per keyword and >23k keywords) I would advise against a formula driven solution - is VBA viable ?

OK - I am looking at this but might not reply until tomorrow.

The code I have presently is still a little on the slow side - ie approx. 40 secs to process just 1000 keywords against the 18k+ locations
Given this code needs to be scalable to handle in excess of 20k keyword phrases this (code/approach) simply isn't viable and requires a re-think on my part.

The ordering of your locations is obviously important and I trust the entries are listed as they are in your sample deliberately ?
(towns, states, countries & continents in that order)

I inserted a column for results like so:

Please Login or Register  to view this content.

Then run this:

Please Login or Register  to view this content.

Thanks DonkeyOte I will wait for your solution. I am afraid the locations are random and are not in any specific order. They are just the UK and Holland locations.

Thanks for your help SHG. Can you please let me know the steps to run this code. Please correct me if I am wrong but is this code returning the row number of the location found in Column A, in the Column B.

I would not expect there to be a row limitation on the size of an array that can be sucked into memory, but there may certainly be a limitation of absolute array size, and the transpose operation transiently doubles the memory demand (I think; DO, Colin?).

The code could be modified to avoid that, and could be modified to use a Location list on one sheet and multiple keyword lists on other sheets (the names of those lists seems reversed to me).

If you want to put the full Monty on box.net and post a link I'll look at it.

In XL2007 it looks like TRANSPOSE() is limited to 65536 elements. It used to be 5461.