How to compare similar entries, NOT duplicated ones?

[Serapk]

Hi - I am working with Excel 2003 and I have two columns to compare similar
entries, NOT duplicated ones as most of the time there is a typo with the
entry and I can't see it as duplicated.

Each column contains about 500 names and they are all Asian names. My goal
is to identify where a name is repeated with a typo or a middle name (i.e.
one column says John Doe whereas the other one says John K. Doe).

So, I would be very happy if someone can show me how to find and similar
entries.

Thank you.
Serap Kr.

 

[Bernard Liengme]

It is very difficult to find typos - they are random events
I would start with this
=IF(LEFT(A1,4)&RIGHT(A1,4)=LEFT(B1,4)&RIGHT(B1,4),"same","different")
best wishes

 

[Serapk]

Hi Bernard - thanks so much for your quick response but unfortunately it did
not work as this formula only shows if they are on the same row. I do
understand that it might be difficult to find if there is a typo but what if
there is an additional letter? ie.. one cell says John Doe and the other one
says Joe K. Doe

 

[Bernard Liengme]

In columns A and B I have some names.
The ones that interest us are
A1: John Doe and B4: John K. Doe
A2: Robert E Lee and B3: Robert Lee

In C1 I have a long formula:
=LEFT(A1,FIND(" ",A1,1)-1)&IF(ISNUMBER(FIND(" ",A1,FIND("
",A1)+1)),MID(A1,FIND(" ",A1,FIND(" ",A1)+1),255),MID(A1,FIND("
",A1,1),255))
And a formula in D1 with the same logic
=LEFT(B1,FIND(" ",B1,1)-1)&IF(ISNUMBER(FIND(" ",B1,FIND("
",B1)+1)),MID(B1,FIND(" ",B1,FIND(" ",B1)+1),255),MID(B1,FIND("
",B1,1),255))


These render the names with just the first and the last part: John Doe and
Robert Lee
Note that Robert Smith and Robert J. Smith both generate Robert Smith but
Robert A B Smith ( two initials so more than two spaces) would mess things
up!

In E1 I used =COUNTIF(D,C1) and it gives values of 1 whenever the name in
C is repeated in D, and 0 if there is no repeat
In F1 I used:=MATCH(C1,D,0) this gave value 4 because C1 is John Doe and
the fourth entry in column D is John Doe. When copied down the column if
gave 3 in D2 since C2 is Robert Lee as is the third item in column D. If
give #N/A when there is no match. To get rid of this, in F1 use
=IF(E1,MATCH(C1,D,0),"")

Send me a private email (just remove TRUENORTH. ) and I will send you a file
best wishes