2

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.

1
  • do you want formula? vba?
    – JMax
    Nov 21, 2011 at 7:27

5 Answers 5

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

1
  • If you need to do this for a group not starting at A1, you need to get a little more detailed with the offsets, like this (example has column starting at B3): =INDIRECT(ADDRESS((ROW($B3)-ROW($B$3)+1)*3+COLUMN(B3)-COLUMN($B3),COLUMN($B3)))
    – Joshua
    Sep 28, 2017 at 2:54
1

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
1

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.

0

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

0

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

You must log in to answer this question.

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