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
