Convert Single Column into Multiple

Question

Does anyone know how to take information from one column, that is just numbers, and convert it so that it is 9 columns wide, by approx. 52 rows long?

So Row 1 would be data from: A1, then A2, then A3, to A9

and Row 2 would be data from: A10, then A11, then A12, and so on.

Thanks! :)

Answer 1

Run this macro on a copy of your worksheet.

Enter 9 in the InputBox

Sub ColtoRows()

'routine to take 1 column of a variable number of rows(including blanks) and put in a

'choice of number of columns with A1,A2,A3,etc.moved to A1,B1,C1,etc.

Dim rng As Range

Dim i As Long

Dim j As Long

    Set rng = Cells(Rows.Count, 1).End(xlUp)

    j = 1

    On Error Resume Next

    nocols = InputBox("Enter Number of Columns Desired")

        For i = 1 To rng.row Step nocols

        Cells(j, "A").Resize(1, nocols).Value = _

                Application.Transpose(Cells(i, "A") _

                .Resize(nocols, 1))

        j = j + 1

    Next

    Range(Cells(j, "A"), Cells(rng.row, "A")).ClearContents

    Exit Sub

End Sub

Gord

Answer 2

Below is a formula approach. 

Put this formula anywhere and drag to right and down -

=IF(OR(COLUMNS($A:A)>9,9*(ROWS($1:1)-1)+COLUMNS($A:A)>COUNTA($A:$A)),"",INDEX($A:$A,9*(ROWS($1:1)-1)+COLUMNS($A:A)))