Course outline / Data cleaning
Lesson 57 of 75 · Intermediate · 4 exercises
Finding duplicates
Spot repeated entries and count how many unique ones there are.
What you'll practise
- How many times is it listed?
- Flag duplicates
- Keep the first, flag the repeats
- How many extra copies?
How many times is it listed?
COUNTIF on the whole column, with the current row as the criteria, tells you how often each value appears.
In cell C2, count how many times A2 appears in $A$2:$A$7.
Flag duplicates
In cell C2, show "Duplicate" if A3 appears more than once in $A$2:$A$7, otherwise "Unique".
Keep the first, flag the repeats
To flag only the second and later copies, count from the top down to the current row with an expanding range.
In cell C2, count A4 within $A$2:A4. A result above 1 means it was seen before.
How many extra copies?
Once you know an entry is repeated, its count minus 1 is the number of extra copies to remove.
In cell C2, work out how many extra copies of ana@shop.com there are: COUNTIF(A2:A7,"ana@shop.com")-1.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.