Convert 8 Rows of Data from Columns to Rows 1

[Prospec]

Good afternoon,

In Excell have Sheet1 with 10,000 rows in 4 columns.

Need to transpose (in rows of 8) rows 2 to 9 of column A to rows then rows 2 to 9 of column B and so on for columns C and D in Sheet2.

Then rows 10 to 17 of column A in row 2 of of Sheet2 and soon on for all records in Sheet1

Here is sample data as is.

[COLOR=blue]
[B]A     B      C       D[/B][/color blue]
a1	b1	c1	d1
a2	b2	c2	d2
a3	b3	c3	d3
a4	b4	c4c	d4
a5	b5	c5	d5
a6	b6	c6	d6
a7	b7	c7	d7
a8	b8	c8	d8

Here is how data should look:

a1	a2	a3	a4	a5	a6	a7	a8	b1	b2	b3	b4	b5	b6	b7	b8	c1	c2

Thanks,

 

[macropod]

In Sheet2, enter the following formula into A1:
=OFFSET(Sheet1!$A$1,(ROW()-1)*4+INT((COLUMN()-1)/4),MOD((COLUMN()-1),4))
Copy across to P1, then copy A11 down as far as needed.

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

hi

dim lRow as long, iRow as integer, iCol as integer, lRow2 as long

for lRow = 1 to 10000 step 8
   lRow2 = lRow2 + 1
   for iCol = 0 to 3
      for iRow = 0 to 7
         sheet2.cells(lRow2, iCol*8 + iRow + 1).value = Sheet1.cells(lRow + iRow, iCol+1).Value
      next
    next
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]