# Compare similar cells in Excel



## godzi (Oct 30, 2007)

Hi there, my question is not too simple.. How can I compare 2 cells with similar text, I mean I have this:

Cell A1: John Smith Cell B1: John S.
A2: Smith John B2: Clinton Bay.

I have 400 rows, of these similar names, so basically I want to find all those cases where the names matches with similar significance. 

Please Help ..!!!

Thanks... Godzi


----------



## kirbs_ph (Oct 29, 2007)

what exactly do you mean by similar significance?


----------



## slurpee55 (Oct 20, 2004)

Will it always be the first names that are the same (or not)?


----------



## jimr381 (Jul 20, 2007)

I typically throw the values into an Access table and run a query looking for unique values. This typically allows me to scan through the list and find Bill Lumbergh typed as Bill Lumberg. The problem you have is that sometimes the first name will be first and sometimes it will be second it seems. If you are using this list for future data entry then you will want to separate the first name from the surname to alleviate confusion form your data entry clerks.


----------



## slurpee55 (Oct 20, 2004)

Yes, Access will not find names as unique if there is any difference, be it in spelling, name order, what have you. I too would import the Excel file into Access and clean it there, but I might first clean up things like name order in Excel by breaking the list into columns, doing a pivot table on the first names and finding any names that stand out (James might be first or last name, Jameson is less likely to be a first name...).


----------



## Zack Barresse (Jul 25, 2004)

Hi there,

Take a look at these threads..

http://www.tek-tips.com/viewthread.cfm?qid=1421469
http://www.mrexcel.com/board2/viewtopic.php?p=974873
http://www.mrexcel.com/archive2/4800/5407.htm

HTH


----------



## godzi (Oct 30, 2007)

Thank you guys, I think I wasn't too explicit last time.

The thing is this. I am working with 2 Columns full of Company's names, I need to separate those rows cells with totally different names, but keep the match with those using similar names, including punctuation, and symbols:

Hence: 
A: 
A1 BODARK OIL COMPANY
A2 M & G OIL COMPANY
A3 PIONEER NATURAL RES. USA, INC.
A4 ENDEAVOR ENERGY RESOURCES L.P.
B:
B1 BODARK OIL CO
B2 ANADARKO CO.
B3 PIONEER NATURAL RES. U
B4 ENDEAVOR ENERGY RESOUR

As you can see I need a function to tell me that A2 and B2 are N/A, but A1, B1 and the rest are similar, it means its the same company.

PLease... help I have this 800 records Table...

Godzi


----------



## slurpee55 (Oct 20, 2004)

While I can't promise this will find everything, it might make a good start - it compares the 5 letters on the right of each cell:
=EXACT(LEFT(A1,5),LEFT(B1,5)) (e.g., here it compares BODAR with BODAR and returns TRUE). You can adjust this - if you want to compare 10 letters, just change the 5 to 10 in each (). 
So just enter this into C1 and drag down. When you see a FALSE, then you can check those out more carefully.


----------



## Zack Barresse (Jul 25, 2004)

I still recommend looking at the Fuzzy Match option. It will do what you ask for.


----------



## slurpee55 (Oct 20, 2004)

Oh, I looked at it, and I agree, but, hey Zack, some of us are mere mortals....


----------



## godzi (Oct 30, 2007)

sLURpee55 that is what I was looking for, something simple and concrete ...!!! You Rock !!!!! Thank you very much

Godzi


----------



## slurpee55 (Oct 20, 2004)

glad to help - that is the sort of thing I have learned here, so keep coming back!


----------

