Repeat lines for each comma delimited value.

[joshbjames]

I have a list of part numbers, descriptions and cross-reference numbers. The cross-reference numbers are stored in a comma delimited field. Sometimes there are 40+ cross-reference numbers, sometimes there are none. In a new sheet, I would like to make a new line for each cross-reference number and populate it with the parent part number and description.

Starting Data

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Description[/TD]
[TD]Cross-reference[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]gear[/TD]
[TD]abc,efg,hij,klm[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]sprocket[/TD]
[TD]nop[/TD]
[/TR]
[TR]
[TD]9012[/TD]
[TD]chain[/TD]
[TD]qrs,tuv[/TD]
[/TR]
[TR]
[TD]3456[/TD]
[TD]tensioner[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









Final Data

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Description[/TD]
[TD]Cross-reference[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]gear[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]gear[/TD]
[TD]feg[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]gear[/TD]
[TD]hij[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]gear[/TD]
[TD]klm[/TD]
[/TR]
[TR]
[TD]5678[/TD]
[TD]sprocket[/TD]
[TD]nop[/TD]
[/TR]
[TR]
[TD]9012[/TD]
[TD]chain[/TD]
[TD]qrs[/TD]
[/TR]
[TR]
[TD]9012[/TD]
[TD]chain[/TD]
[TD]tuv[/TD]
[/TR]
[TR]
[TD]3456[/TD]
[TD]tensioner[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance, this is proving quite difficult.

 

[sandy666]

is that what you want?

with PowerQuery

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1234​

[/td][td=bgcolor:#DDEBF7]gear[/td][td=bgcolor:#DDEBF7]abc,efg,hij,klm[/td][td][/td][td=bgcolor:#E2EFDA]

1234​

[/td][td=bgcolor:#E2EFDA]gear[/td][td=bgcolor:#E2EFDA]abc[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

5678​

[/td][td]sprocket[/td][td]nop[/td][td][/td][td]

1234​

[/td][td]gear[/td][td]efg[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

9012​

[/td][td=bgcolor:#DDEBF7]chain[/td][td=bgcolor:#DDEBF7]qrs,tuv[/td][td][/td][td=bgcolor:#E2EFDA]

1234​

[/td][td=bgcolor:#E2EFDA]gear[/td][td=bgcolor:#E2EFDA]hij[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

3456​

[/td][td]tensioner[/td][td][/td][td][/td][td]

1234​

[/td][td]gear[/td][td]klm[/td][/tr]

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

5678​

[/td][td=bgcolor:#E2EFDA]sprocket[/td][td=bgcolor:#E2EFDA]nop[/td][/tr]

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

9012​

[/td][td]chain[/td][td]qrs[/td][/tr]

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

9012​

[/td][td=bgcolor:#E2EFDA]chain[/td][td=bgcolor:#E2EFDA]tuv[/td][/tr]

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

3456​

[/td][td]tensioner[/td][td][/td][/tr]
[/table]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Cross-reference", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Cross-reference")
in
    #"Split Column by Delimiter"

 

[Fluff]

Or with VBA

Sub SplitAddRows()
   Dim Ary As Variant, Oary As Variant, Splt As Variant
   Dim r As Long, c As Long, i As Long, j As Long
   
   Ary = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("A1").CurrentRegion.Value2
   ReDim Oary(1 To UBound(Ary) * 50, 1 To 3)
   
   For r = 1 To UBound(Ary)
      i = i + 1
      Oary(i, 1) = Ary(r, 1)
      Oary(i, 2) = Ary(r, 2)
      If InStr(1, Ary(r, 3), ",") = 0 Then
         Oary(i, 3) = Ary(r, 3)
      Else
         Splt = Split(Ary(r, 3), ",")
         Oary(i, 3) = Splt(0)
         For j = 1 To UBound(Splt)
            i = i + 1
            Oary(i, 1) = Ary(r, 1)
            Oary(i, 2) = Ary(r, 2)
            Oary(i, 3) = Splt(j)
         Next j
      End If
   Next r
   Sheets("[COLOR=#ff0000]New[/COLOR]").Range("A1").Resize(i, 3).Value2 = Oary
   
End Sub

change sheet names to match