Thread
:
Comparing text strings
View Single Post
#
2
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
Posts: 5,651
Comparing text strings
On Fri, 27 Nov 2009 11:12:01 -0800, Comparing columns with text <Comparing
columns with
wrote:
I have two columns populated with names and I need to compare them for
duplicates. Some duplicate names may contain middle initial or middle names
and some don't. So they may not be EXACT. Please give me suggestions on
formulas or funcions that would help me accomplish this task.
Thanks.
This is an interesting problem, and not particularly easy to solve, unless you
can be very specific, and limiting, in the allowable variability.
For example, given the following:
Name = FirstName [MI or Middle Name] LastName
you could construct an algorithm that looks first for an exact match in
FirstName and LastName followed by a comparison of what is in between.
For example:
Name 1 matches in Name 2
No MI of MN anything
MI same MI or MN starting with MI or nothing
MN MI = left(MN,1) or same MN or nothing
Exactly how to construct this algorithm depends critically on how your data is
stored, and how you define "duplicate names"
On the other hand, if you are looking to answer the question, "do two different
names represent the same person", then a Soundex (or NYSIIS) method might be
better.
http://j-walk.com/ss/excel/tips/tip77.htm
http://en.wikipedia.org/wiki/New_Yor...gence_Sys tem
--ron
Reply With Quote
Ron Rosenfeld
View Public Profile
Find all posts by Ron Rosenfeld