Level 1 · Duplicate customers by email

A duplicate is rarely an exact copy. The same person signs up again on their phone, the keyboard capitalises the first letter, or a paste drags in a space. To a person, owen.hudson@icloud.com and ' owen.hudson@icloud.com' are one address. To a spreadsheet they are two different strings. So the honest way to find duplicates has two parts: clean the value into a key, then count the key.

Counting with COUNTIF

COUNTIF(range, criteria) counts the cells in a range that match a value:

=COUNTIF($F$2:$F$54,F2)

reads “how many cells in F2:F54 hold the same value as F2”. Every row finds at least itself, so the answer is 1 for a unique email and 2 or more for a duplicate. Add >1 and the cell becomes a TRUE/FALSE flag:

=COUNTIF($F$2:$F$54,F2)>1

The range is locked with $ because every row must search the same column. Unlocked, G3 would search F3:F55, and the last rows would search almost nothing.

What COUNTIF already forgives, and what it doesn’t

Try the quick version first, straight on the raw emails:

=COUNTIF($D$2:$D$54,D2)>1

It flags 24 rows. COUNTIF compares text without caring about case, so NICOLEJAMES1982@ICLOUD.COM in D38 matches nicolejames1982@icloud.com in D12. But it does compare spaces: D39 holds ' owen.hudson@icloud.com' with a leading space, and D2 holds the address without it. Those two never match. Six accounts in this slice differ from their original only by that space, so the raw count misses 12 rows, both halves of six pairs.

Original	Duplicate	Raw COUNTIF
sortiz38@outlook.com (D29)	same text (D37)	found
nicolejames1982@icloud.com (D12)	NICOLEJAMES1982@ICLOUD.COM (D38)	found (case is ignored)
owen.hudson@icloud.com (D2)	' owen.hudson@icloud.com' (D39)	missed (the space counts)

Build the key first

A helper column holds the cleaned value so the count can work on it:

F2   =LOWER(TRIM(D2))

TRIM removes spaces at both ends (and squeezes double spaces inside to one). LOWER puts every letter in lower case. COUNTIF would forgive the case anyway, but the key should be clean on its own: other tools that will use this column, like SQL, pandas or a merge in your CRM, do care about case. Fill it down, then count the key:

G2   =COUNTIF($F$2:$F$54,F2)>1

Now all 18 pairs show up: 36 rows flagged. In J1, =COUNTIF(G2:G54,TRUE) counts them, and that number is your sanity check. Each duplicate account comes with its original, so the count should be even. An odd number means a key you didn’t expect.

Same name is not the same person

Douglas Diaz (row 19), Jamal Diaz (row 32) and Timothy Diaz (row 16) are different customers with different emails, and the key keeps them apart. Matching on names would merge strangers.

Which copy to keep is a business call, not a formula. Here the duplicates have the higher ids and no orders, so support keeps the original and moves the login over.

Excel’s own tools: highlight, then remove

Desktop Excel has two built-in tools for this, and both work best on the key column you just built:

• See them: select F2:F54, then Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Every key that appears more than once turns red. Like COUNTIF, the rule ignores case and doesn’t trim, which is why you run it on F, not on the raw emails in D. Sorting by the key puts each pair on neighbouring rows.
• Remove them: click inside the data, Data > Data Tools > Remove Duplicates, tick “My data has headers”, and tick only email_key as the column that decides. Excel keeps the first row of each group, top to bottom, and deletes the rest. Sorted by customer_id as here, that keeps the original account.

Remove Duplicates changes the data and tells you only how many rows went. Run it on a copy, after the flag column has shown you exactly what it will remove. Google Sheets has the same tool under Data > Data cleanup > Remove duplicates.

Common mistakes

• Counting the raw column. It looks right because most pairs appear; the ones with a stray space don’t.
• An unlocked range. =COUNTIF(F2:F54,F2) filled down becomes COUNTIF(F54:F106,F54) by the last row, and the bottom rows stop finding their pairs.
• Flagging only the second copy. COUNTIF(...)>1 marks both halves of a pair on purpose. To mark only the later one, count the rows above: =COUNTIF($F$2:F2,F2)>1.