Move data in one column to multiple columns

spraggn · Nov 29, 2005

Hi I need to move data in 1 column into 3 other columns over 9999 rows. The data is repeated in blocks of 3 rows in the intial colum.

eg

A
0.1111
0.2222
0.3333
0.4444
0.5555
0.6666
0.7777
0.8888
0.9999
0.1212

I need the first 3 rows (0.1111, 0.2222, 0.3333) moved into 3 columns (B,C,D)

then the 2nd block of 3 rows values (0.4444, 0.5555, 0.6666) moved also into the B,C,D columns beneath the 1 st row.

This needs to be repeated for all 9999 rows in the initial column. So I should end up with 3333 rows of data

The data in the first column will start at A1

Thanks for any help

[/img]

acw · Nov 29, 2005

Hi

B1: =OFFSET($A$1,(ROW()-1)*3+COLUMN()-2,0)
Copy across to C1 and D1, then copy down as required. This will break up the data from column A into the relevant columns. Copy then paste value the data as required.

HTH

Tony

Peter_SSs · Nov 30, 2005

spraggn

This does not address your problem exactly in term of where the results go, but is versatile in converting a long column into a rectangular array.

Place numbers 1, 2,... across row 1, starting in column C, for how many columns you want (3 in this case) and down column B from row 2 (1 to 3333 for your case).

In cell B1: =MAX(C1:E1) adjust range to suit how far you put numbers across row 1.

In cell C2 put the formula =IF(INDEX($A:$A,$B$1*($B2-1)+C$1)="","",INDEX($A:$A,$B$1*($B2-1)+C$1))

Drag this formula across row 2 as far as the numbers in row 1 go, then double click the 'Fill Handle' to copy down as far as the numbers go in column B.

If you don't mind a few zeros at the end you could use the shortened formula: =INDEX($A:$A,$B$1*($B2-1)+C$1))

Here is a smaller sample. If you are looking to print the results, Print Area could be set to C2:E7 for this small example.

Mr Excel.xls