Convert text to column and transpose

Biggy

New Member
Joined
Feb 14, 2014
Messages
22
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:
before_zpsjct7ifrz.jpg


After:
after_zpsqafixyqs.jpg
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Give this macro a try...
Code:
[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]
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top