Duplicates: practice exercises
COUNTIF to spot repeats, first copies, UNIQUE, COUNTIFS. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
Some customers were entered more than once. In C2, show Duplicate next to every row whose customer ID appears more than once in the list, and leave the others blank.
| A | B | C | |
|---|---|---|---|
| 1 | Customer ID | Amount (£) | Check |
| 2 | 7407 | 130 | |
| 3 | 7383 | 124 | |
| 4 | 7380 | 129 | |
| 5 | 7375 | 128 | |
| 6 | 7371 | 222 | |
| 7 | 7379 | 145 | |
| 8 | 7413 | 184 | |
| 9 | 7378 | 287 | |
| 10 | 7395 | 44 | |
| 11 | 7397 | 272 | |
| 12 | 7372 | 251 | |
| 13 | 7371 | 175 | |
| 14 | 7393 | 213 | |
| 15 | 7386 | 154 | |
| 16 | 7379 | 26 | |
| 17 | 7413 | 295 | |
| 18 | 7395 | 151 | |
| 19 | 7401 | 71 | |
| 20 | 7371 | 228 | |
| 21 | 7377 | 48 |
How it works
COUNTIF($A$2:$A$21,A2) counts how many times this row's ID appears in the whole list, so more than 1 means it's a duplicate. Lock the whole range.
To keep only the first copy, count from the top down to this row: COUNTIF($A$2:A2,A2). Only the start is locked, so the range grows as it's copied down, and the first copy is the one where the count is 1.
UNIQUE lists each value once. COUNTIFS checks two columns at once, such as the same person on the same date.
=COUNTIF($A$2:$A$6,A2) =COUNTIF($A$2:A2,A2) =UNIQUE(A2:A6) =COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2)
Counting each value
COUNTIF(whole list, this row) counts how many times this row's value appears. 1 means it's unique; more than 1 means it's repeated. Lock the whole list with $ so it doesn't move as you copy down.
That marks every copy, the first one too. To keep one copy of each, count only from the top down to this row instead.
Worked example
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID | Date | Repeats? | Keep? | Same ID and date? |
| 2 | E104 | 01/04/2026 | |||
| 3 | E117 | 01/04/2026 | |||
| 4 | E104 | 08/04/2026 | |||
| 5 | E121 | 08/04/2026 | |||
| 6 | E104 | 08/04/2026 |
=IF(COUNTIF($A$2:$A$6,A2)>1,"Yes","")in C2. E104 appears 3 times, so all three rows show Yes.=IF(COUNTIF($A$2:A2,A2)=1,"Keep","Remove")in D2. The range grows as it goes down: $A$2:A2, then $A$2:A3... Only the first E104 has a count of 1, so it's the one to Keep.=IF(COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2)>1,"Yes","")in E2. COUNTIFS checks both columns. Only the two E104 rows on 08/04 are true duplicates: the same person on the same date.
How many different?
UNIQUE(A2:A6) lists each value once, spilling down the sheet. Wrap it in COUNTA to count them: =COUNTA(UNIQUE(A2:A6)) gives 3 here (E104, E117, E121).
What you'll practise
- Some customers were entered more than once. In C2, show Duplicate next to every row whose customer ID appears more than once in the list, and leave the others blank.
- In C2, show Keep the first time each customer ID appears in the list, and Remove for every later copy.
- Some customers appear more than once. In H1, work out how many different customer IDs there are.
- Each person should book a room only once per day. In D2, show Duplicate next to every booking where the same person has more than one booking on the same date, and leave the others blank.