Count like items and remove duplicates from list

[artz]

Hi,

I have a large bill of materials for a printed circuit board in an Excel spreadsheet. Using the sort function, I was able to sort the columns based on part value; that got me part way of what I need.

What I still need however, is a macro or cell formula to do two things:

1) For a given value, provide count of like values and place count in column N
2) Remove the duplicate rows with the same values

The rows with duplicates should only removes rows in columns K, L, M, and N. (there are other active columns on the worksheet)

Below is a partial screenshot of the worksheet. The used range not including header rows is K6 - M6. This also doesn't include the count column range in column N.

Excel Workbook
	K	L	M	N
5	Value	Digikey PN	Description	Count
6	10	P10.0HCT-ND	RES 10.0 OHM 1/10W 1% 0603 SMD	*
7	511	P511HCT-ND	RES 511 OHM 1/10W 1% 0603 SMD	*
8	511	P511HCT-ND	RES 511 OHM 1/10W 1% 0603 SMD	*
9	0.01uF	PCC1750CT-ND	CAP 10000PF 16V CERM X7R 0603	*
10	0.01uF	PCC1750CT-ND	CAP 10000PF 16V CERM X7R 0603	*
11	0.01uF	PCC1750CT-ND	CAP 10000PF 16V CERM X7R 0603	*
12	0.01uF	PCC1750CT-ND	CAP 10000PF 16V CERM X7R 0603	*
13	0.01uF	PCC1750CT-ND	CAP 10000PF 16V CERM X7R 0603	*
14	0.01uF	PCC1750CT-ND	CAP 10000PF 16V CERM X7R 0603	*
15	0.05 1W	WSCB-50CT-ND	RES 50 OHM 1W 1% 2515 WW SMD	*
16	0.1F	445-1317-1-ND	CAP CER .10UF 16V X7R 10% 0603	*
17	0.1F	445-1317-1-ND	CAP CER .10UF 16V X7R 10% 0603	*
18	0.1F	445-1317-1-ND	CAP CER .10UF 16V X7R 10% 0603	*
19	0.1F	445-1317-1-ND	CAP CER .10UF 16V X7R 10% 0603	*
20	0.1F	445-1317-1-ND	CAP CER .10UF 16V X7R 10% 0603	*
21	0.22uF	445-1318-1-ND	CAP CER .22UF 16V X7R 10% 0603	*
22	1.5Meg	P1.50MHCT-ND	RESISTOR 1.50M OHM 1/10W 1% 0603	*
23	1.5Meg	P1.50MHCT-ND	RESISTOR 1.50M OHM 1/10W 1% 0603	*
24	1.5Meg	P1.50MHCT-ND	RESISTOR 1.50M OHM 1/10W 1% 0603	*
25	1000pF	490-1494-1-ND	CAP CER 1000PF 50V 10% X7R 0603	*
26	1000pF	490-1494-1-ND	CAP CER 1000PF 50V 10% X7R 0603	*
27	1000pF	490-1494-1-ND	CAP CER 1000PF 50V 10% X7R 0603	*
28	100K	P100KHCT-ND	RES 100K OHM 1/10W 1% 0603 SMD	*
29	100K	P100KHCT-ND	RES 100K OHM 1/10W 1% 0603 SMD	*
30	100K	P100KHCT-ND	RES 100K OHM 1/10W 1% 0603 SMD	*
31	100K	P100KHCT-ND	RES 100K OHM 1/10W 1% 0603 SMD	*
32	100K	P100KHCT-ND	RES 100K OHM 1/10W 1% 0603 SMD	*
33	100K	P100KHCT-ND	RES 100K OHM 1/10W 1% 0603 SMD	*
34	100K	P100KHCT-ND	RES 100K OHM 1/10W 1% 0603 SMD	*
35	100K	P100KHCT-ND	RES 100K OHM 1/10W 1% 0603 SMD	*
36	100K	3214W-104ECT-ND	TRIMPOT 100K OHM 4MM TOP ADJ SMD	*
37	100K	P100KHCT-ND	RES 100K OHM 1/10W 1% 0603 SMD	*
38	100K	P100KHCT-ND	RES 100K OHM 1/10W 1% 0603 SMD	*
39	100K	P100KHCT-ND	RES 100K OHM 1/10W 1% 0603 SMD	*
40	100K	P100KHCT-ND	RES 100K OHM 1/10W 1% 0603 SMD	*
41	100K	P100KHCT-ND	RES 100K OHM 1/10W 1% 0603 SMD	*
PCB_BOM

Is anyone in the Forum able to offer some code to do this? Any help is greatly appreciated.

