Is there a formula to let me know if a duplicate item has been entered in a column?

Thanks
Andy

Hi
check out the following site:
http://www.cpearson.com/excel/NoDupEntry.htm
and
http://www.cpearson.com/excel/duplicat.htm

--
Regards
Frank Kabel
Frankfurt, Germany
"Andy Daugherty" schrieb im Newsbeitrag
...
Is there a formula to let me know if a duplicate item has been entered
in a column?

Thanks
Andy

Check out

http://cpearson.com/excel/duplicat.htm

and

http://cpearson.com/excel/nodupentry.htm




In article ,
"Andy Daugherty" wrote:

Is there a formula to let me know if a duplicate item has been entered in a
column?

Thanks
Andy

Andy,

Try this formula

=IF(COUNTA(A1:A1000)=SUMPRODUCT((A1:A1000<"")/COUNTIF(A1:A1000,A1:A1000&"")),"No duplicates","Duplicates")

If you want to see the duplicates, add this formula to B1 and copy down
=IF(COUNTIF($A$1:$A$1000,A1)1,"Duplicate here","")
or wrap this formula;a in conditional formatting to colour the offending cell(so)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andy Daugherty" wrote in message ...
Is there a formula to let me know if a duplicate item has been entered in a column?

Thanks
Andy

Thanks for the info to all of you.

I selected column (C) which was my Length column, went to Data / Validation / and entered the formula from one of the websites you provided: =COUNTIF($C$1:$C$50,C1)=1

I got it to work for that one column but there is one more constraint that I'm dealing with for column (G) also.

Below, you can see the chart I'm using. I need to know when I have a duplicate (Length+Remarks).
For example: I have a part that is 1'-11 3/4" with a 22'-3" RAD, and I want to be warned if I accidentally enter another part 1'-11 3/4" with a 22'-3" RAD.

Thanks.
Andy


MK/SHT REQ'D LENGTH SECTION C.F. C.F. EXT. REMARKS
41 / 4.0 1 1'-11 3/4" 3/2.0 0.26 0.26 22'-3" RAD
42 / 4.0 28 1'-11 9/16" 3/2.0 0.26 7.15 22'-3" RAD
43 / 4.0 1 2'-1 1/16" 3/2.0 0.27 0.27 22'-3" RAD
44 / 4.0 2 1'-10 9/16" 3/2.0 0.24 0.49 7'-9" RAD
45 / 4.0 11 1'-10 3/8" 3/2.0 0.24 2.67 7'-9" RAD
46 / 4.0 2 1'-9 7/8" 3/2.0 0.24 0.47 8'-1" RAD
47 / 4.0 5 1'-9 11/16" 3/2.0 0.23 1.17 8'-1" RAD
48 / 4.0 2 1'-10 1/8" 3/2.0 0.24 0.48 7'-0" RAD
49 / 4.0 4 1'-9 15/16" 3/2.0 0.24 0.95 7'-0" RAD
50 / 4.0 2 1'-10 13/16" 3/2.0 0.25 0.49 6'-0" RAD
51 / 4.0 3 1'-10 5/8" 3/2.0 0.25 0.74 6'-0" RAD

52 / 4.1 1 1'-11 5/8" 3/2.0 0.55 0.55 22'-3" RAD
53 / 4.1 28 1'-11 7/16" 3/2.0 0.55 15.31 22'-3" RAD
54 / 4.1 1 2'-1 1/16" 3/2.0 0.58 0.58 22'-3" RAD
55 / 4.1 2 1'-10 5/16" 3/2.0 0.52 1.04 7'-9" RAD
56 / 4.1 11 1'-10 1/8" 3/2.0 0.52 5.68 7'-9" RAD
57 / 4.1 2 1'-9 9/16" 3/2.0 0.50 1.01 8'-1" RAD
58 / 4.1 5 1'-9 3/8" 3/2.0 0.50 2.49 8'-1" RAD
59 / 4.1 2 1'-9 13/16" 3/2.0 0.51 1.02 7'-0" RAD
60 / 4.1 4 1'-9 5/8" 3/2.0 0.50 2.02 7'-0" RAD
61 / 4.1 2 1'-10 7/16" 3/2.0 0.52 1.05 6'-0" RAD
62 / 4.1 3 1'-10 1/4" 3/2.0 0.52 1.56 6'-0" RAD

