In some workbooks, you might need to block duplicate entries in a column. For example, we don’t want 2 employees to have the same ID number. See how to set up a custom rule for that, with data validation. And keep reading, to see why COUNTIF can cause problems for you.
Block Duplicates in Column
When you’re working in Excel, you can use data validation to prevent duplicates from being entered in a column.
For example, in the video below, an Employee ID is entered in column A, and each ID must be unique.
If you try to enter a duplicate ID number, a data validation rule will block you, and a message appears.
- Tip: You could customize that message, to explain why the number is being blocked.
Video: Prevent Duplicate Entries in Column
Here’s a video that shows the steps for setting up the custom rule, so duplicate ID numbers can’t be entered in column A
There are written instructions below the video, and on the Data Validation Custom Rules page, on my Contextures site.
How to Block Duplicates
First, here’s how to block duplicate entries:
- Select the cells where you want the rule applied — A2:A3 in the screen shot below. (That range is named EmpIDs)
- On the Data tab, click Data Validation, and for Allow, select Custom
- In the Formula box, type this formula that checks for matching entries.:
- =COUNTIF(EmpIDs,A2) <=
The COUNTIF function counts any matches in the EmpIDs range, for the number in cell A2.
Then, if there’s more than one ID with that number, the entry will be blocked by the custom data validation rule.
Important: Read the next section, to see the COUNTIF problem, and how unique IDs could be incorrectly blocked by this rule’s formula.
COUNTIF Problems
In most Excel tables, this custom rule works well, but in some situations, the COUNTIF function might not work correctly, and that can cause problems.
For example, if your numbers are text numbers, instead of real numbers, COUNTIF will cause problems.
In the table shown in the screen shot below, product codes in column B must be unique.
In column E, the COUNTIF function shows 2 in some rows, and those formula results are highlighted in yellow.
Text Numbers and Real Numbers
In those cells, the COUNTIF function is counting “0123” and “123” as (false) duplicates.
That happens because COUNTIF treats text numbers like real numbers, so:
- “0123” is equal to 123
and
- “123” is also equal to 123.
To avoid that problem, instead of using the COUNTIF function, use SUMPRODUCT, as shown below.
SUMPRODUCT Function Solution
The SUMPRODUCT function treats text numbers as text, so “0123” is different from “123”
In the screen shot below, some SUMPRODUCT formula results, in column F, are highlighted in green, and they don’t have false duplicates.
Here’s the data validation formula you can use, if you have any “text” numbers in column B, where you want to block duplicates:
- =SUMPRODUCT(–($B$4:$B$22=B4))
Get the Sample File
Get the Excel sample file, and see more examples of data validation custom rules on my Contextures site.
On that page, there’s also a section on how to prevent duplicates in multiple columns. such as product name, size and colour combinations.
For example, you could enter multiple products that are jackets, in slze large, but each of those large jackets must be in a different colour.
___________________
How to Prevent Duplicate Entries in Excel Column
___________________