Split rows into new rows

[Danny54]

How would I go about splitting the following sheet into the second sheet. Im looking for column d to contain a |
if it does, split column by commas and create new rows.

Input example

title	color	day	size	owner
ford	blue	mon	10	john
chev	red	tue	8.1|8.2, 8.3	joe

Output example

title	color	day	size	owner
ford	blue	mon	10	john
chev	red	tue	8.1|8.2,	joe
chev	red	tue	8.3	joe

thanks

 

[GraH]

Easily done with Power Query.
Add custom column and get text after delimiter pipe. Then Split column by delimiter coma and split to rows.
Google it for a step by step tutorial

 

[kweaver]

Are there always only 2 size groups max in column D or could there be more, each group separated by a comma?

 

[Danny54]

Are there always only 2 size groups max in column D or could there be more, each group separated by a comma?

There can be many many more. In this example, i show only two

Thanks

 

[Peter_SSs]

Try this with a copy of your workbook.

Sub Rearrange()
  Dim a As Variant, b As Variant, Sizes As Variant
  Dim i As Long, j As Long, k As Long, r As Long, uba2 As Long
  
  With Range("A1", Range("E" & Rows.Count).End(xlUp))
    a = .Value
    uba2 = UBound(a, 2)
    ReDim b(1 To Rows.Count, 1 To uba2)
    For i = 1 To UBound(a)
      r = r + 1
      For j = 1 To uba2
        b(r, j) = a(i, j)
      Next j
      Sizes = Split(a(i, 4), ",")
      If UBound(Sizes) > 0 Then
        b(r, 4) = Sizes(0)
        For k = 1 To UBound(Sizes)
          r = r + 1
          For j = 1 To uba2
            b(r, j) = a(i, j)
          Next j
          b(r, 4) = Sizes(k)
        Next k
      End If
    Next i
    .Offset(, .Columns.Count + 1).Resize(r).Value = b
  End With
End Sub

Here is my sample data and results of the code.

Danny54.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	title	color	day	size	owner		title	color	day	size	owner
2	ford	blue	mon	10	john		ford	blue	mon	10	john
3	chev	red	tue	8.1|8.2, 8.3	joe		chev	red	tue	8.1|8.2	joe
4							chev	red	tue	8.3	joe
5
Sheet1

 

[Danny54]

Thank you all

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.