Data Analysis to detect duplication

**montroseite** · 12-12-2010, 11:01 AM

Dear Admin,

I got one question here, I need to compile my company address book from 4 workbooks into 1 worksheet. I am very sure that there are some duplication within the records. I was used the Advanced Filter function to extract unique records. But, this function cannot detect some scenarios, please see example:

A1
Restaurant Tai Lian private owned
Restaurant Tai Lian
Restaurant Tai Lian S/B

A1
Astron Martin S/B
Astron Martin

A1
Sime Darby S/B
Company Sime Darby

I might can detect the first 2 examples using sorting, but the 3rd example is hardly to use sorting. Furthermore, I got 10k records need to massage. I am wonder is there an easier way or faster to perform the house keep jobs?

Thanks and Regards

**JBeaucaire** · 12-12-2010, 11:10 AM

Perhaps an adjacent column where you remove all these known "strings" that make the names not match. This will remove "private owned" from all the strings...

=TRIM(SUBSTITUTE(A1, "private owned", ""))

You can add more like so:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "private owned", ""),"S/B", ""),"Company", ""))

Copy the formula down the whole column, then do your sorting/match tests.

**montroseite** · 12-12-2010, 11:37 AM

Hi Admin,

I am appreciate your suggestion, but to me it is seem like not working well as there are 10k lines, I am hardly to do these. If there any formula has function like Vlookup but return all possible values without exact match also throw out ?

Thanks and Regards!

**JBeaucaire** · 12-13-2010, 12:24 AM

Nope...the concept of "almost matches" is a human concept. Any formula or macro will have to be explicitly taught how to logically decide what "close enough" means on a case by case basis. As you work out each exception, it should apply to multiple cases, but you'll still have to work it out.

A dataset of 10k entries should be broad enough to help you acquire a logic for dealing with most situations, that's a lot data.