prevent duplicate with condition

[vincy]

I like to make excel sheet were in column A i like to enter a list of unique id numbers
of 10 digit. I want that if i enter a previously entered number then excel should prevent me, plus if by mistake i enter a 9 digit or 11 digit then also it should alert me.
The corresponding name to the id number should not change even if i shuffle the id nos in 'ascending' or 'descending' order. Pls guide

 

[NARAYANK991]

Hi ,

Assuming your data entry starts from cell A2 , select any reasonable data entry range , say A2:A300 , and after clicking on Data Validation , Custom , enter the following formula :

=AND(COUNTIF($A$2:A2,A2)<=1,LEN(A2)=10,INT(A2)=A2)

Narayan

 

[Abhijeet R. Joshi]

Hi Narayan,

Recently for a similar post @Luke had mentioned the same DV procedure with the formula as
COUNTIF($A$2:A2,A2)<2
and now you have mentioned as
COUNTIF($A$2:A2,A2)<=1

Can you help me with the use of (<2) and (<=1) ??

 

[Luke M]

the < and > symbols are mathematical notation for less than and greater than respectively. Additionally, we can add the "=" symbol to make "<=" and ">=" which represent "less than or equal to" and "greater than or equal to". When dealing with integers (ie, whole numbers), there is no mathematical difference between saying "less than 2" or "less than or equal to 1".

 

[Abhijeet R. Joshi]

Thanks for the explaination Luke..

Moreover, why was this combined with the countif formulas if what I am curious for..

 

[Luke M]

The COUNTIF is looking at the range given in first argument, and checking to see how many times the value input into the cell exists. So, will return an integer anywhere from 0 and up. We then compare that value to the "<2" to give us a True/False evaluation. If there are less than 2 instances, it's not a duplicate, and the formula allows the entry. Otherwise, it's false, and it throws an error.

 

[Xiq]

For this problem, you can leave the < operator from the countif function.

 

[Luke M]

For this problem, you can leave the < operator from the countif function.

Now we're just getting crazy!!! But yes, we could have...many different solutions to this problem.

 

[bobhc]

Now we're just getting crazy!!!

Now come on Luke M how can you imply and infer that people who use Excel may be slightly unstable in the mind area,.....now if you will excuse me I have to take my goldfish for a walk.....

 

[vincy]

Hi ,

Assuming your data entry starts from cell A2 , select any reasonable data entry range , say A2:A300 , and after clicking on Data Validation , Custom , enter the following formula :

=AND(COUNTIF($A$2:A2,A2)<=1,LEN(A2)=10,INT(A2)=A2)

Narayan

@ narayan sir,
With your formula i started inputing data. It was working fine. After a stage i was doing "cut,copy,paste" from a website. Excel stopped working the datavalidation from that point even though i had selected range more than that. Why did it happen? Now what is the next method to correct it.

 

[r1c1]

Hi Vincy,

When you copy paste data, data validation does not work. It can only work, when you are typing data.

 

[vincy]

Hi Vincy,

When you copy paste data, data validation does not work. It can only work, when you are typing data.

How to overcome this problem. "copy, paste" is so normal these days. How can we avoid that and do data entry. Does Excel is limited to that?

 

[Luke M]

While there are some semi-reliable ways to prevent it using VBA, the premise works by detecting a paste and then reversing the paste. Perhaps another method you could use would be to create a formula in a different cell to flag when an error has occured. Formula would be something like this:
=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))
=COUNTA(A2:A10)

First half of that formula counts # of unique values (from XL help file), and the back half just checks how many cells have a value in them. Formula will evaluate to FALSE if a duplicate exists.

 

[SirJB7]

Hi, vincy!
Just to answer your comments/question in order:
Without using VBA you cant. Yes, it's so normal. You can't. Yes, Excel is limited to that.
Excel's extremely flexible and powerful but sometimes it has many drawbacks that even they appear logic for human beings they don't seem to look like that to Redmond guys.
Regards!
PS: Same happens in a lot of daily things. Cars can reach 200Km/h, it's so normal, but you can drive at that speed in cities, avenues and streets are limited about that... so you'll have to adapt your wishes to reality and use the tool up to the available limits, despite you like them or not.