Convert 1 label, 1 entry per row table to 1 label, multiple entries per row table

[Hawkser]

I have a very basic table (two columns) with thousands of rows. Column A has a label, and column B has an entry. There can be multiple entries (rows) per label, but the number of entries per label will vary.

(original table)
Label A, Entry 1
Label A, Entry 2
Label B, Entry 1
Label C, Entry 1
Label C, Entry 2
Label C, Entry 3
...

If I convert that to a Pivot Table, it is very easy to produce ...

Label A
Entry 1
Entry 2
Label B
Entry 1
Label C
Entry 1
Entry 2
Entry 3

What I need, is the following output ...

Label A, Entry 1, Entry 2
Label B, Entry 1
Label C, Entry 1, Entry 2, Entry 3


Basically, I need a table that is one label per row, followed by however many entries there are for that label.

I am struggling to understand how to get from the original table to the desired output. The pivot table is close to what I need, I just need the entries to be listed horizontally across multiple columns, instead of listed down multiple rows. But those groupings of entries per label is exactly what I need, just a different format. Maybe I need to use VBA and some scripting? Or maybe there is a function built into Excel already to simply produce the desired output? Any ideas or guidance would be appreciated.

TIA.

 

[Michael M]

Maybe this

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
    If Range("A" & r).Value = Range("A" & r - 1).Value Then
        Range("B" & r - 1).Value = Range("B" & r - 1).Value & ", " & Range("B" & r).Value
        Rows(r).Delete
    End If
Next r
End Sub

 

[sandy666]

or with PowerQuery (Get&Transform)

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Label"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Entry", each Table.Column([Count],"Entry")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Entry", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    #"Extracted Values"[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Label[/td][td=bgcolor:#5B9BD5]Entry[/td][td][/td][td=bgcolor:#70AD47]Label[/td][td=bgcolor:#70AD47]Entry[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Label A[/td][td=bgcolor:#DDEBF7] Entry 1[/td][td][/td][td=bgcolor:#E2EFDA]Label A[/td][td=bgcolor:#E2EFDA] Entry 1, Entry 2[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Label A[/td][td] Entry 2[/td][td][/td][td]Label B[/td][td] Entry 1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Label B[/td][td=bgcolor:#DDEBF7] Entry 1[/td][td][/td][td=bgcolor:#E2EFDA]Label C[/td][td=bgcolor:#E2EFDA] Entry 1, Entry 2, Entry 3[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Label C[/td][td] Entry 1[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Label C[/td][td=bgcolor:#DDEBF7] Entry 2[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Label C[/td][td] Entry 3[/td][td][/td][td][/td][td][/td][/tr]
[/table]