Stop Duplicates!

Let us learn a nice trick today, which can help you to stop duplicates from entering your file.

There are definitely good tools to easily track and report duplicates, like the “Highlight duplicates” feature of the Conditional Formatting group, but we are going to learn a preventive mechanism whereby the duplicate is arrested on entry itself and not as a post mortem exercise.

We shall use the technique of Data Validation to get this done. Given below is a sample scenario and solution:

1.  Select the range in which you want to prevent duplicates – this could be say the range of cells from G1 to G80.
2. From the Ribbon, click on Data->Data Validation (alternate is to use the shortcut keys Alt+D, followed by L)
3. In the first tab of the dialog (Settings), select “Custom” from the “Allow” dropdown box.
4. Now you will get a box to enter formula.
5. In the formula box type this:  =COUNTIF($G$1:$G$80,G1)=1
6. Your sentry tool is now ready for operation, but we will give it some dialogue to display.
7. You have an option to present a customized error message to the users who enter a duplicate value.  To configure this, select the Error Alert tab of the dialog box.
8. In the Title box type "Stop Duplicates!" (or any such thing you like – may be add in your name too to make it very customized)
9. In the Error Message box type "You are trying to enter a duplicate value. Please enter non-duplicates only. " (or any such thing you like)
10. We are all set , just click on the OK button to set the validation on.

The formula used:

If you see the formula used in Step 5 above, it is a simple COUNTIF formula which tells Excel to count how many times the data from the current cell (G1) is included in the range G1:G80.

Excel automatically changes the current cell reference – so if you go to Cell G23 and open the data validation rule again, you will see that the formula would have changed to G23 – like this: =COUNTIF($G$1:$G$80,G23)=1

The formula evaluates and if the count of such values is equal to 1, it means there is no duplicate value. The condition evaluates to TRUE and everything passes through OK. However, if there is a duplicate being entered, the formula evaluates to FALSE (answer not equal to 1), and triggers the error alert, which you have customized above.

Now you have the right tools with you, so just go ahead and stop the dupes ;)

Note: Some of the tips shown here are extracts from my book on "Excel for the CEO" - details available atwww.mrexcel.com/ceo.shtml. You can also find this in the ebook edition "Excel for the Small Business Owner" available for online ordering at www.mrexcel.com/sbo.shtml