Splitting data in a table into another table with different size

[ijhoeq]

	A	B
1	Part Number	Name
2	ABC123	C1,C2,C3,C15
3	XYZ321	J1
4	IJK100	L1,L2

<tbody>
</tbody>

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!

	A	B
1	Part Number	Name
2	ABC123	C1
3	ABC123	C2
4	ABC123	C3
5	ABC123	C15
6	XYZ321	J1
7	IJK100	L1
8	IJK100	L2

<tbody>
</tbody>

 

[sandy666]

with Power Query

Part Number	Name		Part Number	Name
ABC123	C1,C2,C3,C15		ABC123	C1
XYZ321	J1		ABC123	C2
IJK100	L1,L2		ABC123	C3
			ABC123	C15
			XYZ321	J1
			IJK100	L1
			IJK100	L2

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