VBA - Search duplicate values, sum quantities, and concatenate a string

[LearnNewThings]

I have a data set that is sent to me from one of my suppliers, I have attached a sample of the data here. In this data set, there is one column with customer PN (Column B), 3 columns with quantities (Columns C, D, & E), and a column with purchase order information (Column F). Throughout the data, there are many instances where there are duplicate rows of the customer PN (Column B). I would like to sum up each QTY column for all given duplicated Customer PNs, and concatenate the PO information with a carriage return. I have manually provided an example of what I am trying to achieve below the yellow line. The data set is ~1000 lines long, so a manual approach is not ideal.



A couple of notes regarding the data:

• Not all Customer PN have duplicate values
• There could be more than 2 rows with the same Customer PN
• Not all values in either of the three QTY values are greater than 0
• Not all duplicate Customer PN will have anything in the PO Info column
• I already have VBA scripts to manipulate some of the data, so adding more is not a big deal

Test Data.xlsx
	A	B	C	D	E	F
1	PART NUMBER	CUSTOMER P/N	ON HAND	QTY REQ	ON ORDER	PO INFO
2	51-5737	160-00047	20.00	130.00	105.00	702840530201 105 2/10/2022 TECH ETCH, INC. CONFIRMED
3	51-6213	160-00047	10.00	0.00	10.00	702840531231 10 2/10/2022 TECH ETCH, INC. CONFIRMED
4	51-5735	160-00046	27.00	130.00	105.00	702840530101 105 2/10/2022 TECH ETCH, INC. CONFIRMED
5	51-6132	160-00046	6.00	0.00	0.00
6	54-4375	430-00084	0.00	0.00	0.00
7	54-4499	430-00084	90.00	168.00	80.00	702841990601 80 2/16/2022 M&K ENGINEERING INC CONFIRMED
8	54-4416	430-00087	0.00	0.00	0.00
9	54-4491	430-00087	0.00	0.00	0.00
10	54-4372	430-00108	2.00	0.00	0.00
11	54-4496	430-00108	94.00	168.00	75.00	702841990301 75 2/16/2022 M&K ENGINEERING INC CONFIRMED
12	54-4370	431-00010	2.00	0.00	0.00
13	54-4495	431-00010	22.00	168.00	150.00	702841820101 150 2/14/2022 PDF CO CONFIRMED
14	51-5729	437-00004	25.68	260.00	240.00	702840550701 240 2/14/2022 MCMASTER-CARR SUPPLY CO. CONFIRMED
15	51-6110	437-00004	0.00	0.00	0.00
16	51-5716	437-00005	34.30	8.50	0.00
17	54-4451	437-00005	0.00	0.00	0.00
18	82-849	440-00014	0.00	0.00	0.00
19	93-3735	440-00014	0.00	0.00	0.00
20	82-848	440-00015	0.00	0.00	0.00
21	93-3734	440-00015	0.00	0.00	0.00
22	82-857	440-00035	0.00	0.00	0.00
23	93-3815	440-00035	0.00	0.00	0.00
24
25
26
27		CUSTOMER P/N	ON HAND	QTY REQ	ON ORDER	PO INFO
28		160-00047	30.00	130.00	115.00	702840530201 105 2/10/2022 TECH ETCH, INC. CONFIRMED 702840531231 10 2/10/2022 TECH ETCH, INC. CONFIRMED
Sheet2

 

[DanteAmor]

Try this, data on sheet2, results on sheet3.

