Convert one column into multiple rows

Solution 1:

You can use a formula to achieve this.

=INDIRECT(ADDRESS((ROW($A1)-1)*3+COLUMN(A1),1))

Enter the formula above into a blank cell. Copy it two cells to the right, then down until you start to get zeros (delete these).

In this formula, A1 points to the first item in the column you want to re-arrange. To change the number of columns, modify 3 in the formula into something else.

Solution 2:

Here is a way to do it in VBA (assuming that the data are in column A):

Option Explicit

Sub movetocolumns()
Dim i As Integer, iRow As Integer
Dim arrSource As Variant

'Set the first row
iRow = 1

With ActiveWorkbook.Worksheets("Sheet1")
    'get the data into an array from the first column
    arrSource = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))

    'parse every value of the array and add the data to the next column
    For i = 1 To (UBound(arrSource) - UBound(arrSource) Mod 3) Step 3
        .Cells(iRow, 2) = arrSource(i, 1)
        .Cells(iRow, 3) = arrSource(i + 1, 1)
        .Cells(iRow, 4) = arrSource(i + 2, 1)
        iRow = iRow + 1
    Next i
    'add the remaining values
    Select Case UBound(arrSource) Mod 3
        Case 1  'one item to add
            .Cells(iRow, 2) = arrSource(i, 1)
        Case 2  'still two items to add
            .Cells(iRow, 2) = arrSource(i, 1)
            .Cells(iRow, 3) = arrSource(i + 1, 1)
        Case Else   'nothing to add
    End Select
End With
End Sub

Solution 3:

you just need to convert it to multiple columns first, then select the column values and press Ctrl + C to copy them, then select a cell and right click to select Paste Special > Transpose.