Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Closest Text Match
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Closest Text Match
On Jan 22, 7:44*am, fabio wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Closest Text Match
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" wrote: On Jan 22, 7:44 am, fabio wrote: 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Closest Text Match
On Jan 22, 11:06*am, fabio wrote:
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" wrote: On Jan 22, 7:44 am, fabio wrote: 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 .- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find closest text match for each unique entry in a list | Excel Discussion (Misc queries) | |||
Text comparison - closest match | Excel Discussion (Misc queries) | |||
Text string comparison - closest match | Excel Discussion (Misc queries) | |||
Finding Closest Match | Excel Worksheet Functions | |||
Finding closest value within an array | Excel Programming |