Move data in one column to multiple columns

spraggn

New Member
Joined
May 15, 2005
Messages
8
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]
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,488
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
10.11113123
20.222210.11110.22220.3333
30.333320.44440.55550.6666
40.444430.77770.88880.9999
50.555540.12120.12340.1256
60.666650.12780.13050.1322
70.777760.1344  
80.8888
90.9999
100.1212
110.1234
120.1256
130.1278
140.1305
150.1322
160.1344
17
Multiple Columns
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,104
Members
412,441
Latest member
kelethymos
Top