A
Andy Daugherty
Is there a formula to let me know if a duplicate item has been entered in a column?
Thanks
Andy
Thanks
Andy
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
F
Frank Kabel
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
Is there a formula to let me know if a duplicate item has been entered
in a column?
Thanks
Andy
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
Is there a formula to let me know if a duplicate item has been entered
in a column?
Thanks
Andy
J
JE McGimpsey
B
Bob Phillips
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)
Is there a formula to let me know if a duplicate item has been entered in a column?
Thanks
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)
Is there a formula to let me know if a duplicate item has been entered in a column?
Thanks
Andy
A
Andy Daugherty
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
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
B
Bob Phillips
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)
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
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)
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
A
Andy Daugherty
That worked great! Man, I appreciate your help!
Andy
Andy
A
Andy Daugherty
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
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
B
Bob Phillips
=SUMPRODUCT(--(Sheet2!$C$1:$C$50&Sheet2!$G$1:$G$50=Sheet2!C1&Sheet2!G1))=1
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
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
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
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
A
Andy Daugherty
Thanks Bob. I appreciate your help.
Andy
Andy