Home » Questions » Computers [ Ask a new question ]

Excel validation - can it be used to make sure duplicates are not entered?

Excel validation - can it be used to make sure duplicates are not entered?

I have an excel spreadsheet - is it possible to have validation so that a column cannot contain a value that already exists in the column?

Asked by: Guest | Views: 22
Total answers/comments: 1
Guest [Entry]

"Yes, from this example ...

Select a range of cells, for example, A2:A20.
Select Data -> Data Validation (in Data Tools Group).
In Data Validation dialog box, select Settings -> Allow -> Custom.
In the Formula box, enter the following formula: =COUNTIF($A$2:$A$20,A2)=1
Select the Error Alert tab.
In the Title box, enter: Duplicate Entry.
In the Error message box, enter: The value you entered already appears in the list
above.
Click OK."