Cleaning duplicates in a contact list: write the match rules before you delete anything

A duplicate is defined by a rule, not by the sheet

Two rows are the same contact because of something you decide counts: the same email address, the same organisation name and postcode, the same person at the same organisation. Any rule makes two kinds of mistake. It can miss a real duplicate, which leaves the list untidy. Or it can merge two different contacts, which loses information about a real person or organisation. The second is usually worse, so a cautious rule leaves uncertain pairs alone.

What Excel's Remove Duplicates actually does

Microsoft's page describes it precisely. You choose the columns that form the key. If every ticked column matches, the whole row is removed, including the columns you did not tick, and the first occurrence is kept. The comparison uses what is displayed in the cell, not the stored value, so the same date shown as 3/8/2006 and as Mar 8, 2006 counts as different. The removal is permanent apart from Undo, and the page recommends copying the original data first. Power Query's Remove Duplicates likewise works on the columns you select.

These are sound tools for exact repeats. They are not designed for near-matches such as "Brook & Sons Ltd" and "Brook and Sons Limited", and they have no memory of what was removed.

• Exact repeat, same displayed values: safe to merge by rule.
• Near match with different spelling: a rule or a review is needed.
• Same value, different people: the tools cannot tell, so you must.

Normalise a copy for comparing, and keep the original columns

Build separate comparison columns: trim spaces, ignore letter case, standardise company suffixes such as Ltd and Limited, remove punctuation and standardise postcode spacing. Compare on those, but never overwrite the original values. If a merge is wrong, you want the original text back.

Watch for chains. If A matches B and B matches C, but A and C differ in important ways, merging all three on the strength of the chain may be wrong.

Shared fields are not proof

Two people can share an address, a phone number or a role mailbox such as an information address. Two organisations can share a building. A surname is not an identity. Write down, with examples, the cases where a shared field must not cause a merge, and test the rule against a handful of look-alike pairs that you know are different.

• Same email, different names: could be a shared mailbox; review.
• Same name and postcode, different email: possibly the same person; review or merge by an explicit rule.
• Same organisation, different contacts: keep both; they may be different people.

Make every merge reversible, and know the limits

A merge log records each surviving row, each row merged into it and the rule that matched. The row counts must reconcile: original rows equal cleaned rows plus merged-away rows. Uncertain pairs go to a review file with both rows left in the list. The original file is never altered.

The fixed-scope job from £195 (an untested proposal) works on one list of up to 10,000 rows of products, parts, assets or companies that holds no personal data, with rules you write. It is accepted by the row reconciliation, a test set of at least 20 true duplicate pairs and 20 look-alike pairs with half of them kept back from us until the rules are fixed, the review file and an unchanged original. Payment follows your sign-off.

Lists of people, with names, email addresses or phone numbers, are not covered. They are out of scope until a secure handover route exists, and none has been set up yet. We do not contact anyone named on a list, check whether addresses exist or advise whether you may use a list for marketing or whether you comply with data-protection law; that is a legal question for you. Work starts only after a secure way to hand the file over has been agreed in writing. The first enquiry contains only column headings, counts and invented examples, never the list.