The same email address is in my list twice

A contact list contains what is obviously the same person more than once. The addresses look identical until you notice the capitalisation, or a stray space, or that one of them is wrapped in a display name.

Why this happens

  • Exact string comparison is case-sensitive, and email addresses are routinely written in whatever case the person typed. “Jo@Northwind.co” and “jo@northwind.co” differ by two characters and are the same mailbox.
  • The specification is partly to blame for the confusion. The domain half is definitively case-insensitive; the local half, before the at sign, is technically allowed to be case-sensitive, and no mail provider anybody actually uses treats it that way. So the pedantic reading says these might be two people and the practical reading says they are one.
  • Display wrappers cause the rest. A field exported as “Jo Smith <jo@northwind.co>” is not an email address, it is an address inside a label, and it will not match the bare form no matter how you compare them.
  • The consequence is not just an untidy list. Two records for one person means two emails sent, which is the thing most likely to get a sending domain complained about.

What to do right now

  • Lowercase and trim before comparing anything. That one step collapses the large majority of apparent duplicates in any contact list.
  • Strip display wrappers first, so that what you are comparing is the address rather than the presentation of it.
  • Decide which record wins per field rather than picking a row. The older record often has the better company name and the newer one the better job title.

Stopping it on the next file

An email field is stored lowercased, so the case a sender happened to type stops being a difference at all. Marking it unique then compares without regard to case, which means “Jo@northwind.co” arriving after “jo@northwind.co” is caught and named against the row it duplicates rather than added. Across deliveries the same field is what makes three partner lists merge into one contact per person.

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

My yes/no column has five different spellingsA column that only ever means yes or no contains several spellings of each. Filtering for “Yes” misses rows, and a count of the yeses is lower than it should be.My CSV opens with everything in one columnThe file opened, but every row sits in a single column with the separators still visible in the text. Widening the column shows the whole line, commas or semicolons and all.VLOOKUP fails on values that look identicalA lookup or a join returns nothing for rows that plainly should match. Copying both values into a cell and comparing them returns false, even though they look the same on screen.My export has subtotal rows mixed in with the dataThe file has real rows, but also rows that are section headings with one cell filled, subtotal rows with a figure and no product, and a grand total at the bottom. Loading it as-is produces totals roughly double the truth.

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.

Start freeAll fixes