Course / Clean & Check Data

Finding duplicates

Flag repeated sign-ups, tell first entries from repeats, and count how many different people you really have. Intermediate · 5 min · COUNTIF, Expanding ranges, UNIQUE, ROWS

Web forms collect duplicates. People press Submit twice, come back a week later to make sure it worked, or type their email with a capital letter. Before the running club sends out welcome packs, it needs to know who is really on the list.

The email address makes the best key: two members can share a name, but not an inbox. Two COUNTIF patterns do most of the work:

• COUNTIF($B$2:$B$11, B2) counts how often this row's email appears in the whole list
• COUNTIF($B$2:B2, B2) counts it only from the top down to this row, so the first copy scores 1, the next 2, and so on

Steps

1. In C2:C11, flag every email that appears more than once: count it across the whole list with COUNTIF, then test whether the count is greater than 1. Lock the list as $B$2:$B$11 so it stays put as you copy down.

2. To keep each person once, number the copies instead. In D2:D11, use =COUNTIF($B$2:B2, B2). Only the start of the range is locked, so the range grows by one row each time you copy it down.

3. Turn the copy numbers into a decision. In E2:E11, show First where the copy number is 1, and Repeat otherwise.

4. So how many different people signed up? In B14, let UNIQUE strip out the repeats and count what is left with ROWS: =ROWS(UNIQUE(B2:B11))

Remember

• COUNTIF($B$2:$B$11, B2)>1 flags every copy of a repeated value, originals included.
• COUNTIF($B$2:B2, B2) counts only down to the current row, so 1 marks the first copy.
• ROWS(UNIQUE(range)) or COUNTA(UNIQUE(range)) counts the distinct values.
• COUNTIF and UNIQUE both ignore upper and lower case.

Previous: Spotting problems with IS functions · Next: Mapping codes with CHOOSE and SWITCH