Friday, June 3, 2011

Prevent duplicate entries in a column


  1. Select the range of cells in which you want to prevent duplicates (for example, B2:B1048576).
  2. From the Data menu select Validation.
  3. Click on the Settings tab.
  4. From the Allow dropdown box select Custom.
  5. In the formula box type =COUNTIF($B$2:$B$1048576,B2)=1 (see explanation below)
  6. Click on the Error Alert tab.
  7. In the Title box type "Duplicate Entry!"
  8. In the Error Message box type "You cannot enter a value already in the list."
Explanation:

This formula tells Excel to count how many times the data from the current cell is included in the validation range. If the answer is equal to 1 then the formula =COUNTIF($B$2:$B$1048576,B2)=1 is TRUE and everything is OK. However, if the answer is not equal to 1, then the formula is FALSE and an error occurs causing the Error Alert.

No comments:

Post a Comment