"jk" wrote:
[Quoted Text] > 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
---
|