Two records for the same customer look like a small annoyance. In practice they mean an invoice sent to the wrong address, a payment that does not match anything, a supplier paid twice, or a salesperson who does not know that a colleague spoke to the same client last week. The longer the records are left, the more history is split across them.
This article describes a cleanup that one person can run in about two weeks, without special software, and that does not break anything you cannot undo.
Why duplicates appear
Knowing the cause tells you what to fix afterwards. The common ones are:
- Different spellings. "Novak s.r.o.", "Novak sro" and "NOVAK Ltd" entered by three people.
- No check before creating a record. Nobody searches first, because searching is slow or the result is unclear.
- Several systems. The same customer exists in the CRM, the invoicing tool and the order system. For that case see reconciling customers between CRM and invoicing.
- Real changes. A company moves, renames or is taken over, and a new record is created instead of updating the old one.
Step 1: choose one owner (day 1)
Pick one person who decides on every merge and answers questions from others. This is usually someone from operations or finance who knows the customers and suppliers, not the most senior person. Others may suggest merges; the owner decides.
Tell the people who create records that the cleanup is starting, so that they do not add new duplicates in the meantime.
Step 2: write the matching rule (day 1-2)
Before you look at a single pair, write down in plain words when two records are the same company. A short rule is enough:
- Certain match: the same tax or company ID.
- Probable match: the same name after removing legal forms and punctuation, and the same postal code or the same bank account.
- Do not merge: different tax IDs, even with the same name. Branches and sister companies often share a name.
Records that fall under "probable" are checked by the owner by hand. Records that fall under "certain" can be merged in a batch once the owner has looked at a sample.
Step 3: export and list the candidates (day 2-4)
Export customers and suppliers into a spreadsheet with ID, name, tax ID, address, bank account, created date and the date of last activity (invoice, order or payment). Then:
- Sort by tax ID and mark repeated values.
- Make a helper column with the name in lower case, without legal forms and punctuation, and mark repeated values.
- Mark pairs that fall under the rule and add one column: keep, merge into or leave.
Do not try to find every duplicate. Work first on records with activity in the last twelve months; old, inactive records can wait.
Step 4: merge carefully (day 5-10)
For each pair, the owner decides which record survives. The usual choice is the one with the most history or the correct tax ID. Then:
- Never delete. Merge if your system supports it. If it does not, mark the old record as inactive and write "replaced by [ID]" in a note.
- Keep the history. Open invoices, orders and contracts must end up linked to the surviving record, or remain readable on the old one.
- Check open payments first. For suppliers, confirm that no unpaid invoice is hanging on the record being retired.
- Log every merge. One line in the spreadsheet: date, old ID, surviving ID, who decided. This is your way back if a merge turns out to be wrong.
Start with ten pairs, ask someone who works with these customers to look at the result, and only then continue with the rest.
Step 5: stop them coming back (day 11-14)
A cleanup that is not followed by a prevention rule is only a pause. Agree on three small things:
- Search before create. Anyone who adds a customer or supplier first searches by tax ID and by name. If your tool allows, make the tax ID a required and unique field.
- One way of writing names. For example the legal name from the company register, without extra notes in the name field.
- A monthly check. The owner repeats the sorting from step 3 on records created in the last month. It takes about half an hour.
What to avoid
- Merging by name only. Two companies with the same name are not always one company.
- Cleaning everything at once. Records with no activity do little harm; start where the work is.
- Several people merging. Without one owner, the same pair gets merged two ways.
- Deleting to save space. Storage is cheap; lost history is not.
Summary
Duplicate customer and supplier records can be reduced in two weeks with one owner, a matching rule written in plain words and a log of every merge. Merge instead of deleting, begin with active records and finish by adding a search-before-create step and a short monthly check. If duplicates come from several systems rather than from careless entry, solve that first; otherwise the cleanup will need to be repeated.
Sources
- Our related article linked above.
- The matching rule, the two-week plan and the monthly check are a working practice, not a standard; adapt them to your systems and team. Examples are illustrative. No client results or market figures are claimed in this article.
