prevent entering duplicate values in Excel
Q41. In Excel, how can I prevent users from entering duplicate values in a range?
For example, while preparing a list of 30 invitees for a function, you want to avoid entering the same name more than one time.
To do it in Excel, here is the answer:
a) Select the range where duplicate entries have to be avoided. Under "Data" tab, click Data Validation -> Data Validation.
b) Select "Custom" for allow field and enter =COUNTIF($AM$2:$AM$31,AM2)=1 in Formula field where $AM$2:$AM$31 corresponds to the range where duplicate entries have to be avoided.
c) Click on "Error Alert" tab. Check "Show error alert after invalid data is entered" option if it is not checked already. Change the Title and Error Message fields as appropriate.
c) When user enters a name (John Miller in the example below) that already exists in the range, error message appears asking user to retry or cancel.