De-duplicating cells in Excel-Solution 3
One frequent requirement in Excel is to check for duplicates while you are entering data into a spreadsheet.
Imagine that you are manually entering address information for a list of contacts and you find out that most of the entries you made were duplicate entries. Fortunately, Excel has built-in features to handle duplicate checking on the fly. In this tutorial we look at a simple example of how to get Excel to check for duplicates when entering data.
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
-
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.
-
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.
-
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 .
-
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.