Thanks,

Art

 

[BobUmlas]

If you have Excel 2007 or greater, then this is a builtin feature -- Data tab, Remove duplicates.

 

[ColinKJ]

Hi artz,

You say to count based on value and remove duplicates.

In your example, rows 28 to 41 have the same values, but there are 2 different descriptions in this group:

P100KHCT-ND
3214W-104ECT-ND

In this example, the count based on value would be 14, but is row 36 a duplicate ?

ColinKJ

 

[ColinKJ]

Hi artz,

Here's a macro that counts the values column, and deletes duplicates based on the value row:

Sub CountRemove()
R = Range("K" & Rows.Count).End(xlUp).Row
For a = R To 6 Step -1
ct = 1
x = Cells(a, 11)
y:
If Cells(a - 1, 11) = x Then
ct = ct + 1
Range("K" & a & ":N" & a).ClearContents
a = a - 1
GoTo y
Else
Cells(a, 14) = ct
End If
Next a
Range("K6:N" & R).Select
    Selection.Sort Key1:=Range("K6"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Cells(6, 11).Select
End Sub

I've put together an example WB called CountDelete, which you can download from :

http://www.box.net/shared/jqd3vd4cd0

Ctrl+q to activate

ColinKJ

 

[artz]

Hi ColinKJ,

Thanks for your response. You are right, I think that we need to count by Digikey PN. That should provide unambiguous identifiers. Do you have a suggestion on how to do what I need using column L?

Thanks,

Art

 

[artz]

BobUmlas,

Thanks for the tip. I didn't know feature was available. Unfortunately, unless it also counts the number of dupes it deletes, it does help in this case.

Regards,

Art

 

[ColinKJ]

Hi artz,

I've updated it based on column L:

Sub CountRemove()
R = Range("K" & Rows.Count).End(xlUp).Row
Range("K6:N" & R).Select
    Selection.Sort Key1:=Range("L6"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
For a = R To 6 Step -1
ct = 1
x = Cells(a, 12)
y:
If Cells(a - 1, 12) = x Then
ct = ct + 1
Range("K" & a & ":N" & a).ClearContents
a = a - 1
GoTo y
Else
Cells(a, 14) = ct
End If
Next a
Range("K6:N" & R).Select
    Selection.Sort Key1:=Range("L6"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Cells(6, 11).Select
End Sub

I've also updated the example WB CountDelete:

http://www.box.net/shared/jqd3vd4cd0

Ctrl+q to activate

Regards

Colin

 

[artz]

Colin,

You are awesome! Thanks, it worked great. The only thing though that I should have thought of was that by overwriting the range, I lost my original list. And unfortunately, I didn't save a worksheet with the original table in columns K,L, and M. I can create another one though.

Is there a way to modify what you wrote so that columns K,L, and M stay intact, and the counted and deleted dupes data, i.e., what is written to columns K, L, M and N with your sub is shifted over to say start at column P and would include columns P, Q, R, and S? Make sense?

This is a tool that will use often so it would be really great if you could make this mod if this isn't too complicated.

Thanks,

Art

 

[ColinKJ]

Hi artz,

Try this one:

Sub CountMove()
R = Range("K" & Rows.Count).End(xlUp).Row
Range("K6:N" & R).Select
    Selection.Sort Key1:=Range("L6"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
For a = R To 6 Step -1
ct = 1
x = Cells(a, 12)
y:
If Cells(a - 1, 12) = x Then
ct = ct + 1
Range("K" & a & ":N" & a).Cut
Cells(a, 16).Select
ActiveSheet.Paste
a = a - 1
GoTo y
Else
Cells(a, 14) = ct
End If
Next a
Cells(6, 11).Select
End Sub

You can download an exmple WB named CountMove from:

http://www.box.net/shared/yvllbpimli

Again, Ctrl+q to activate.

Regards

Colin

 

[artz]

Hi Colin,

Thanks for taking a stab at a reformulation. When I run the sub, parts with a quantity of 1 were dropped from the reconstituted (original) list and quantities greater than 1 show a quantity 1 less than the number in column the reconstituted (original) list.

Also, there are a lot of empty rows in both tables. Any chance that you can take a look. Below is an excerpt from the output:

Excel Workbook
	K	L	M	N	O	P	Q	R
5	Value	Digikey PN	Description	*	*	*	*	*
6	XC2C32A-6VQG44C	122-1410-ND	IC CR-II CPLD 64MCELL 44-VQFP	1	*	*	*	*
7	1N4148	1N4148W-FDICT-ND	DIODE SWITCH 100V 400MW SOD123	6	*	*	*	*
8	*	*	*	*	*	1N4148	1N4148W-FDICT-ND	DIODE SWITCH 100V 400MW SOD123
9	*	*	*	*	*	1N4148	1N4148W-FDICT-ND	DIODE SWITCH 100V 400MW SOD123
10	*	*	*	*	*	1N4148	1N4148W-FDICT-ND	DIODE SWITCH 100V 400MW SOD123
11	*	*	*	*	*	1N4148	1N4148W-FDICT-ND	DIODE SWITCH 100V 400MW SOD123
12	*	*	*	*	*	1N4148	1N4148W-FDICT-ND	DIODE SWITCH 100V 400MW SOD123
13	CD4013	296-14089-1-ND	IC DUAL D-TYPE FLIP-FLOP 14TSSOP	1	*	*	*	*
14	CD4060	296-14119-1-ND	IC BNRY COUNTR/DIV 14STG 16TSSOP	1	*	*	*	*
15	LM393 (half)	296-14607-1-ND	IC DUAL DIFF COMPARATOR 8-TSSOP	1	*	*	*	*
16	TPS71518	296-18731-1-ND	IC LDO REG 50MA 1.8V SC70-5	1	*	*	*	*
17	TLV2252 (half)	296-7432-5-ND	IC OPAMP GP R-R 200KHZ 8TSSOP	3	*	*	*	*
18	*	*	*	*	*	TLV2252 (half)	296-7432-5-ND	IC OPAMP GP R-R 200KHZ 8TSSOP
19	*	*	*	*	*	TLV2252 (half)	296-7432-5-ND	IC OPAMP GP R-R 200KHZ 8TSSOP
20	10K	3214W-103ECT-ND	TRIMPOT 10K OHM 4MM TOP ADJ SMD	2	*	*	*	*
21	*	*	*	*	*	10K	3214W-103ECT-ND	TRIMPOT 10K OHM 4MM TOP ADJ SMD
22	100K	3214W-104ECT-ND	TRIMPOT 100K OHM 4MM TOP ADJ SMD	1	*	*	*	*
23	500K	3214W-1-504ECT-ND	TRIMPOT 500K OHM 4MM CERMET SMD	1	*	*	*	*
24	20K	3214W-203ECT-ND	TRIMPOT 20K OHM 4MM TOP ADJ SMD	2	*	*	*	*
25	*	*	*	*	*	20K	3214W-203ECT-ND	TRIMPOT 20K OHM 4MM TOP ADJ SMD
26	5K	3214W-502ECT-ND	TRIMPOT 5K OHM 4MM TOP ADJ SMD	1	*	*	*	*
27	KSC241JLFS	401-1762-1-ND	SWITCH TACT SILVER 300GF J-LEAD	2	*	*	*	*
28	*	*	*	*	*	KSC241JLFS	401-1762-1-ND	SWITCH TACT SILVER 300GF J-LEAD
29	4.8KHz	433-1066-ND	BUZZER 4.80KHZ 3-16BDC 85DB PCB	1	*	*	*	*
30	0.1F	445-1317-1-ND	CAP CER .10UF 16V X7R 10% 0603	5	*	*	*	*
31	*	*	*	*	*	0.1F	445-1317-1-ND	CAP CER .10UF 16V X7R 10% 0603
32	*	*	*	*	*	0.1F	445-1317-1-ND	CAP CER .10UF 16V X7R 10% 0603
33	*	*	*	*	*	0.1F	445-1317-1-ND	CAP CER .10UF 16V X7R 10% 0603
34	*	*	*	*	*	0.1F	445-1317-1-ND	CAP CER .10UF 16V X7R 10% 0603
35	0.22uF	445-1318-1-ND	CAP CER .22UF 16V X7R 10% 0603	1	*	*	*	*
36	1uF	445-5138-1-ND	CAP CER 1.0UF 10V X7R 0603	5	*	*	*	*
37	*	*	*	*	*	1uF	445-5138-1-ND	CAP CER 1.0UF 10V X7R 0603
38	*	*	*	*	*	1uF	445-5138-1-ND	CAP CER 1.0UF 10V X7R 0603
39	*	*	*	*	*	1F	445-5138-1-ND	CAP CER 1.0UF 10V X7R 0603
40	*	*	*	*	*	1F	445-5138-1-ND	CAP CER 1.0UF 10V X7R 0603
PCB_BOM

Do you think that this can be fixed? Also, I wonder if it's easier to leave the original table where it was in columns K, L, and M and to output the counted list in columns P, Q, R, and S. Just a thought.

Thanks,

Art