Merging contact lists gives me duplicates
Three lists went in and the combined file has the same people more than once, often with slightly different spellings, capitalisation, or company names.
Why this happens
- Names are not identifiers. “Jo Smith”, “Joanne Smith”, and “SMITH, Jo” are one person and three strings, and no exact match will ever join them.
- Even a stable field fails on presentation. Trailing spaces, differing capitalisation, and display forms like “Jo Smith <jo@northwind.co>” all defeat an exact comparison between values that are genuinely the same.
- Each source also has its own idea of the columns. One sends Full Name, another First and Last, a third sends Contact. Concatenating them produces a fourth shape that matches none of the originals.
What to do right now
- Pick one field that identifies a person and merge only on that. Email is usually the only candidate in a contact list.
- Normalise before comparing: trim, lowercase, and strip any display wrapper. Most apparent duplicates are the same value wearing different clothes.
- Decide which source wins per field, not per row. The newest list may have the better phone number and the worse job title.
- Keep a column recording where each row came from. When two sources disagree later, that column is the only thing that lets you work out which to trust.
Stopping it on the next file
Mark the email field unique and merging stops being an operation you perform: rows join on it, so three partner lists produce one contact per person rather than three. Typing it as an email also refuses the malformed addresses at the door, which is where they are cheap, rather than at a sending tool, where they cost deliverability.
The contact list template is a sheet with those rules already set, if you want to see the shape of one before building your own.
Other things that go wrong
“N/A” and dashes in a column that should be numbersA numeric column contains “N/A”, “-”, “n.a.”, or “(blank)” alongside real figures. Totals are wrong or the import refuses the whole column.My CSV columns are shifted or split in the wrong placesMost rows look right, but some have values in the wrong columns: a postcode in the country column, a phone number where the email should be. The rows that are wrong all seem to have something in common.My supplier's prices are a thousand times wrongPrices from a European supplier are out by a factor of about a thousand, or occasionally by a factor of a hundred. Nothing errored. The totals are simply wrong, and some individual rows look perfectly reasonable.The plus sign disappeared from my phone numbersInternational phone numbers have lost their leading plus. Some cells show an error instead, and some have collapsed into a single run of digits with the spaces gone.
Define the sheet once
Name your fields, and every file after that is matched to them and checked cell by cell before a row is allowed to land.