Preventing Duplicates When Entering Data

 

Problem:

We want to make it impossible to enter duplicate values in List1 (cells A2:A10).

Solution:

Use Data Validation as follows:
1. Select the range to validate (cells A2:A10)
2. Data –> Data Validation –> Settings
3. From the Allow dropdown list, select Custom.
4. Enter the following COUNTIF formula in the Formula box:
=COUNTIF($A$2:$A$10,A2)=1
5. Select the Error Alert tab.
6. In the Title box, enter “Duplicate Entry”.
7. In the Error Message box, enter “The value you entered already appears in the list above.”
8. Click OK.

After following this procedure, any duplicate entry will cause the error message to pop up, forcing the user to enter a different value.



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>