Take the 2-minute tour ×

I need to maintain the order of the data. So to explain I need to move the data from cell

a1 to b1,
a2 to c1,
a3 to d1,
a4 to e1,
a5 to f1,
a6 to g1,
a7 to h1,
a8 to i1,

a9 to B2
a10 to C2
a11 to D2
a12 to E2
a13 to F2
a14 to G2
a15 to H2
a16 to I2

Rinse and repeat until I've gone through all 24000 entries in column A

Certainly there is an easy way to accomplish this via macro or vba? Perhaps maybe a feature in the most recent versions might already do this?

share|improve this question
I tried to post pics of what I am doing but I don't have enough forum "points" to do so :S –  Ralph Aug 16 '12 at 15:15
add comment

3 Answers

up vote 2 down vote accepted

Here is a VBA solution:

Option Explicit
Private Sub ReArrangeCells()

  Dim ws As Worksheet, LastRow As Long
  Set ws = Excel.ActiveSheet

  LastRow = Range("A65536").End(xlUp).Row

  Dim i As Long, j As Long, FromCell As Range, ToCell As Range, sNewCol As String, sNewRow As String
  For i = 1 To LastRow

    Set FromCell = ws.Range("A" & i)                       'the cell we want to move
    sNewCol = IIf(i Mod 8 = 0, Chr$(72), Chr$((i Mod 8) + 64))
    sNewRow = IIf(i Mod 8 = 0, (i \ 8), (i \ 8) + 1)
    Set ToCell = ws.Range(sNewCol & sNewRow)               'the cell we want to copy the data to

    FromCell.Copy ToCell
    If i <> 1 Then FromCell.Clear

    If i Mod 100 = 0 Then DoEvents

  Next i

End Sub
share|improve this answer
This worked like a charm thank you very much! –  Ralph Aug 16 '12 at 18:42
add comment

Here is a solution. It assumes the following:

  1. All the original data is in column A
  2. Column B contains just the index of the row

Formula: in =INDIRECT(CONCATENATE("a",$B2*8 + col_offset)) where col_offset is the column number (zero indexed). So in the first column it would be =INDIRECT(CONCATENATE("a",$B2*8)) and in the 8th column it would be =INDIRECT(CONCATENATE("a",$B2*8+7)).

A picture to make things clearer:

enter image description here

Edit: New formula: =INDIRECT(CONCATENATE("a",(ROW()-2)*8+(COLUMN()-3))) where the -2 and -3 are row and column offsets. Just paste this into all the cells where you want your data to show up. (this solution is better because column B can now be deleted)

share|improve this answer
Thanks for the quick reply! :) –  Ralph Aug 16 '12 at 15:04
@Ralph please let me know if there are any issues. Working to clean it up a bit now. –  soandos Aug 16 '12 at 15:05
I do get this to work for the first 8 (a1 to a8)!But how do I make it work beyond that? I tried to drag autofill down and that just gave me wrong data type error. I must be doing something wrong or not understanding. –  Ralph Aug 16 '12 at 15:10
See the edit for a new formula, but Ctrl + D should work fine (you need to do the whole row at once with the old formula), with the new one it does not matter –  soandos Aug 16 '12 at 15:14
Again, thank you for the solution. I could not figure out how to make it work, but i'm sure it's because I'm not doing something right. –  Ralph Aug 16 '12 at 18:41
add comment

The VBA answers are the best for handling a large amount of data, but I'll point out that you can also do this via copy/paste using "Transpose". It's an easy right-click solution for small needs. This applies to 2010 for sure and I believe 2007 as well.

Looks like I cannot do images yet either..

  • Copy your cells vertically
  • Right-click another cell and use the fourth option under "Paste Options" (looks like two blue rectangles at right angles)

Quicker in some circumstances.

share|improve this answer
add comment

Your Answer


