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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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
54,328
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,003
Messages
5,834,825
Members
430,324
Latest member
bosphoruskid

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
Top