Transpose data from rows into columns

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!!!

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

--
Regards,
Tom Ogilvy


"greaseman" <greaseman.275avb_1146514854.8471@excelforum-nospam.com> wrote
in message news:greaseman.275avb_1146514854.8471@excelforum-nospam.com...
>
> 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!!!
>
>
> --
> greaseman
> ------------------------------------------------------------------------
> greaseman's Profile:
http://www.excelforum.com/member.php...o&userid=28808
> View this thread: http://www.excelforum.com/showthread...hreadid=537855
>

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!

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.