Options needed to cleanup State list

[bigroo1958]

Hello,

I download a word document from are website and convert to a excel document. I then link this spreadsheet to an Access database and wish to compare the linked data to data held in the database for updates. The issue I am finding is the Mexican states are in a three letter abbreviation format, whereas the database has a table named tbllocations with states in a two letter format for Mexico. Also, I will add, the spreadsheet state names are not clean, they have full stops on some states.

This is where I need suggestions as to what is the best approach.

Would it be easier to convert within the excel spreadsheet the state codes and if so how to do this with VBA at a click of a button, or within Access after the spreadsheet has been linked and convert the state to what is in the tbllocation with a click of a button with VBA code. I could within Access create an update query for each variation but this does not seem too practical.

Also, I was not too sure if I should place this in the Access or Excel forum

Thanks

[SamT]

hey have full stops on some states.

Don't know what "Full Stop" means.

Don't know anything about Access, so I would use Excel VBA with two Scripting Dictionaries, one with the three letter names as Keys and the two letter names as Items to convert the 3's to 2's. Then I would run the other Dictionary with the two letter names as keys and "True" as the Items to color the remaining names that could not be recognized by the first Dictionary.

[bigroo1958]

Thanks Sam T for the reply.

Sorry, Full Stops, I should have said periods in between the letters.
Would you have an example of the two scripting Dictionaries you mention, not too sure what these are in VBA world or what they do!

[SamT]

You must have a reference set to Microsoft Scripting Runtime.

Attached is an excerpt from a much larger workbook that uses Dictionaries. For States, no less Well, at least Counties. The States Dictionary only had one entry at the time. I stripped a lot off even the worksheet.

The example code is all in the Module "UDOs" and references the worksheet "Counties." The notes in blue on Counties is for you.

In sheet1 is an example of the two lists I think would work for you. The example code is in the sheet2 code module.