Disallow duplicates - Data Validation

How to prevent duplicates in data validation

Didn't find your answer?

I am trying to find how to prevent duplicates in data validation in Excel 2013.  I am sure that there used to be a tick box called 'Prevent Duplicates' or similar but am blowed if I can find it now.

Any help acknowledged and appreciated.

Replies (5)

Please login or register to join the discussion.

avatar
By alan.falcondale
23rd Sep 2016 15:32

this is my data validation for entry in cell F1
in the data validation selection select Custom
in the formula field enter something like
=IF(COUNTIF(F:F,F1)>1,FALSE,TRUE)

select OK
in column F I entered
a,b,c,d,e,f
in the following cell I entered 'a' again and the entry was refused

Thanks (1)
avatar
By alan.falcondale
23rd Sep 2016 15:33

not sure whether that was clear:
F1 = a
F2 = b
F3 = c
F4 = d
et cetera

Thanks (1)
Replying to alan.falcondale:
pic
By jndavs
29th Sep 2016 11:38

Isn't that what you wanted?
Entering 'a' again would be a duplicate.

Thanks (1)
avatar
By Richard Willis
29th Sep 2016 12:05

Many thanks for the replies and apologies for the delay.

Thanks (0)