Merge Data From Two Lines Into One

[jdkuehne]

I'm not really sure what the correct name is for what I want to do, so here's a description.

I have an Excel sheet where column A has, in some cases, the same value, but column B is always different. I want to move the secondary column B values to column C, Column D, etc. o the first instance of the value in column A. Like this...

This data set...

A	B	C	D	E
widget1	1-1
widget1	1-2
widget1	1-3
widget2	2-1
widget2	2-2
widget3	3-1
widget4	4-1
widget4	4-2
widget4	4-3

<tbody>
</tbody>

should be converted to this.

A	B	C	D	E
widget1	1-1	1-2	1-3
widget2	2-1	2-2
widget3	3-1
widget4	4-1	4-2	4-3

<tbody>
</tbody>

 

[sandy666]

try PowerQuery

A	B	C	D	E		A	B.1	B.2	B.3
widget1	1-1					widget1	1-1	1-2	1-3
widget1	1-2					widget2	2-1	2-2
widget1	1-3					widget3	3-1
widget2	2-1					widget4	4-1	4-2	4-3
widget2	2-2
widget3	3-1
widget4	4-1
widget4	4-2
widget4	4-3

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type any}, {"D", type any}, {"E", type any}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"A"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "B", each Table.Column([Count],"B")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"B", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "B", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"B.1", "B.2", "B.3"})
in
    #"Split Column by Delimiter"[/SIZE]

 

[jdkuehne]

Sandy,

Thank you for that. I have no idea how to use it, but now that I know it's possible, I'll figure it out.

Cheers!

 

[Joe4]

Because you posted this in the Excel Question section, and not the Power Query section, here is an Excel VBA solution:

Sub MyCombine()

    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Set first row to start on (skipping first row of data)
    r = 3
    
    Do
'       Check to see if column A equals row above it
        If Cells(r, "A") = Cells(r - 1, "A") Then
'           Copy value from column B to end of row above it
            Cells(r - 1, Columns.Count).End(xlToLeft).Offset(0, 1) = Cells(r, "B")
'           Delete row
            Rows(r).Delete
        Else
'           Move on to next row
            r = r + 1
        End If
    Loop Until Cells(r, "A") = ""
    
    Application.ScreenUpdating = True
    
End Sub

 

[sandy666]

sure,

Excel for Office 365 (built-in) Excel 2019 (built-in) Excel 2016 (built-in) Excel 2013 (add-in) Excel 2010 (add-in)

• Getting Started with Get & Transform in Excel
• Microsoft Power Query for Excel Help
• Excel Power Query (Get & Transform) Tutorial for Beginners

 

[Rick Rothstein]

Sandy,

Thank you for that. I have no idea how to use it, but now that I know it's possible, I'll figure it out.

Here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub TransposeWidgets()
  Dim Ar As Range
  With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate("IF(" & .Address & "=" & .Offset(-1).Address & ",""""," & .Address & ")")
    For Each Ar In .SpecialCells(xlBlanks).Areas
      Ar(1).Offset(-1, 1).Resize(, Ar.Count + 1).NumberFormat = "@"
      Ar(1).Offset(-1, 1).Resize(, Ar.Count + 1) = Application.Transpose(Ar(1).Offset(-1, 1).Resize(Ar.Count + 1))
    Next
    .SpecialCells(xlBlanks).EntireRow.Delete
  End With
End Sub[/td]
[/tr]
[/table]

 

[jdkuehne]

Thank you Joe4 and Rick. It's always good to have options. I have a lot to learn.

 

[Joe4]

You are welcome!