Andy,

Is this what you want

=SUMPRODUCT(--($C$1:$C$50&G$1:$G$50=C1&G1))=1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andy Daugherty" wrote in message ...
Thanks for the info to all of you.

I selected column (C) which was my Length column, went to Data / Validation / and entered the formula from one of the websites you provided: =COUNTIF($C$1:$C$50,C1)=1

I got it to work for that one column but there is one more constraint that I'm dealing with for column (G) also.

Below, you can see the chart I'm using. I need to know when I have a duplicate (Length+Remarks).
For example: I have a part that is 1'-11 3/4" with a 22'-3" RAD, and I want to be warned if I accidentally enter another part 1'-11 3/4" with a 22'-3" RAD.

Thanks.
Andy


MK/SHT REQ'D LENGTH SECTION C.F. C.F. EXT. REMARKS
41 / 4.0 1 1'-11 3/4" 3/2.0 0.26 0.26 22'-3" RAD
42 / 4.0 28 1'-11 9/16" 3/2.0 0.26 7.15 22'-3" RAD
43 / 4.0 1 2'-1 1/16" 3/2.0 0.27 0.27 22'-3" RAD
44 / 4.0 2 1'-10 9/16" 3/2.0 0.24 0.49 7'-9" RAD
45 / 4.0 11 1'-10 3/8" 3/2.0 0.24 2.67 7'-9" RAD
46 / 4.0 2 1'-9 7/8" 3/2.0 0.24 0.47 8'-1" RAD
47 / 4.0 5 1'-9 11/16" 3/2.0 0.23 1.17 8'-1" RAD
48 / 4.0 2 1'-10 1/8" 3/2.0 0.24 0.48 7'-0" RAD
49 / 4.0 4 1'-9 15/16" 3/2.0 0.24 0.95 7'-0" RAD
50 / 4.0 2 1'-10 13/16" 3/2.0 0.25 0.49 6'-0" RAD
51 / 4.0 3 1'-10 5/8" 3/2.0 0.25 0.74 6'-0" RAD

52 / 4.1 1 1'-11 5/8" 3/2.0 0.55 0.55 22'-3" RAD
53 / 4.1 28 1'-11 7/16" 3/2.0 0.55 15.31 22'-3" RAD
54 / 4.1 1 2'-1 1/16" 3/2.0 0.58 0.58 22'-3" RAD
55 / 4.1 2 1'-10 5/16" 3/2.0 0.52 1.04 7'-9" RAD
56 / 4.1 11 1'-10 1/8" 3/2.0 0.52 5.68 7'-9" RAD
57 / 4.1 2 1'-9 9/16" 3/2.0 0.50 1.01 8'-1" RAD
58 / 4.1 5 1'-9 3/8" 3/2.0 0.50 2.49 8'-1" RAD
59 / 4.1 2 1'-9 13/16" 3/2.0 0.51 1.02 7'-0" RAD
60 / 4.1 4 1'-9 5/8" 3/2.0 0.50 2.02 7'-0" RAD
61 / 4.1 2 1'-10 7/16" 3/2.0 0.52 1.05 6'-0" RAD
62 / 4.1 3 1'-10 1/4" 3/2.0 0.52 1.56 6'-0" RAD

That worked great! Man, I appreciate your help!

Andy

Ok, one last question.
Everything works great for Sheet1, but I have Sheet2 and Sheet3 also.
I'm not sure where to place the Sheet numbers.

=SUMPRODUCT(--($C$1:$C$50&G$1:$G$50=C1&G1))=1

=SUMPRODUCT(--(Sheet2!$C$1:$C$50&Sheet2!$G$1:$G$50=Sheet2!C1&She et2!G1))=1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andy Daugherty" wrote in message ...
Ok, one last question.
Everything works great for Sheet1, but I have Sheet2 and Sheet3 also.
I'm not sure where to place the Sheet numbers.

=SUMPRODUCT(--($C$1:$C$50&G$1:$G$50=C1&G1))=1

Thanks Bob. I appreciate your help.

Andy