Remove Duplicates in Excel works on exact matches. It will happily leave you with three rows for the same customer because one has an ampersand, one spells Limited out and one is in capitals. Those are the duplicates that cost you money, because they are the ones that survive every clean up.
Why exact matching misses most of them
Duplicates are almost never identical. They arrive from different places: one record typed by someone on the phone, one imported from a supplier list, one filled in by the customer on a web form. Each source has its own conventions for ampersands, legal suffixes, capitalisation and whether the trading name or the registered name goes in the box. Sort the column and they sit nowhere near each other, because S-M-I-T-H space ampersand and S-M-I-T-H space A-N-D are different strings.
What the same record actually looks like
Company names diverge on legal form, so Limited, Ltd, PLC and nothing at all. On ampersands against the word and. On the definite article, since The Smith Group and Smith Group are one company. On trading names against registered names. People diverge on initials, so J Smith and John Smith, on middle names, and on nicknames. Addresses diverge on whether the flat number went in line one or line two. Any of those makes an exact match fail while a human would spot it immediately.
The approach that works, and the one that does not
The obvious idea is to measure how many characters differ, which is what edit distance does. It fails badly on names. Smith Ltd and Smith Solutions Ltd share a long run of characters so they score highly, and they are two different businesses. John Smith and Jane Smith share a surname and score highly too. Comparing word against word is the thing that works: match each word in the shorter name to its closest partner in the longer one, and judge the pair by the weakest match rather than the average. Then a typo in one word is forgiven while a genuinely different word is not, because the distinguishing word is precisely the one that matters.
Which fields to trust
Not all evidence is equal. A matching company number identifies the same business on its own, whatever the names say, and so does a matching email address. A matching postcode proves very little: two businesses at the same address are not one business, and a shared postcode with a similar name is exactly the trap that merges records it should not. Treat identifiers as decisive, names as the test, and everything else as supporting evidence only.
Deciding which one survives
Once you have a group, something has to be kept and the rest removed. The default worth using is the most complete record, counting how many fields are populated, then filling any remaining blanks from the others. That way nothing is lost: the record with the phone number contributes the phone number even if it is not the one you keep. What you should not do is keep the oldest or the newest by reflex, because neither correlates with which one is right.
The error that costs more than the duplicates
Merging two records that are actually different customers. A duplicate left in your list is an annoyance, someone gets two emails and you look careless. A wrongly merged pair is data loss, and you will not notice until somebody asks why their order went to another company. Any process for this should lean toward leaving things alone, flag the marginal cases for a human rather than deciding them, and never merge automatically on a threshold alone.
Doing it without uploading your customer list
Our free data cleansing tool does this in your browser. It compares names word by word, treats company numbers and email addresses as decisive, shows each group with what it matched on, and lets you pick which record survives before anything changes. The file never leaves your machine, which is the point: a customer list is the last thing you want to paste into a website to find out what it would do.
Three rows, one customer
Free tool that finds the near duplicates Excel misses, in your browser, with nothing uploaded.
Find the duplicates in your file