Grouping like combinations

[leebauman]

Hello, my data contains thousands of orders with a variety of item possibilities. I want to count the like item combinations.

In column A are my order numbers and column B are the items for each order

I want an output that shows the "like" item groups and references the associated order numbers (like in columns D & E below)

Much appreciated

 

[Akuini]

Try this:

Sub a1159007a()
Dim i As Long
Dim va
Dim d As Object

va = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)

Set d = CreateObject("scripting.dictionary")
d.CompareMode = vbTextCompare
For i = 1 To UBound(va, 1)
If d.Exists(va(i, 1)) Then
    d(va(i, 1)) = d(va(i, 1)) & ", " & va(i, 2)
Else
    d(va(i, 1)) = va(i, 2)
End If

Range("D2").Resize(d.Count, 2) = Application.Transpose(Array(d.Items, d.Keys))
Next


End Sub

Book1
	A	B	C	D	E
1	ORDER	ITEM		ITEM GROUP	ORDER
2	C	Paxton		Paxton, Derek, Patrick	C
3	C	Derek		Zaiden, Zander, Paxton	N
4	C	Patrick		Derek, Patrick	U
5	N	Zaiden		Zein	Y
6	N	Zander
7	N	Paxton
8	U	Derek
9	U	Patrick
10	Y	Zein
Sheet1

 

[alansidman]

Using Power Pivot as shown in this tutorial==>Excel: Reporting Text in a Pivot Table - Strategic Finance

Results look like this:

Book7
	A	B
3	ORDER	Measure 1
4	C	Paxton, Derek, Patrick
5	N	Zaiden, Zander, Paxton
6	U	Derek, Patrick
7	Y	Zein
Sheet2