How can I split Excel data from 1 column into 8 columns while keeping the data in a specific order?

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?

Ralph

Posted 2012-08-16T14:24:57.677

Reputation: 43

I tried to post pics of what I am doing but I don't have enough forum "points" to do so :S – Ralph – 2012-08-16T15:15:13.137

Answers

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

wbeard52

Posted 2012-08-16T14:24:57.677

Reputation: 3 149

This worked like a charm thank you very much! – Ralph – 2012-08-16T18:42:06.513

Here is a solution. It assumes the following:

All the original data is in column A
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)

soandos

Posted 2012-08-16T14:24:57.677

Reputation: 22 744

@Ralph please let me know if there are any issues. Working to clean it up a bit now. – soandos – 2012-08-16T15:05:00.530

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 – 2012-08-16T15:10:09.813

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 – 2012-08-16T15:14:08.843

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 – 2012-08-16T18:41:43.067

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.

Andy Raddatz

Posted 2012-08-16T14:24:57.677

Reputation: 111