Cimaware Software

Summary

We will use the Data Validation feature in Excel to make sure that duplicate entries are flagged as soon as they are entered. Our solution will give the user an immediate message indicating that the value just entered is a duplicate value.

NOTE: The data validation approach is not without a weakness. This approach only works for values entered directly into cells and not if the value in a cell is copied and pasted into it or if the value is a result of a calculation.

Detailed Solution

1.

For this example, we will enter a simple list of numbers to demonstrate how Excel can check for duplicates when entering data. First we will highlight the area where we will be entering data. In this example, we will highlight the range from A1 to A10.


 

2.

Next we go to MAIN MENU >> DATA >> VALIDATION. The following window is presented. Under the Settings tab, select Custom for the Allow field. In the Formula field, enter the following formula: =COUNTIF($A$1:$A$10,"="&A1)=1.


 
 

3.

Now move over to the Error Alert tab. Select the Show error alert after invalid data is entered , option. Under the style option, choose Stop . For the Title of the message, enter Duplicate Encountered . Under Error message , enter, The value you have just entered is already in the list. Please check and re-enter. Press OK .
 

 
 

4.

To check if this works, lets enter some values. In cell A1 I enter 1 , in A2 I enter 2 and in A3 I enter 3 . Then in cell A4 I enter 1 and I get the following message.