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

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest DEALS, OFFERS and News before anyone else.

You are almost done - please check your inbox for confirmation!

Pin It on Pinterest

Share This