Confident Cells

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.

Your turn

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.

ABC
1Customer IDAmount (£)Check
27407130
37383124
47380129
57375128
67371222
77379145
87413184
97378287
10739544
117397272
127372251
137371175
147393213
157386154
16737926
177413295
187395151
19740171
207371228
21737748

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

ABCDE
1IDDateRepeats?Keep?Same ID and date?
2E10401/04/2026
3E11701/04/2026
4E10408/04/2026
5E12108/04/2026
6E10408/04/2026
  1. =IF(COUNTIF($A$2:$A$6,A2)>1,"Yes","") in C2. E104 appears 3 times, so all three rows show Yes.
  2. =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.
  3. =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

  1. 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.
  2. In C2, show Keep the first time each customer ID appears in the list, and Remove for every later copy.
  3. Some customers appear more than once. In H1, work out how many different customer IDs there are.
  4. 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.