Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.