MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to Avoid Entering Duplicating Rows in Excell


Posted by A.Hayi Mansoor on January 19, 2002 1:34 PM

I am facing the problem of duplication while entering data in Excell-2000. Is there any way to put check to avoid duplicate entery in a feiled e.g ID.
Regards
Hayi


Posted by Aladin Akyurek on January 19, 2002 1:45 PM

Hayi --

One way:

Lets say that you enter the IDs in column A and you have a label in A1.
A2 will be the next empty cell.

Activate A2, the empty cell.
Activate Data|Validation.
Choose Custom for Allow.
Enter as Formula:

=COUNTIF($A$1:A1,A2)

If so desired, you might want to add a message such as "ID exists -- Enter another." on the Error Alert tab.

Click OK.

Copy down A2 as far as you need.

Caveat. This method offers no protection against Copy and Paste.

Aladin

=======


Posted by Jacob on January 19, 2002 2:13 PM

Hi
You will also need to add =1 to the end of the countif

=COUNTIF($A$1:A2,A2)=1

HTH

Jacob

--


Posted by Aladin Akyurek on January 19, 2002 2:31 PM

Jacob: You're right, the formula is incomplete. Thanks pointing out that. However, I had a slightly different formula in mind:

=COUNTIF($A$1:A1,A2)=0

Aladin

=========


Posted by Jacob on January 19, 2002 7:33 PM

That right I didnt test mine it the countif = 1 then the first value will fail, duh.

Jacob : You're right, the formula is incomplete. Thanks pointing out that. However, I had a slightly different formula in mind: =0 ==


Posted by A.Hayi Mansoor on January 19, 2002 10:16 PM

Hi,
Thanks dears for ur comments.
but i m still having prob.
Can u tell me what shoul i write in A2 (suppose it is the desired column where i d't want duplication). What should i write in the formula text box of formula (Data---Validation---Customise----Formula)
Just tell write what should i write there.
Thanks
bye

Posted by Aladin Akyurek on January 19, 2002 11:48 PM


=COUNTIF($A$1:A1,A2)=0

===================== ,

Posted by A.Hayi Mansoor on January 20, 2002 10:49 PM


Thanks Dear
You have solved my problem.
Thanks again
Hayi