Prevent entering duplicate values in Excel
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.
You can find similar Excel Questions and Answer hereunder
1) Is there a way to get a log value of a number using Excel?
2) How can I prevent users from seeing / accessing my macro code?
3) How can I add and link a Chart Title to a cell value?
4) How can I ensure that user enters only certain acceptable values in an input cell?
5) I am not able to use VLOOKUP as the lookup value column is to the right of the column that has the information I need. Is there an alternative option?
6) How do I find the median salary of employees with the same skillset in my Organization?
7) How can I find the slab in which a given value fits?
8) In a Table , how can I remove duplicate records?
9) Is there a way I can easily identify the duplicate values in a dataset?
10) Given a raw data Table, how can I find the value of a field for a specific value of another field?