Maybe this formula?

=AND(LEFT(A1,FIND(" ",A1)-1)=LEFT(B1,FIND("
",B1)-1),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT("
",99)),99))=TRIM(RIGHT(SUBSTITUTE(B1," ",REPT("
",99)),99)),IF(LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))=LEN(B1)-LEN(SUBSTITUTE(B1," ","")),MID(A1,FIND(" ",A1&" ")+1,FIND("
",A1&" ",FIND(" ",A1&" ")+1)-FIND(" ",A1&" ")-1)=MID(B1,FIND(" ",B1&"
")+1,FIND(" ",B1&" ",FIND(" ",B1&" ")+1)-FIND(" ",B1&" ")-1),TRUE))

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
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