Convert text to column and transpose

[Biggy]

Hello, Excel Gurus!

I have some data like below in the first image and I want to change it to like the second image by using a macro.

In the real table there are a lot more columns and rows, but columns who should be made "text to column" and "transposed" are two: Product Color, Product size. Would be great if it's possible to copy other corresponding information, e.g. "Product Code" and "Product Name", but an issue is that for some rows in some columns could be blank, so risk that it will take information from the previous item.

Hopefully, someone will be able to help because I checked similar forum posts, but adjusting didn't helped or more believable variant: I didn't know exactly how to do it. I am using Excel 2016.

Before:

After:

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub ExpandProductsBColorAndSize()
  Dim R As Long, X As Long, Z As Long, Index As Long, LastRow As Long, MaxResults As Long
  Dim Data As Variant, Results As Variant, C() As String, S() As String
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("D2:E" & LastRow) = Evaluate(Replace("IF(RIGHT(D2:E#)=""|"",LEFT(D2:E#,LEN(D2:E#)-1),REPT(D2:E#,1))", "#", LastRow))
  Data = Range("A2:F" & LastRow)
  MaxResults = Evaluate(Replace("SUM((1+LEN(D2:D#)-LEN(SUBSTITUTE(D2:D#,""|"","""")))*(1+LEN(E2:E#)-LEN(SUBSTITUTE(E2:E#,""|"",""""))))", "#", LastRow))
  ReDim Results(1 To MaxResults, 1 To 6)
  For R = 1 To UBound(Data)
    C = Split(Data(R, 4), "|")
    S = Split(Data(R, 5), "|")
    For X = 0 To UBound(C)
      For Z = 0 To UBound(S)
        Index = Index + 1
        Results(Index, 1) = Data(R, 1)
        Results(Index, 2) = Data(R, 2)
        Results(Index, 3) = Data(R, 3)
        Results(Index, 4) = C(X)
        Results(Index, 5) = S(Z)
        Results(Index, 6) = Data(R, 6)
      Next
    Next
  Next
  Range("A2").Resize(MaxResults, 6) = Results
End Sub[/td]
[/tr]
[/table]

 

[Biggy]

Wow, Just WOW. Thank You very, very, very much! I can't believe how fast I got an answer.

My example was pretty basic one, real situation is much, much complicated, so I must to modify it and I went through Your code to understand what exactly I should do because, honestly, I almost nothing understand from macros.

I tried to understand as much as I can, but I got some questions. Would be great if it’s would be possible to get some answers and explanations. I could post real example, but in that case I wouldn’t learn anything, but I wanna know, so next time I will have not to ask (hopefully).

So here are a questions:
What means C(), S() in given macro? I guess with it we define what we should split columns 4,5 and for splitting use |, but why exactly C() and S()? What to do if there is third or fourth column I should modify same way? Just use e.g. N(), M()?

Why in the Range("D2:E" & LastRow) = Evaluate(Replace("IF(RIGHT(D2:E#)=""|"",LEFT(D2:E#,LEN(D2:E#)-1),REPT(D2:E#,1))", "#", LastRow)) is exactly D2:E (I guess it’s because in these columns are data who should be separated and accordingly transposed, but what to do if these columns isn’t next to each other or there are more columns?)?

In MaxResults cell range is given because, those columns should be transposed, but what happens if there is one more additional column? Is there just on more *(1+LEN(E2:E#)-LEN(SUBSTITUTE(E2:E#,""|"",""""))) with necessary column?I guess what R is meant column number, but why it’s defined as R? Is by default rows defined as R? What means X = 0 To UBound(C), For Z = 0 To UBound(S), what happens if there are two more rows?

Sincerely,
Tony