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

 

[ColinKJ]

Hi artz,

Try this version of CountMove:

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

Again, Ctrl+q to activate

Regards

Colin

 

[artz]

Hi Colin,

Except for having an extra column of data in column S, your worksheet does exactly what I need. Thanks so much. I can edit your macro to remove the extra data in column S.

As I mentioned this little routine will be very helpful going forward.

Thanks Again,

Art