VBA to summarize data

[adammon]

Hello,

Can someone help me out with creating a VBA code to summarize the following data set?

	D	E
1	SKU	Ordered
2	00001-BLACK-S	1
3	00001-DKBRN-XL	1
4	00308-BKWHT-O/S	2
5	00308-BKWHT-O/S	2
6	00308-ROYAL-O/S	1
7	00611-PINE-O/S	2
8	02427-MODEL-L	3
9	02427-MODEL-L	3
10	02427-MODEL-M	3
11	02427-MODEL-M	2
12	02427-MODEL-S	2
13	02427-MODEL-S	1
14	02427-MODEL-XL	2
15	02427-MODEL-XL	1

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

USPACK - Order summary​

Here's what I'm trying to accomplish:

	K	L
2	SKU	Ordered
3	00001-BLACK-S	1
4	00001-DKBRN-XL	1
5	00308-BKWHT-O/S	4
6	00308-ROYAL-O/S	1
7	00611-PINE-O/S	2
8	02427-MODEL-L	6
9	02427-MODEL-M	5
10	02427-MODEL-S	3

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

USPACK - Order summary​

Normally I would create a pivot table or the consolidation tool, but I need the data to be more user friendly so I can use it with other formulas.

Thanks.

 

[Fluff]

You can do that with formulae

Excel 2013/2016
	K	L
2	00001-BLACK-S	1
3	00001-DKBRN-XL	1
4	00308-BKWHT-O/S	4
5	00308-ROYAL-O/S	1
6	00611-PINE-O/S	2
7	02427-MODEL-L	6
8	02427-MODEL-M	5
9	02427-MODEL-S	3
10	02427-MODEL-XL	3
Index

Cell Formulas
Range	Formula
L2	=SUMIF(D$2:D$16,K2,E$2:E$15)
K2	{=INDEX(D$2:D$15,MATCH(0,COUNTIF(K$1:K1,D$2:D$15),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Or with VBA

Sub Summary()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("D2", Range("D" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = .Item(Cl.Value) + Cl.Offset(, 1).Value
      Next Cl
      Range("K2").Resize(.Count).Value = Application.Transpose(.Keys)
      Range("J2").Resize(.Count).Value = Application.Transpose(.Items)
   End With
End Sub

 

[adammon]

Thank you for this. The VBA code works great.

The formula would also work, but the user wouldn't have column K as a reference for the index match. I'm essentially trying to build a tool so the user wouldn't need to make any edits to the data. Rather just paste some information on the sheet and run the macro.

 

[Fluff]

Glad to help & thanks for the feedback