Consolidating columns of text based

Roonie847

New Member
Joined
Sep 6, 2019
Messages
4
I have a data set that looks like this:

Col1Col2
asam
bsam
csam
dsam
esam
fsam
gsam
abill
bbill
dbill
gbill
hbill
ddave
edave
fdave

<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>
</tbody>

and I need to convert it to look like this:
NewCol1 NewCol2
a sam|bill
b sam|bill
c sam
d sam|bill|dave
e sam|dave
f sam|dave
h bill

<colgroup><col width="64" style="width:48pt" span="2"> </colgroup><tbody>
</tbody>

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,060
Office Version
365
Platform
Windows
What version of Xl are you using?
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,754
with Power Query aka Get&Transform

Column1Column2Column1Custom
asamasam|bill
bsambsam|bill
csamcsam
dsamdsam|bill|dave
esamesam|dave
fsamfsam|dave
gsamgsam|bill
abillhbill
bbill
dbill
gbill
hbill
ddave
edave
fdave

Code:
[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

New Member
Joined
Sep 6, 2019
Messages
4
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,754
no problem, wait for another solution

have a nice day
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,060
Office Version
365
Platform
Windows
Or with a macro
Code:
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

New Member
Joined
Sep 6, 2019
Messages
4
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,060
Office Version
365
Platform
Windows
If you had TEXTJOIN it could be done with formulae, but as you dont PQ or VBA is probably the easiest option.
 

Forum statistics

Threads
1,081,681
Messages
5,360,521
Members
400,590
Latest member
hiicinc

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top