Finding Closest Text Match

[fabio]

Hi

Is it possible to compare two lists of names (for example) and return the
closest match from the second list for comparison?

Basically I was trying to do a VLOOKUP based on a list of names and a second
sheet with names and data but as these were input separately there may be
spelling differences or typos in the names. If I could include in the values
returned the name from the second list which was closest to my LOOKUP_VALUE,
by comparing the two names I would be able to make a decision as too whether
there was a typo or that a close enough match did not exist.

I'm currently using Office 2003.

Thanks for any input.

 

[goshute]

Hi

Is it possible to compare two lists of names (for example) and return the
closest match from the second list for comparison?

Basically I was trying to do a VLOOKUP based on a list of names and a second
sheet with names and data but as these were input separately there may be
spelling differences or typos in the names.  If I could include in the values
returned the name from the second list which was closest to my LOOKUP_VALUE,
by comparing the two names I would be able to make a decision as too whether
there was a typo or that a close enough match did not exist.

I'm currently using Office 2003.

Thanks for any input.

Fabio,
Unless you have tax Id's, names are one of the hardest things to work
with and nothing I have found, or written, works 100% of the time. I
use VLOOKUP to identify direct matches only. Beyond that, VBA is the
best way to match up names, but there can be so many variables, even
this is hard. My first step is a direct comparison of the names, then
I remove any titles, then middle initials, then compare shortened
versions of the names, then I remove the vowels, then I look at the
addresses. Even if a match is found they are scruitinezed. I always
have to know if Bob Parker is the same person as Bobby Parker. Bob
Parker on Main Street may or may not be the same Bob Parker on Second
Street and Bob Parker Jr. may or may not be Bob Parker. I wind up
writting VBA routines for each file because each one is so different,
then manually scrutinize and spot check the results.

Sometimes there are so may variables, I match what I can easily do
with VLOOKUP or a simple VBA routine, and do the others by hand. You
need to be able to verify and trust your results. "Fuzzy" results can
sometimes be worse than no results.

Goshute

 

[fabio]

Hi Goshute

Thanks and I do appreciate the 'fuzzy' issues around such a requirement. I
thought it was more likely to be a coding application rather than VLOOKUP (I
was using that as an example of comparing entries in two lists).

My lists are not huge so I some degree of assessment of the results is not
unreasonable but I would like an indication of a near match rather than a
blank so I am prompted to make such an assesment. A returned blank of no
match does not help indicate which ones need review. As a result it would
then be possible to update the records so the name is identical in both lists.

Again thanks for your views.

 

[goshute]

Hi Goshute

Thanks and I do appreciate the 'fuzzy' issues around such a requirement. I
thought it was more likely to be a coding application rather than VLOOKUP(I
was using that as an example of comparing entries in two lists).

My lists are not huge so I some degree of assessment of the results is not
unreasonable but I would like an indication of a near match rather than a
blank so I am prompted to make such an assesment. A returned blank of no
match does not help indicate which ones need review.  As a result it would
then be possible to update the records so the name is identical in both lists.

Again thanks for your views.







- Show quoted text -

Fabio,
You may try splitting the first and last names into seperate columns
using "Text to Columns". Then run seperate VLOOKUP's to match the
entire name, the first name, and the last name. Compaing the results,
may give you a better starting point than doing it all manually.
Goshute