Unanswered: Matching on similar/equivalent fields between two tables
Hello, thank you for your attention in advance.
This seems like a simple problem, but I cannot find a simple solution.
T1 (Table 1) Long list of company names, entered inconsistently
T2 (Table 2) Short list of company names, unique
I would like to match based on equivalence between T1 & T2, and display or update T1 with equivalently matched data from T2.
Bank of Amer.
AT & T
Bank of America
Desired results are a new column in T1, with normalized naming as follows:
T1-Original Column-----T1-Additional/Results Column
ATT--------------------AT & T
BANKOFAMER-----------Bank of America
W Fargo---------------Wells Fargo
Bank of Amer.----------Bank of America
Sorry fella. Unless someone knows of something really cunning you will need to associate this manually.
You might be able to use a soundex algorithm that will short cut a lot of the donkey work but I would expect anything we come up with to have stuff "fall through the gaps". A really simple start would be to replace all non alpha characters (" ", ".") with a "*" and do a like comparrison. You could also create a vb UDF to parse the input string (the column entries from T1) and add a "*" before every capitalised letter that is preceded with a lower case letter too.
Are you hoping to do this at run time or are you intending to clean the data?