Data Validation skips rows
 

I am using data validation to prevent duplicates in a column and the
validation i am using is =COUNTIF(A:A,A1)<2 .This works on some sheets but on
others, it skips every other two rows. What would cause this?

Data Validation skips rows
 

"jk" wrote:
I am using data validation to prevent duplicates in a column and the
validation i am using is =COUNTIF(A:A,A1)<2 .This works on some sheets but on
others, it skips every other two rows. What would cause this?

Assuming the validation is applied correctly .. not sure, perhaps data entry
consistency? Eg: some text entries may contain "invisible" extraneous
whitespaces (leading, in-between, trailing) leading to non trigger of the
data validation for what looks to be duplicates

Try instead the validation formula:
=SUMPRODUCT((TRIM($A$1:$A$1000)=TRIM(A1))*(TRIM($A $1:$A$1000)<""))<2
which allows use of TRIM, unlike COUNTIF

For calc efficiency, use the smallest range sufficient
to cover the max expected data entry extent in col A
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---