In this guide 6 sections
Duplicates appear because several people type names freely, with no unique customer code to stop them. Fuzzy matching can list likely pairs, but a person must decide each merge. Pick one master record. Move open invoices and receipts onto it, and keep the old rows as aliases.
Why one customer becomes three rows
A spreadsheet accepts anything. Sales types Smith Hardware, accounts types Smith Hardware Ltd, and a driver adds a row holding only a mobile number after a phone order. Each entry looks sensible to its author.
No customer code forces a single answer. Sorting by name hides the trouble, since the first two sit side by side and read as one line, while Smith H/W lands pages away and nobody connects it.
Copying a file to email it and pasting edits back adds more rows. So do trailing spaces, mixed case and a second branch typed in as a new buyer by someone who did not check the list first.
- Free typing with no customer code
- Legal suffix present or missing: Ltd, LLC, Inc
- Abbreviations such as Hdwe or H/W
- Phone only or email only rows
- Two branches entered as two customers
How fuzzy matching finds the candidates
Fuzzy matching scores how alike two strings are. It lowercases, strips punctuation, drops suffixes such as Ltd, and compares what remains by edit distance, then checks phone numbers, tax numbers and email domains as separate fields.
The output is a list of pairs with a score. A shared phone number and a near-identical name sit at the top and are almost certainly one customer. The same town with a merely similar name sits lower and may be two real shops.
The tool proposes. It does not merge. A rule that merges on its own will one day join two separate buyers, and then their balances, credit limits and delivery addresses are tangled together.
- Normalise case, spacing and suffixes
- Compare name, phone, email and tax ID separately
- Score each pair and sort high to low
- Set a floor below which pairs are ignored
Why each merge needs an owner’s decision
A merge is a business decision with money attached. The surviving row decides which credit limit, price list and payment terms apply from then on, so someone who knows the customer must choose.
Sales may insist two rows are one buyer, while accounts knows they are separate companies with separate guarantors. Both views matter. The owner of the customer file settles it and writes the reason beside the pair, so the choice can be defended later.
Keep batches small. Fifty pairs a day beat five hundred on a tired Friday.
Move receivables onto one ledger
After the decision, open invoices, credit notes, receipts and unallocated payments all move onto the surviving record before the old row is touched. Otherwise the statement shows two balances and a payment sits against the wrong one.
Check total receivables before and after the merge. The figure must not change by a single unit. If it does, a row was dropped or counted twice, and you should stop and find it.
Keep the retired names as aliases on the master record. Next time someone types Smith Hardware Ltd, the system finds the right customer instead of creating a new one.
- Receivables total before and after
- Open invoices and credit notes moved
- Unallocated receipts reassigned
- Old names saved as aliases
- Merge logged with date and approver
Stop the duplicates coming back
Give every customer a short code and make it the only key. A picker with search stops people retyping a name, and the system can warn at entry whenever a similar name already exists in the file.
Name one person who approves new customers. The check takes a minute. It saves hours of cleanup, and it keeps a single owner answerable for the quality of the whole list.
Run the matching report each month so the list stays short and easy to clear. Start this week with your largest customer file.
Duplicate review sheet
Each candidate pair takes one line of this sheet. Columns hold record A, record B, match score, shared phone or email, receivable on each, surviving record, reason, decider and date. A tick box confirms balances moved and the alias saved.
Open a blank worksheet to printQuestions owners ask
Can software merge duplicates automatically?
It can for exact matches on a unique key. For near matches a person should confirm each pair, because a wrong merge mixes two customers’ balances.
What happens to the old customer rows?
Keep them as aliases or in an archive with a link to the surviving record. Deleting them removes the trail of what was merged.
Should we clean before or after moving to new software?
Clean before loading where you can. Duplicates loaded into a new system are harder to fix, because invoices already point to them.
How often should we check for duplicates?
Monthly for a busy customer file. Once a code and an entry warning are in place, the list stays short.