Duplicates with spelling errors, not exact duplicates and partial duplicates

**ViaPointe** · 03-09-2011, 12:27 PM

I want a program that will find all the partial duplicates. Like when I search something in google and it will finish part of my search based a few letters and make corrections on something that I miss-spelled. It doesn't have to be that advance but I just want something to point anything out that have similar letters and or numbers like for example.

123456789
233456788

FRANCES BACON
CRANCES FACON

JAMES T
JAMEN T

800 VALLEY RD
800 VALLEY ROAD

If there is no such program, than how much would a programmer charge.

Thanks in advance.

**MarvinP** · 03-09-2011, 12:54 PM

Hi ViaPointe and welcome to the forum,

I believe you need to know more about searching in Excel and wildcard characters. Look at:
http://office.microsoft.com/en-us/ex...005203612.aspx
There are also "Soundex" searches for things that might sound alike: See:
http://www.j-walk.com/ss/excel/tips/tip77.htm

As you must realize, finding exact matches is always easier than non-exact.

If you could be more specific about your requirements than perhaps there is a better answer.

**ViaPointe** · 03-09-2011, 06:14 PM

A. column: name
B. column: last name
C. column: address
D. column: city
E. column: zipcode
F. column: phone number
G. column: e-mail
H. column: date they were entered in the database
I. column: date when the client

What happens is that there are spelling errors in the names, addresses, cities, zipcodes, phonenumbers, e-mails.

There are over 10,000 clients.

I'd say like fifteen percent of them are dupes.

I need an easy way to recognize them and group them together because the dupes sometimes occur from the same person five times.

**MarvinP** · 03-09-2011, 07:00 PM

I've had to clean more than 10 databases where problems like this are involved. My method was to autosort the whole table and look down each column. I'd find a large string of Smith but the first or last one in the Smith Group would be misspelled. Hand correcting them was slow but was my solution.

I've also done advanced filters or pivot tables where I could get a unique alphabetic list. I'd see there were 2 of Smitth, 35 Smith and 4 Smitvh. This would then give a hint on what to Search and Find and Replace.

I'm sorry to say I know of no "for sure and exact" formula to correct these kinds of errors.

That said, Excel has a Data Validation feature that will keep spelling problems from happening in the future, if you set it up correctly.