Data Validation skips rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
jk said:
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?
Click to expand...

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

---
 
Back
Top