CODEWITHSUNDEEP
excel
user2274337
user2274337

Reputation: 1

Data Validation for Unique Record and Specific Digit length

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...

Upvotes: 0

Views: 1101

Answers (2)

Doug Glancy
Doug Glancy

Reputation: 27478

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"

Upvotes: 1

Codeek
Codeek

Reputation: 1624

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))

Upvotes: 0

Related Questions