Happy Friday!…time to Excel with another #formulafriday blog post in this series. Today let’s take a look at how to prevent duplicate name or employee ID numbers in a worksheet. So, how do we do that,?. We can create a simple solution with Data Validation and the COUNTIF Function. So for today’s example, we are going to use a really simple data set. It consists only of Employee ID’s. Your data set will be more complex but we are only looking to only perform an action on one column of data cells. Those which hold the Employee ID. So customising it to your particular needs should be straightforward.
The Example Data Set
In the example below Employee ID’s will be entered into the cell range B3:B10. We definitely do not want duplicates so let’s prevent the same number being entered twice.
- Select your range of cells B3:B10
- Home Tab
- Data
- Data Validation
- Allow Custom
Using The COUNTIF Formula
Thsis is where we can enter the COUNTIF formula. The syntax is
COUNTIF( range, criteria)
Where
range – This is the range of cells that you want to count based on the criteria.
criteria – This is the criteria used to determine which cells to count.
=COUNTIF B$3:$B$10,B3) <= 1
So, the formula has two arguments, =COUNTIF($B$3:$B$10,B3), this counts the number of values in the range B3 to B10 that are equal to or less than the value in B3. The range is absolute by putting the $ around the range, to fix the reference.
So do you want to give the user who enters a duplicate value to receive a warning?
In the Error Alert Tab you can customise the error message, in this case we can put something like-
Employee ID Already Used!
Let’s test it….I try to enter EN10009 into cell B10 and see the response from Excel.
There we go, trying to enter EN10009 which is a duplicate value in the cell range B3:B10 our customised warning is displayed. Job Done.
If you want more tips then sign up to my monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series you can do so by clicking on the link below.
How To Excel At Excel – Formula Friday Blog Posts.
