Block duplicates
PremiumEach customer ID may only be used once. The rule is set on A2:A6 and written for A2. In C2, count how many times A2 appears in A2:A6 with COUNTIF and test that it is exactly 1. Lock the list with $.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Customer ID | Name | Rule | |||
| 2 | 1005 | Anne Holm | ||||
| 3 | 1002 | Jonas Berg | ||||
| 4 | 1001 | Mette Lund | ||||
| 5 | 1003 | Ali Khan | ||||
| 6 | 1004 | Sara Juhl | ||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock Drop-down lists and data validation and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Why this works
COUNTIF($A$2:$A$6,A2) counts the cells in the list that equal A2. The ID counts itself, so a unique ID gives 1. 1005 appears once, so the test is TRUE.
Type 1002 in A6 instead of 1004, and the count for A6 is 2. The rule is FALSE, and Excel refuses the entry.
Common mistake: Testing for more than 1. That is the rule for highlighting duplicates. Data validation needs the rule for what is allowed, a count of exactly 1.
The answer
- C2
=COUNTIF($A$2:$A$6,A2)=1