error checking

**ghadir14** · 10-15-2007, 10:40 AM

Hello,
If I have a sheet e.g 1000 rows which is constantly updated with codes...
eg.
col A B C D E
code title notes year year etc

How can i check in the columns if I have accidently copied the same code in(A) E.G 211 ROWS down...

I think I would need to lable another column eg g as (same)
then type =IF SOMEthing to find same....Can someone help me with this formula which would be applied right down all 1000 columns..? I know its quite straight forward.
this is in relation to my previous which I'm yet to elaborate on on '' check'' which compares 2 worksheets.

Many Thanks

**NBVC** · 10-15-2007, 11:00 AM

In the new column, starting in row 2, enter formula =IF(countif($A$1:A2,A2)>1,"Repeat","") copied down.

**ghadir14** · 10-15-2007, 11:15 AM

Thanks but what does the second A2 do in the formula?

**NBVC** · 10-15-2007, 11:23 AM

Originally Posted by ghadir14

Thanks but what does the second A2 do in the formula?

the formula checks everything from A1 to the row you're at (if in row 2, then A2) and counts how many time whatever is in A2 occurs in rows above, including the row you're on. If the count is more than 1, than you have entered a duplicate. The second A2 is simply the criteria to check... see Countif in Excel help files for details.

**ghadir14** · 10-15-2007, 12:04 PM

Hello
Sorry
i am trying to do this on this practice file, its not working.

i am typing =countif($A$2:A3,A3)>1,"Repeat",")
what am I doing wrong?? I typed it in column H2 underneath the lable, I have tried it without the lable also ...

Thanks in advance
G14