Transpose data from rows into columns

[greaseman]

I have an Excle file that consists of 12 pieces of data in each row.
What I'm wanting to do is to take the first 3 columns of data into
three columns, and then do the same for the next row and so forth.
After that, I want to then go back to the first row of data and take
the data from columns 4 -6 and place that data into the next next row
of the same three columns, go down one row, grab the next row's data
form columns 4-6 and place it into the next row of the same three
columns and so on. I then want to repeat the process for all the rows,
using columns 7 -9 and then columns 10 - 12.

Example:

Row 1: 1x 2x 3x 4y 5y 6y 7z 8z 9z 10a 11a 12a
Row 2: 1a 2a 3a 4b 5b 6b 7c 8c 9c 10z 11z 12z

Desired results:

1x 2x 3x
4y 5y 6y
7z 8z 9z
10a 11a 12a
1a 2a 3a
4b 5b 6b
7c 8c 9c
10z 11z 12z

If anyone has some suggestions or ideas on how this gets done, I would
definitely appreciate the replies. Thanks in advance!!!

 

[Tom Ogilvy]

Sub Unwrap()
Dim sh As Worksheet, sh1 As Worksheet
Dim i As Long, j As Long
Dim i1 As Long, j1 As Long
Set sh = ActiveSheet
i = 1
j = 1
Set sh1 = Worksheets.Add(After:= _
Worksheets(Worksheets.Count))
i1 = 1
j1 = 1
Do While Not IsEmpty(sh.Cells(i, j))
Debug.Print i, j, i1, j1
sh1.Cells(i1, j1) = sh.Cells(i, j)
j1 = j1 + 1
j = j + 1
If j1 > 3 Then
i1 = i1 + 1
j1 = 1
End If
If j > 12 Then
j = 1
i = i + 1
End If
Loop

End Sub

 

[greaseman]

Tom,

Thank you for your code suggestion! I'll play around with it today and
see what pops out on the other end. It looks pretty much like what I
need.

Thanks again!

 

[greaseman]

Tom,

Your code suggestion worked great, however I made a mistake in what I
was asking for. I meant to ask for a suggestion for putting the data
into a different format and not what I originally asked for - my fault
entirely. What I actually wanted was:

Example:

Row 1: 1x 2x 3x 4y 5y 6y 7z 8z 9z 10a 11a 12a
Row 2: 1a 2a 3a 4b 5b 6b 7c 8c 9c 10z 11z 12z

Desired results:

1x 2x 3x
1a 2a 3a
4y 5y 6y
4b 5b 6b
7z 8z 9z
7c 8c 9c
10a 11a 12a
10z 11z 12z

How would I modify your code to give me this type of result?? And
again, entirely my fault for not asking for what I really wanted to
obtain.

Thanks in advance for your assistance.