Sub sum_quantities()
  Dim a As Variant
  Dim dic As Object
  Dim i As Long, j As Long
  
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("Sheet2").Range("A2:F" & Sheets("Sheet2").Range("B" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  
  For i = 1 To UBound(a, 1)
    If Not dic.exists(a(i, 2)) Then
      j = j + 1
      dic(a(i, 2)) = j
    End If
    j = dic(a(i, 2))
    b(j, 1) = a(i, 2)
    b(j, 2) = b(j, 2) + a(i, 3)
    b(j, 3) = b(j, 3) + a(i, 4)
    b(j, 4) = b(j, 4) + a(i, 5)
    b(j, 5) = IIf(b(j, 5) = "", a(i, 6), b(j, 5) & vbCr & a(i, 6))
  Next
  Sheets("Sheet3").Range("A2").Resize(UBound(b, 2), 5).Value = b
End Sub

 

[LearnNewThings]

DanteAmor,

This worked well for some of the data, but it did not perform this on all of the data. It ended at Customer PN 431-00010. When I applied it to my larger data set, it only performed it on a limited set of data instead of the whole set. How do I expand this to a data set that is dynamic in size (row count)? Additionally, the PO text did not have a carriage return until I double-clicked in the cell itself (then it split where it should). Is there a way to have the carriage return be visible without needing to double-click in the respective cell?

Finally, would it be too much to ask if you could add some comments explaining the logic you have proposed? This is a completely new approach that I am unfamiliar with and would LOVE to learn what is driving this.

Thanks so much!!

- Rich

 

[DanteAmor]

This worked well for some of the data, but it did not perform this on all of the data. It ended at Customer PN 431-00010.

Check that the data does not have spaces before or after inside the cell.

When I applied it to my larger data set

How many records do you have?

How do I expand this to a data set that is dynamic in size (row count)?

The code works down to the last cell with data from column B.

 

[LearnNewThings]

Check that the data does not have spaces before or after inside the cell.

There are no spaces anywhere in the cells of column B

How many records do you have?

For the last export, there were 764 records. However, this size can change.

The code works down to the last cell with data from column B.

It didn't work past row 13 in my sample data (which resulted in 6 merged rows. When I applied it to my larger data set, it ended at row 7 (which resulted in 6 rows). I have included screenshots of example data and the output that resulted from running the code. I have also included an expanded data set, though it is not the complete set.

12202021.xlsm
	A	B	C	D	E	F
1	PART NUMBER	CUSTOMER P/N	ON HAND	QTY REQ	ON ORDER	PO INFO
2	51-5735	160-00046	27	130	105	702840530101 105 2/10/2022 TECH ETCH, INC. CONFIRMED
3	51-6132	160-00046	6	0	0
4	54-4375	430-00084	0	0	0
5	54-4499	430-00084	90	168	80	702841990601 80 2/16/2022 M&K ENGINEERING INC
6	54-4374	430-00085	0	0	0
7	54-4498	430-00085	95	168	75	702841990501 75 2/16/2022 M&K ENGINEERING INC
8	54-4373	430-00086	0	0	0
9	54-4497	430-00086	95	168	75	702841990401 75 2/16/2022 M&K ENGINEERING INC
10	54-4416	430-00087	0	0	0
11	54-4491	430-00087	0	0	0
12	54-4415	430-00088	0	0	0
13	54-4490	430-00088	4	260	256	702813010501 8 12/22/2021 WROBEL ENGINEERING CO INC CONFIRMED 702813010502 13 12/22/2021 WROBEL ENGINEERING CO INC CONFIRMED 702841861801 125 3/21/2022 WROBEL ENGINEERING CO INC CONFIRMED 702841861802 110 3/28/2022 WROBEL ENGINEERING CO INC CONFIRMED
14	54-4414	430-00089	0	0	0
15	54-4489	430-00089	0	5	5	702730520901 5 1/14/2022 WROBEL ENGINEERING CO INC CONFIRMED
16	54-4398	430-00090	0	0	0
17	54-4485	430-00090	0	130	130	702813471001 5 1/19/2022 WROBEL ENGINEERING CO INC CONFIRMED 702813471002 5 1/19/2022 WROBEL ENGINEERING CO INC CONFIRMED 702841861301 120 2/28/2022 WROBEL ENGINEERING CO INC CONFIRMED
18	54-4396	430-00091	0	0	0
19	54-4483	430-00091	0	130	130	702813470901 5 1/19/2022 WROBEL ENGINEERING CO INC CONFIRMED 702813470902 5 1/19/2022 WROBEL ENGINEERING CO INC CONFIRMED 702841861101 120 2/28/2022 WROBEL ENGINEERING CO INC CONFIRMED
20	54-4394	430-00094	0	0	0
21	54-4481	430-00094	25	260	235	702841860901 235 2/28/2022 WROBEL ENGINEERING CO INC CONFIRMED
22	54-4402	430-00097	0	0	0
23	54-4877	430-00097	0	130	130	702845060101 25 12/22/2021 WROBEL ENGINEERING CO INC CONFIRMED 702845060201 105 2/11/2022 WROBEL ENGINEERING CO INC CONFIRMED
24	54-4413	430-00099	0	0	0
25	54-4488	430-00099	5	130	125	702813471302 5 12/29/2021 WROBEL ENGINEERING CO INC CONFIRMED 702841861601 120 2/28/2022 WROBEL ENGINEERING CO INC CONFIRMED
26	54-4393	430-00100	0	0	0
27	54-4440	430-00100	15	130	115	702841860501 115 2/28/2022 WROBEL ENGINEERING CO INC CONFIRMED
28	54-4391	430-00102	0	0	0
29	54-4480	430-00102	24	130	106	702841860801 106 2/28/2022 WROBEL ENGINEERING CO INC CONFIRMED
30	54-4404	430-00103	0	0	0
31	54-4486	430-00103	10	130	120	702841861401 120 2/28/2022 WROBEL ENGINEERING CO INC CONFIRMED
32	54-4411	430-00104	0	0	0
33	54-4487	430-00104	0	130	130	702813471201 25 1/19/2022 WROBEL ENGINEERING CO INC CONFIRMED 702841861501 105 2/28/2022 WROBEL ENGINEERING CO INC CONFIRMED
34	54-4366	430-00105	0	0	0
35	54-4479	430-00105	1	130	129	702813470603 9 12/30/2021 WROBEL ENGINEERING CO INC CONFIRMED 702841860701 120 2/28/2022 WROBEL ENGINEERING CO INC CONFIRMED
36	54-4372	430-00108	2	0	0
37	54-4496	430-00108	94	168	75	702841990301 75 2/16/2022 M&K ENGINEERING INC
38	51-5753	431-00005	41	168	130	702840600101 130 2/14/2022 RENISHAW INC. CONFIRMED
39	54-4371	431-00005	0	0	0
40	54-4370	431-00010	2	0	0
41	54-4495	431-00010	22	168	150	702841820101 150 2/14/2022 PDF CO CONFIRMED
42	54-4389	431-00013	0	0	0
43	54-4511	431-00013	33	170	150	702841840301 150 3/2/2022 ORION INDUSTRIES, INC. CONFIRMED
44	54-4385	432-00052	0	0	0
45	54-4506	432-00052	35	170	135	702841920301 135 2/2/2022 STRATASYS DIRECT, INC. CONFIRMED
46	54-4369	432-00057	0	0	0
47	54-4494	432-00057	95	168	75	702841990201 75 2/16/2022 M&K ENGINEERING INC
48	54-4368	432-00058	0	0	0
49	54-4493	432-00058	45	168	125	702841920201 125 1/14/2022 STRATASYS DIRECT, INC. CONFIRMED
50	54-4367	432-00077	0	0	0
51	54-4492	432-00077	94	168	100	702841990101 100 2/16/2022 M&K ENGINEERING INC
52	51-5729	437-00004	25.68	260	240	702840550701 240 2/14/2022 MCMASTER-CARR SUPPLY CO. CONFIRMED
53	51-6110	437-00004	0	0	0
54	51-5716	437-00005	34.3	8.5	0
55	54-4451	437-00005	0	0	0
56	82-849	440-00014	0	0	0
57	93-3735	440-00014	0	0	0
58	82-848	440-00015	0	0	0
59	93-3734	440-00015	0	0	0
60	82-857	440-00035	0	0	0
61	93-3815	440-00035	0	0	0
62	51-5711	445-00044	10	0	0
63	51-5824	445-00044	55	365.5914	325	702840560101 325 2/14/2022 HARDWARE SPECIALTY INC CONFIRMED
64	801-1087	810-00008	0	0	0
65	801-1121	810-00008	0	0	0
66	801-1096	810-00011	0	0	0
67	801-1120	810-00011	0	0	0
68	801-1097	810-00013	0	0	0
69	801-1122	810-00013	0	0	0
70	800-1824	820-00027	0	176	6	501667570000 6 6/30/2021
71	801-1086	820-00027	0	0	0
72	93-3784	820-00027	0	0	0
73	93-3812	820-00027	0	0	0
74	800-1825	820-00028	0	175	7	501661990000 1 6/30/2021 501670880000 6 6/30/2021
75	801-1085	820-00028	0	0	0
76	93-3782	820-00028	0	0	0
77	93-3813	820-00028	0	0	0
78	801-1095	820-00032	0	0	0
79	801-1119	820-00032	0	0	0
80	800-1823	820-00033	0	136	6	501671040000 6 6/28/2021
81	801-1094	820-00033	1	0	0
82	93-3811	820-00033	0	0	0
83	801-1093	820-00040	0	0	0
84	801-1118	820-00040	0	0	0
85	801-1092	820-00041	0	0	0
86	801-1117	820-00041	0	0	0
87	800-1822	820-00043	0	130	0
88	801-1091	820-00043	0	0	0
89	93-3810	820-00043	0	0	0
90	801-1084	820-00044	0	0	0
91	801-1115	820-00044	0	0	0
92	93-3814	820-00044	0	0	0
93	800-1806	820-00045	0	0	0
94	800-1821	820-00045	0	130	0
95	801-1090	820-00045	0	0	0
96	93-3809	820-00045	0	0	0
97	801-1089	820-00047	0	0	0
98	801-1116	820-00047	0	0	0
99	600-3098	830-00010	19	170	10	501675980000 10 12/30/2021
100	92-416	830-00010	0	0	0
101	600-3103	830-00011	20	168	5	501674830000 5 12/10/2021
102	92-417	830-00011	0	0	0
103	90-3753	830-00011-FAB	70	65	0
104	90-3834	830-00011-FAB	0	80	145	702841070101 145 12/27/2021 NCAB GROUP USA CONFIRMED
105	600-3104	830-00012	0	130	0
106	92-422	830-00012	0	0	0
107	600-3099	830-00013	18	170	10	501675990000 10 12/30/2021
108	92-415	830-00013	0	0	0
109	600-3105	830-00014	10	130	0
110	92-421	830-00014	0	0	0
111	600-3100	830-00015	25	170	0
112	92-419	830-00015	0	0	0
113	600-3101	830-00016	25	170	0
114	92-418	830-00016	0	0	0
115	600-3102	830-00017	10	130	0
116	92-420	830-00017	0	0	0
117	94-308	840-00010	0	0	0
118	94-320	840-00010	10	130	0
119	52-1714	850-00041	0	0	0
120	52-1761	850-00041	10	130	120	702844811001 65 1/28/2022 SCOTT ELECTRONICS INC CONFIRMED 702844811002 55 2/28/2022 SCOTT ELECTRONICS INC CONFIRMED
121	52-1713	850-00042	0	0	0
122	52-1760	850-00042	15	130	115	702844810901 60 1/28/2022 SCOTT ELECTRONICS INC CONFIRMED 702844810902 55 2/28/2022 SCOTT ELECTRONICS INC CONFIRMED
123	52-1712	850-00044	0	0	0
124	52-1759	850-00044	10	130	120	702844810801 65 1/28/2022 SCOTT ELECTRONICS INC CONFIRMED 702844810802 55 2/28/2022 SCOTT ELECTRONICS INC CONFIRMED
125	52-1711	850-00045	0	0	0
126	52-1758	850-00045	10	130	120	702844810701 65 1/28/2022 SCOTT ELECTRONICS INC CONFIRMED 702844810702 55 2/28/2022 SCOTT ELECTRONICS INC CONFIRMED
127	52-1710	850-00046	0	0	0
128	52-1757	850-00046	10	130	120	702844810601 65 1/28/2022 SCOTT ELECTRONICS INC CONFIRMED 702844810602 55 2/28/2022 SCOTT ELECTRONICS INC CONFIRMED
129	52-1709	850-00047	0	0	0
130	52-1756	850-00047	10	130	120	702844810501 65 1/28/2022 SCOTT ELECTRONICS INC CONFIRMED 702844810502 55 2/28/2022 SCOTT ELECTRONICS INC CONFIRMED
131	52-1719	850-00049	0	0	0
132	52-1740	850-00049	10	130	120	702844860101 65 1/14/2022 FOURSTAR CONNECTIONS INC 702844860102 55 2/16/2022 FOURSTAR CONNECTIONS INC
133	52-1708	850-00050	0	0	0
134	52-1755	850-00050	10	130	120	702844810401 65 1/28/2022 SCOTT ELECTRONICS INC CONFIRMED 702844810402 55 2/28/2022 SCOTT ELECTRONICS INC CONFIRMED
135	52-1707	850-00051	0	0	0
136	52-1754	850-00051	10	130	120	702844810301 65 1/28/2022 SCOTT ELECTRONICS INC CONFIRMED 702844810302 55 2/28/2022 SCOTT ELECTRONICS INC CONFIRMED
137	52-1706	850-00052	0	0	0
138	52-1753	850-00052	0	130	130	702844811701 65 1/28/2022 SCOTT ELECTRONICS INC CONFIRMED 702844811702 55 2/28/2022 SCOTT ELECTRONICS INC CONFIRMED 702812240201 10 3/21/2022 SCOTT ELECTRONICS INC CONFIRMED
139	52-1705	850-00054	0	0	0
140	52-1752	850-00054	75	130	55	702844810201 55 2/28/2022 SCOTT ELECTRONICS INC CONFIRMED
141	52-1704	850-00058	0	0	0
142	52-1751	850-00058	10	130	120	702844861601 65 1/28/2022 FOURSTAR CONNECTIONS INC 702844861602 55 2/28/2022 FOURSTAR CONNECTIONS INC
143	52-1702	850-00060	0	0	0
144	52-1750	850-00060	10	130	120	702844860701 65 1/28/2022 FOURSTAR CONNECTIONS INC 702844860702 55 2/28/2022 FOURSTAR CONNECTIONS INC
145	52-1717	850-00061	0	0	0
146	52-1763	850-00061	10	130	120	702844860901 65 1/14/2022 FOURSTAR CONNECTIONS INC 702844860902 55 2/16/2022 FOURSTAR CONNECTIONS INC
147	52-1700	850-00063	0	0	0
148	52-1748	850-00063	10	130	120	702844861401 65 1/28/2022 FOURSTAR CONNECTIONS INC 702844861402 55 2/28/2022 FOURSTAR CONNECTIONS INC
149	52-1699	850-00064	0	0	0
150	52-1747	850-00064	10	130	120	702844861101 65 1/28/2022 FOURSTAR CONNECTIONS INC 702844861102 55 2/28/2022 FOURSTAR CONNECTIONS INC
151	52-1715	850-00065	0	0	0
152	52-1762	850-00065	10	130	120	702844811801 65 1/28/2022 SCOTT ELECTRONICS INC CONFIRMED 702844811802 55 2/28/2022 SCOTT ELECTRONICS INC CONFIRMED
153	52-1698	850-00070	0	0	0
154	52-1746	850-00070	75	130	55	702844860501 55 2/28/2022 FOURSTAR CONNECTIONS INC
155	52-1695	850-00073	0	0	0
156	52-1742	850-00073	10	130	120	702844861501 65 1/28/2022 FOURSTAR CONNECTIONS INC 702844861502 55 2/28/2022 FOURSTAR CONNECTIONS INC
157	52-1694	850-00074	0	0	0
158	52-1743	850-00074	8	130	125	702844810101 25 1/28/2022 SCOTT ELECTRONICS INC CONFIRMED 702844810102 100 1/28/2022 SCOTT ELECTRONICS INC CONFIRMED
159	800-1799	900-00024	0	0	0
160	800-1828	900-00024	0	178	8	501670490000 8 7/16/2021
161	93-3786	900-00024	0	0	0
162	93-3817	900-00024	0	0	0
163	800-1798	900-00025	0	0	0
164	800-1827	900-00025	0	138	8	501670960000 8 7/16/2021
165	93-3816	900-00025	0	0	0
166	800-1797	900-00034	0	0	0
167	800-1826	900-00034	0	132	2	501667580000 2 7/16/2021
168	54-4782	FAB-20-00094	5	0	0
169	54-4845	FAB-20-00094	0	170	170	702832300101 25 12/13/2021 Cytrellis Biosystems, Inc 702840900201 145 3/1/2022 Cytrellis Biosystems, Inc
170	54-4769	FAB-20-00097	0	0	0
171	54-4844	FAB-20-00097	25	170	145	702840900101 145 3/1/2022 Cytrellis Biosystems, Inc
172	93-4081	LBL-00029	0	0	0
173	93-4119	LBL-00029	10	130	0
174	93-3670	WI-00103	0	0	0
175	93-3777	WI-00103	0	0	0
176	93-3671	WI-00117	0	0	0
177	93-3776	WI-00117	0	0	0
178	93-3674	WI-00120	0	0	0
179	93-4052	WI-00120	0	0	0
Sheet1

 

[DanteAmor]

My fault.

Change this:
Sheets("Sheet3").Range("A2").Resize(UBound(b, 2), 5).Value = b

For this:
Sheets("Sheet3").Range("A2").Resize(UBound(b, 1), 5).Value = b

 

[LearnNewThings]

Fantastic! That worked out perfectly, thank you so much for your assistance!!

 

[DanteAmor]

Im glad to help you, thanks for the feedback.

 

[mohadin]

Same Idea but...

Sub sum_quantities()
    Dim a As Variant
    Dim dic As Object
    Dim i As Long, j As Long
    a = Sheets("Sheet2").Range("A2:F" & Sheets("Sheet2").Range("B" & Rows.Count).End(3).Row).Value
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(a)
            If Not .exists(a(i, 2)) Then
                .Add a(i, 2), Array(a(i, 2), a(i, 3), a(i, 4), a(i, 5), a(i, 6))
            Else
                w = .Item(a(i, 2))
                w(1) = w(1) + a(i, 3):   w(2) = w(2) + a(i, 4)
                w(3) = w(3) + a(i, 5):   w(4) = w(4) & vbLf & a(i, 6)
                .Item(a(i, 2)) = w
            End If
            Set w = Nothing
        Next
        Sheets("Sheet3").Range("A2").Resize(.Count, 5).Value = Application.Index(.items, 0, 0)
    End With
End Sub