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.

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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
share|improve this answer
    
successful test +1 –  Raystafarian Feb 14 '12 at 14:38

Does this meet your needs?

  • 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)
  • Copy A21 to B21 and C21
  • Copy A21:F21 down as many rows as required.
  • Use Paste Special Values to convert A21:Cnn to values rather than formula.
share|improve this answer
    
This also has a successful test –  Raystafarian Feb 14 '12 at 14:41
    
I don't think the site allows me to tick both the suggestions, but they are both great. Thanks! –  user1205901 Feb 15 '12 at 11:31
    
JMax posted his answer a few seconds before mine so it is fair he gets the points for an accepted answer. –  Tony Dallimore Feb 15 '12 at 16:58

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.