Hello All.
Can anybody write complicated lookup Macros?
I can't!
Help me please!Smile
I really need help on a macro if possible.
It's a kind of lookup macro I suppose.

I'm no coder, it's all above me really, but I know what I'd like to be able to do, so I suppose that's half the battle:-)
I will try and explain in as brief a way as possible, but I think it would be better if anyone can help that they will probably want my Excel Workbook (At the moment in 2007 macro enabled version)
I can email the sheet if someone PM's me.

OK.I will explain all. Hold onto your seats:-)

In this instance I have a Excel Workbook called ""Homes Thailand Master Property Sheet."

Basically this is a sheet of all keyword phrases found on search engines on the web (Google.com) for example related to property. This sheet includes misspellings and data collected from meta tags etc.

The main workbook sheet which has the large keyword list of about 40,000 rows of phrases is called, "All KWs"
I have entered a lot of the raw data in Col A (From Row 3) under the title of "Keyword Phrase"

As it looks now it is really messy with lots of unwanted characters etc.
So I will explain how I'm using the sheet (Part of it) anyway so you can understand exactly what I'm trying to achieve.

1.First step is to clean up the data.
I have a Macro assigned button called "Clean All KWs List" this will launch a macro that deletes unwanted characters, and carriage returns etc etc.
It's excellent.

2.Next step,I sort all the list by Number of Characters with another macro assigned button.

3. Next is to delete Characters greater and less than, so I usually select 3 and 85,, so this deletes all the characters greater than 85 and less than 3. This is because there is no content value in keyword phrases with 2 characters or more than 85 for me.

4. Again I sort by No of Characters.

So, now I have a list that is clean and in some kind of order.
Now this is where I really, really would like to be able to sort in a different way as it will be a huge time saver.
I shall explain.

If anybody does/is looking at the workbook,Ignore all other sheets in this workbook apart from "Country Finder" Sheet.
Here's the scenario;

I have a massive list related to property. Thing is, in this case it only wants to be related to property terms and "Thailand", or places in Thailand, ie Bangkok,Phuket,Koh Samui etc.
If you looked through the list you would see lots of other countries listed.
For example;
property to rent New York
property to rent in Florida
Property for sale in Paris

So, here is what I would love to be able to do.
I don't want to delete the rows with the other countries in,as there might be some great search term phrases which I wouldn't want to delete, but I do want to be able to delete the country name within the phrase, then after the macro has gone through the list, delete the duplicates.
If I have to go through this list by hand say 40,000 rows, it will take me ages, but imagine I just want to run the "Country Finder" for words in the "Asian List",and ignore deleting the Thailand related words.
This might delete half the list and save me a huge amount of time!

I'll explain more,
On the "Country Finder" sheet I have organised the sheet into Rows with the headings;
1.Americas List Heading in cell A1
2.Canadian List Heading in cell C1
3.UK List Heading in cell E1
4.Europe List Heading in cell G1
5.African List Heading in cell I1
6.Asian List Heading in cell K1
7.Australasia List Heading in cell M1
8.All Other Lists Heading in cell O1

All country/city data related to each list starts from Row 5 and runs down to where ever, so can be added to at any time.

I'll give an example.
I would like to be able to click an assigned button and a pop window would appear.
This could be called "Delete Countries" macro.
In it it would list the 8 categories, with check boxes next to each.
If I check a box the macro would ignore all in that list.(So not deleting any from that list).
Also though the macro would have a "Write in box" where I could type in names that I would want to ignore also.
So, in this example if I clicked the macro button and a pop up box appeared and I want to NOT delete these 3 words only, "Thailand,Bangkok, and Phuket" the box would show the 8 categories listed.
I wouldn't check any of them, but in the write in box below the 8 listed categories I would enter the 3 words separated by a space or comma IE (thailand,bangkok,phuket), so this would look for all the words on the "Country Finder" sheet except these three.
This would then basically look at each row in my "AllKws" sheet and if it finds any of the words listed in this "Country Finder" sheet (Except for the 3 words of thailand,bangkok,phuket) it would delete that word from the row, BUT NOT the entire row.

Once this has run, a pop up box would appear with statistics.
This could be time elapsed, the 8 categories listed and how many names from each list were deleted, and total amount of rows altered etc.

Then, I maybe I would have several thousand duplicate rows in the keyword list.
For example,
property to rent new york
property to rent thailand
property to rent london
which would now look like;

property to rent
property to rent thailand
property to rent

So, I could then run the existing macro in my "AllKws" sheet called "Clean All Keywords List" which among other things, deletes duplicate rows.

This would be a huge massive time saver.
This 1 macro could delete (I'm guessing here) up to 30-40% of the list!)
Imagine the time this could save.
It would delete 30-40% of the keyword list but wouldn't be deleting important keyword phrases, only the unwanted countries.

I hope this all makes sense.

As mentioned earlier,if anyone can help me everyone is more than welcome to my workbook as it would make it similar to understand if you can actually see it before your eyes.

Either PM me or email me at

Many thanks for all your time, I really hope I've made sense and someone can help me with this

All the best
John Caines
ps, I have psted on Mr Excel also ,
here is the link