Data Validation for Unique Record and Specific Digit length

0
votes

I want to apply Data Validation on a cell. When a user enter a numeric value data validation should check its length 13 digits. If the length is greater than or less than 13 digits then message should be shows "Please enter 13 digit value" or do not accept the value. If the length is equal to 13 digits then the formula check duplicates within the range. If duplicate record found then a message should be shows "Please Enter Unique Records Only".

I am using this formula but it would not working properly...

=IF(LEN(A:A)=13,IF(COUNTIF(A:A,A1)>1,"duplicate",IF(LEN(A:A)<>13,"Please enter 13 digit","Please enter 13 digit")))

Thanks in Advance...

2
excel
A:A? Is there a cell as such? - Codeek
First thing don't use , use | (pipe), second your conditions don't add up to desired result. why print "Please enter 13 digit" twice - Codeek
Couldnt you use the built in data validation tool? - Geoffrey Fernandez

2 Answers

1
votes

This cleaned-up version of your formula would do what you want, I think, if entered in B1 and copied down. It only marks duplicates for the second occurrence onward:

=IF(LEN(A1)=13,IF(COUNTIF($A$1:A1,A1)>1,"duplicate",),"Please enter 13 digit")

For Data Validation, I think the best you can do is combine the two conditions, like:

=AND(LEN(A1)=13,COUNTIF($A$1:A1,A1)=1)

and use an Error Message like, "Please enter a 13-digit, non-duplicated value"

0
votes

Assuming A2 is the cell where value is entered, B2:B4 is the range of values and the value entered is whole number, Use this :

=IF(LEN(A2)<>13|"Not 13 Digits"|IF(COUNTIF(B2:B4|A2)>1|"DUPLICATE"|A2))