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


--
greaseman
------------------------------------------------------------------------
greaseman's Profile: http://www.excelforum.com/member.php...o&userid=28808
View this thread: http://www.excelforum.com/showthread...hreadid=537855

Transpose data from rows into columns
 

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" wrote
in message ...

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

Transpose data from rows into columns
 

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
------------------------------------------------------------------------
greaseman's Profile: http://www.excelforum.com/member.php...o&userid=28808
View this thread: http://www.excelforum.com/showthread...hreadid=537855

Transpose data from rows into columns
 

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.


--
greaseman
------------------------------------------------------------------------
greaseman's Profile: http://www.excelforum.com/member.php...o&userid=28808
View this thread: http://www.excelforum.com/showthread...hreadid=537855