A list merged from two sources always has people in it twice, and rarely typed the same way twice. Cleaning the text first makes the copies match, and a COUNTIF whose range grows as it is copied down counts only the rows above, so the first sign-up is kept and only the repeats are flagged.
You are organising a charity fun run, and the sign-ups from the paper forms and the website have been pasted into one list in A2:B10. Before the reminder email goes out, you want everyone on it once. In C2, turn the email in B2 into a clean one: no spaces at either end and all lower case. Copy it down to C10. Then in D2, show "Keep" if that clean email has not appeared in any row above it, and "Remove" if it has, so the first sign-up for each person is kept. Copy that down to D10 too.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Name | Email as entered | Clean email | Action |
| 2 | Priya Shah | priya.shah@example.com | ||
| 3 | Tom Ellery | tom.ellery@example.com | ||
| 4 | Grace Okafor | grace.okafor@example.com | ||
| 5 | Priya Shah | Priya.Shah@example.com | ||
| 6 | Daniel Hart | dan.hart@example.com | ||
| 7 | Tom Ellery | tom.ellery@example.com | ||
| 8 | Megan Lowe | megan.lowe@example.com | ||
| 9 | Dan Hart | DAN.HART@EXAMPLE.COM | ||
| 10 | Sam Reid | sam.reid@example.com |
Two problems sit in this list, and each needs its own tool. The first is that a duplicate rarely looks like one. "Priya.Shah@example.com " has capitals and a trailing space, and " tom.ellery@example.com" has a leading space, so as far as any comparison is concerned they are different text. TRIM removes the spaces at either end and LOWER folds the capitals, and after =LOWER(TRIM(B2)) every copy is character for character the same as its original. COUNTIF would in fact ignore the capitals on its own, which is why the DAN.HART row is caught even on the raw column, but it would not ignore the spaces, so without column C two of the three repeats slip through. The second problem is deciding which copy to keep. COUNTIF($C$2:$C$10,C2) counts every appearance in the whole list, so Priya's first row and her second both score 2 and both get removed, and she drops off the list entirely. Anchoring only the start of the range, $C$2:C2, makes the range grow as the formula is copied down: in row 5 it covers C2:C5, the rows up to and including this one. The first time an address appears, the count is 1, because only this row matches; any later time, the earlier row is in the range as well, so the count is 2 or more. That keeps rows 2, 3 and 6, the first sign-ups for Priya, Tom and Daniel, and marks rows 5, 7 and 9 to remove, leaving six people. Notice that the names are no use for this: "Dan Hart" and "Daniel Hart" are the same person, while two different people can share a name, and the email is the field that really identifies who signed up.