transposing one column into rows 52 columns wide

[homejames]

Hi

I have answers to a survey from about 140 people listed in one column. The survey is 52 questions long.
I need to transpose the data, so the first 52 rows in the column are transposed into one row 52 columns long, and then the next 52 rows are transposed into the row below the first transposed row as a 52 column row and so on until all the data is so arranged.

the data starts in H1 to H7280.

to use an example with 3 items per survey it would be:
A
B
C
D
E
F
G
H
I

to

ABC
DEF
GHI

 

[CROY1985]

Try this:

=OFFSET($H$1,COLUMN()-COLUMN($G1)+((ROW()-ROW(G$1))*52),0)

Assuming you want your transpose to start in column G. You just need to copy that over for 52 columns.

 

[CROY1985]

So ive just realised i must still be asleep. G comes before H...DOH! Swap the G for I


=OFFSET($H$1,COLUMN()-COLUMN($I1)+((ROW()-ROW(I$1))*52),0)

 

[wrightyrx7]

Hi there,

Give this a go on a COPY of your workbook.

It should transpose the data in column H (each 52 rows) and put the data in Columns I to BH (52 columns)

Might be an easier way of writing the code but it should do what you want.

Sub transpose52()
Dim i As Long, x As Long
    With ActiveSheet
        x = 1
            For i = 1 To 7280
                Cells(x, 9).Resize(, 52) = WorksheetFunction.Transpose(Cells(i, 8).Resize(52))
                x = x + 1
                i = i + 51
            Next i
    End With
End Sub

 

[homejames]

Re: transposing one column into rows 52 columns wide - thanks it works!

thanks! that works perfectly!