Convert one column into multiple rows

I simply want to take data from Column A and put it into Column B, Row 1; Column C, row 1 and Column D, row 1.

I want to put it into groups of three from top to bottom.

For example:

Column A
1
2
3
4
5
1
1
2
3

I want it to look like this

1 2 3
4 5 1
1 2 3

and keep on going.

5 Answers

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.

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

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.

It's not exactly pretty, but I was able to do it with 3 helper columns and a pivot table, as shown below.

In further extension to Ellesa's and Joshua's solution the following will paste from and to an arbitrary place in the sheet. The single column starting at $G$93 is transformed into rows with 10 entries each starting at $M$93:

=INDIRECT(ADDRESS(ROW($G$93)+(ROW($M93)-ROW($M$93))*10+(COLUMN(M93)-COLUMN($M$93)),COLUMN($G$93)))