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.