Splitting data in a table into another table with different size

[ijhoeq]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Part Number
[/TD]
[TD="align: center"]Name
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]ABC123[/TD]
[TD]C1,C2,C3,C15[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]XYZ321[/TD]
[TD]J1[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]IJK100[/TD]
[TD]L1,L2[/TD]
[/TR]
</tbody>[/TABLE]

Hello,

I have data like the table above (with many more rows and longer names) and I need to break the Name column so that each name is separate with the Part Number listed next to it. An example of what I want to produce is shown below. Any help would be greatly appreciated!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Part Number
[/TD]
[TD="align: center"]Name
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]ABC123[/TD]
[TD]C1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]ABC123[/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]ABC123[/TD]
[TD]C3[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]ABC123[/TD]
[TD]C15[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]XYZ321[/TD]
[TD]J1[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]IJK100[/TD]
[TD]L1[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]IJK100[/TD]
[TD]L2[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

with Power Query

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]ABC123[/td][td=bgcolor:#DDEBF7]C1,C2,C3,C15[/td][td][/td][td=bgcolor:#E2EFDA]ABC123[/td][td=bgcolor:#E2EFDA]C1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]XYZ321[/td][td]J1[/td][td][/td][td]ABC123[/td][td]C2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]IJK100[/td][td=bgcolor:#DDEBF7]L1,L2[/td][td][/td][td=bgcolor:#E2EFDA]ABC123[/td][td=bgcolor:#E2EFDA]C3[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]ABC123[/td][td]C15[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]XYZ321[/td][td=bgcolor:#E2EFDA]J1[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]IJK100[/td][td]L1[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]IJK100[/td][td=bgcolor:#E2EFDA]L2[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Name")
in
    Split[/SIZE]

 

[DanteAmor]

Run this macro, the result in columns D and E

Sub Splitting_data()
  Dim c As Range, s As Variant, i As Long
  i = 2
  For Each c In Range("B2", Range("B" & Rows.Count).End(xlUp))
    For Each s In Split(c, ",")
      Cells(i, "D").Value = c.Offset(, -1)
      Cells(i, "E").Value = s
      i = i + 1
    Next
  Next
End Sub

 

[ijhoeq]

Thanks!

 

[sandy666]

Welcome!

 

[DanteAmor]

Youre welcome.