# 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.

-
do you want formula? vba? – JMax Nov 21 '11 at 7:27

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 2 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
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)