Monday, November 29, 2004

MS Excel:Prevent Duplicate entries within a range of cells

Suppose you want to prevent duplicate entries in the range of cells A1:A10:

Select cell A1.
Choose "Validation" from "Data" menu.
Choose "Settings" tab.
Choose "Custom" from "Allow" drop-down list.
In the Formula box, enter the following =NOT(OR(COUNTIF($A$1:$A$10,A1)>1))
Choose the "Error Alert" tab in order to set an alert style.
Set Style to Stop (the alert message with a Stop sign on it).
In the Title box, enter what you want to appear in the Error Alert's title bar (for example, "Duplicated Entry"); in the Error Message box, enter the message box text ("Please type an entry not entered previously," for example).
Choose "OK".
Back in the worksheet, select cell A1;
then, using the square AutoFill handle, drag to fill cells A2:A10.

No comments: