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]
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,216,099
Messages
6,128,823
Members
449,470
Latest member
Subhash Chand

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top