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...
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]widget1
[/TD]
[TD]1-1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]widget1
[/TD]
[TD]1-2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]widget1
[/TD]
[TD]1-3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]widget2
[/TD]
[TD]2-1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]widget2
[/TD]
[TD]2-2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]widget3
[/TD]
[TD]3-1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]widget4
[/TD]
[TD]4-1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]widget4
[/TD]
[TD]4-2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]widget4
[/TD]
[TD]4-3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


should be converted to this.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]widget1
[/TD]
[TD]1-1
[/TD]
[TD]1-2
[/TD]
[TD]1-3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]widget2
[/TD]
[TD]2-1
[/TD]
[TD]2-2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]widget3
[/TD]
[TD]3-1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]widget4
[/TD]
[TD]4-1
[/TD]
[TD]4-2
[/TD]
[TD]4-3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]A[/td][td=bgcolor:#5B9BD5]B[/td][td=bgcolor:#5B9BD5]C[/td][td=bgcolor:#5B9BD5]D[/td][td=bgcolor:#5B9BD5]E[/td][td][/td][td=bgcolor:#70AD47]A[/td][td=bgcolor:#70AD47]B.1[/td][td=bgcolor:#70AD47]B.2[/td][td=bgcolor:#70AD47]B.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]widget1[/td][td=bgcolor:#DDEBF7]1-1[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]widget1[/td][td=bgcolor:#E2EFDA]1-1[/td][td=bgcolor:#E2EFDA]1-2[/td][td=bgcolor:#E2EFDA]1-3[/td][/tr]

[tr=bgcolor:#FFFFFF][td]widget1[/td][td]1-2[/td][td][/td][td][/td][td][/td][td][/td][td]widget2[/td][td]2-1[/td][td]2-2[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]widget1[/td][td=bgcolor:#DDEBF7]1-3[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]widget3[/td][td=bgcolor:#E2EFDA]3-1[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]widget2[/td][td]2-1[/td][td][/td][td][/td][td][/td][td][/td][td]widget4[/td][td]4-1[/td][td]4-2[/td][td]4-3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]widget2[/td][td=bgcolor:#DDEBF7]2-2[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]widget3[/td][td]3-1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]widget4[/td][td=bgcolor:#DDEBF7]4-1[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]widget4[/td][td]4-2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]widget4[/td][td=bgcolor:#DDEBF7]4-3[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

[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!