Removing approximate duplicates in single column in '03

[silvergrahm]

Hi all,

I am looking to removing duplicates of names of companies in a sheet that has contact information from a number of sources. The first thing I need to do is figure out how to remove duplicates based only on column A. I am using excel 2003 for mac. Will I be able to do this if I simply highlight column A, then data>filter>advanced filter and check unique records only? It prompts me with a warning screen "excel cannot determine what row in your list or selection contains column labels, which are required..."

If so, this will remove about 90% of my duplicates. However, there may be spelling discrepancies in some of the entries. That's not to say that anything will be misspelled, but it might be the difference between "Acme" and "Acme inc". Does anyone know a good way to get around this problem?

Thank you

[Jarko28]

Hi,

One way is to have criteria which will determine how to define a match. So in your example for "Acme" or "Acme inc." the way Excel would be able to handle this problem is by saying: take a look at the first 4 characters in both cells that contain the data, if they match, then consider them the same. For example, Excel's =LEFT function

You can add further rules such as ...apply the rule only for items which have names that are no more than 6 characters long...etc Excel's =IF function


Thanks.