# Rearranging a 15 X 25 Excel matrix into three columns

In Excel I have data in cells from A1 to O5 (15 X 25).

I wish to rearrange things such that I have the following

``````  [A1][B1][C1]
[D1][E1][F1]
[G1][H1][I1]
[J1][K1][L1]
[M1][N1][O1]
[A2][B2][C2]
[D2][E2][F2]
``````

...and so on.

I've tried and failed to find an elegant way to do this in Excel. Could someone please advise me?

I've seen this but they seem to be playing a slightly different game there.

-

Here is a VBA shot:

``````Option Explicit

Sub rearrange()
Dim aValues As Variant
Dim i As Integer, j As Integer
Dim lRow As Long

'store the values in an array
aValues = Worksheets("Sheet1").Range("A1:O5").Value
lRow = 1

With Worksheets("Sheet2")
For i = 1 To UBound(aValues, 1)
For j = 1 To UBound(aValues, 2) Step 3
.Cells(lRow, 1).Value = aValues(i, j)
.Cells(lRow, 2).Value = aValues(i, j + 1)
.Cells(lRow, 3).Value = aValues(i, j + 2)
lRow = lRow + 1
Next j
Next i
End With
End Sub
``````
-
successful test +1 – Raystafarian Feb 14 '12 at 14:38

• Set E21 to `=INT((ROW(A1)-1)/5)`
• Set F21 to `=MOD(ROW(A1)-1,5)`
• Set A21 to `=INDIRECT("R" & (\$E21+1)&"C" & (COLUMN(A21)+\$F21*3),FALSE)`