Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to compare similar entries, NOT duplicated ones?
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to compare similar entries, NOT duplicated ones?
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Serapk" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to compare similar entries, NOT duplicated ones?
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 -- SerapKr. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
How to compare similar entries, NOT duplicated ones?
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: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: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:D,0),"") Send me a private email (just remove TRUENORTH. ) and I will send you a file best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Serapk" wrote in message ... 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 -- SerapKr. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare and consolidating duplicated rows | Excel Discussion (Misc queries) | |||
how to merge two spreadsheets and remove duplicated entries. | Excel Discussion (Misc queries) | |||
Hiding Non-Duplicated Entries | Excel Discussion (Misc queries) | |||
Find & delete duplicated entries. | Excel Discussion (Misc queries) | |||
how to delete duplicated entries from an excel list | Excel Discussion (Misc queries) |