Consolidating columns of text based

[Roonie847]

I have a data set that looks like this:

[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Col1[/TD]
[TD="width: 64, bgcolor: transparent"]Col2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]b[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]d[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]e[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]f[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]g[/TD]
[TD="bgcolor: transparent"]sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a[/TD]
[TD="bgcolor: transparent"]bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]b[/TD]
[TD="bgcolor: transparent"]bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]d[/TD]
[TD="bgcolor: transparent"]bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]g[/TD]
[TD="bgcolor: transparent"]bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]h[/TD]
[TD="bgcolor: transparent"]bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]d[/TD]
[TD="bgcolor: transparent"]dave[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]e[/TD]
[TD="bgcolor: transparent"]dave[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]f[/TD]
[TD="bgcolor: transparent"]dave[/TD]
[/TR]
</tbody>[/TABLE]

and I need to convert it to look like this:
[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]NewCol1[/TD]
[TD="width: 64, bgcolor: transparent"] NewCol2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]a[/TD]
[TD="bgcolor: transparent"] sam|bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]b[/TD]
[TD="bgcolor: transparent"] sam|bill[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]c[/TD]
[TD="bgcolor: transparent"] sam[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]d[/TD]
[TD="bgcolor: transparent"] sam|bill|dave[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]e[/TD]
[TD="bgcolor: transparent"] sam|dave[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]f [/TD]
[TD="bgcolor: transparent"] sam|dave[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]h[/TD]
[TD="bgcolor: transparent"] bill[/TD]
[/TR]
</tbody>[/TABLE]

Basically, I need to consolidate all of the values from Col2 into a single data element where the values in col1 are repeated. Been looking at a creative Vlookup combined with a concat but cannot figure it out. My dataset is about 6000 rows.

 

[Fluff]

What version of Xl are you using?

 

[Roonie847]

Excel 2016

 

[sandy666]

with Power Query aka Get&Transform

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]a[/td][td=bgcolor:#DDEBF7]sam[/td][td][/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]sam|bill[/td][/tr]

[tr=bgcolor:#FFFFFF][td]b[/td][td]sam[/td][td][/td][td]b[/td][td]sam|bill[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]c[/td][td=bgcolor:#DDEBF7]sam[/td][td][/td][td=bgcolor:#E2EFDA]c[/td][td=bgcolor:#E2EFDA]sam[/td][/tr]

[tr=bgcolor:#FFFFFF][td]d[/td][td]sam[/td][td][/td][td]d[/td][td]sam|bill|dave[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]e[/td][td=bgcolor:#DDEBF7]sam[/td][td][/td][td=bgcolor:#E2EFDA]e[/td][td=bgcolor:#E2EFDA]sam|dave[/td][/tr]

[tr=bgcolor:#FFFFFF][td]f[/td][td]sam[/td][td][/td][td]f[/td][td]sam|dave[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]g[/td][td=bgcolor:#DDEBF7]sam[/td][td][/td][td=bgcolor:#E2EFDA]g[/td][td=bgcolor:#E2EFDA]sam|bill[/td][/tr]

[tr=bgcolor:#FFFFFF][td]a[/td][td]bill[/td][td][/td][td]h[/td][td]bill[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]b[/td][td=bgcolor:#DDEBF7]bill[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]d[/td][td]bill[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]g[/td][td=bgcolor:#DDEBF7]bill[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]h[/td][td]bill[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]d[/td][td=bgcolor:#DDEBF7]dave[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]e[/td][td]dave[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]f[/td][td=bgcolor:#DDEBF7]dave[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Column1"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Column2")),
    Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), "|"), type text})
in
    Extract[/SIZE]

 

[Roonie847]

Ok... I am sorry, I am sure that solves the problem but that is substantially beyond my capabilities... I do not have any idea how to execute that nor where to put it so I could execute it.

 

[sandy666]

no problem, wait for another solution

have a nice day

 

[Fluff]

Or with a macro

Sub Roonie847()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Cl.Offset(, 1).Value
         Else
            .Item(Cl.Value) = .Item(Cl.Value) & "|" & Cl.Offset(, 1).Value
         End If
      Next Cl
      Range("D2").Resize(.Count, 2).Value = Application.Transpose(Array(.keys, .items))
   End With
End Sub

 

[Roonie847]

Thanks for all of the help folks... I clearly need to take some Excel classes. It looks like that what I need to do is going to require something well beyond my abilities. Was thinking this was a simple thing that would not require coding or macros.

 

[Fluff]

If you had TEXTJOIN it could be done with formulae, but as you dont PQ or VBA is probably the easiest option.

 

[Toadstool]

Trying again...