Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
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.

share|improve this answer

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
share|improve this answer

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

enter image description here

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .