Merge Excel rows based on common column values

[OC2PS]

I have a table similar to the image below. Column A has repeated values, while column B has values corresponding to column A. e.g. a single customer can have multiple orders.

What I would like to do is combine the rows, so that there is only one row per unique column A value (customer) and in the column B for that row, all column B values (orders) are gathered together in a comma separated list.

 

[Fluff]

How about

Sub OC2PS()
   Dim Ary As Variant
   Dim r As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         If Not .Exists(Ary(r, 1)) Then
            .Add Ary(r, 1), Ary(r, 2)
         Else
            .Item(Ary(r, 1)) = .Item(Ary(r, 1)) & ", " & Ary(r, 2)
         End If
      Next r
      Sheets("Sheet2").Range("A1").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub

Change sheet names to suit.

 

[OC2PS]

Thank you! Quick question: In the code, you've noted `Range("A1")` - should I provide the full data range in this? e.g. A2:B8

 

[Fluff]

No need, as long as there aren't any completely blank rows or columns in the data.

 

[OC2PS]

You, my friend, are amazin. Thank you.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[alansidman]

Here is another way without VBA but using Power Pivot

IMA Site Maintenance