Software Tips

Disallow duplicates in MS Excel column

Making a list of product SKUs, trying to avoid duplicates.  Found this data validation post

Click on Data tab>Data Validation Icon

Click on the Allow: drop down and change to Custom
In Formula field enter: =COUNTIF($A$2:$A$1500,A5)=1
A2 being the start and A1500 being the end of the list.

Explanation: The COUNTIF function takes two arguments. =COUNTIF($A$2:$A$20,A2) counts the number of values in the range A2:A20 that are equal to the value in cell A2. This value may only occur once (=1) since we don’t want duplicate entries. Because we selected the range A2:A20 before we clicked on Data Validation,Excel automatically copies the formula to the other cells. Notice how we created an absolute reference($A$2:$A$20) to fix this reference.

Similar Posts