# Move data in one column to multiple columns

#### spraggn

##### New Member
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

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

thanks for your help Tony and Peter

Cheers

Nick

Replies
0
Views
163
Replies
3
Views
246
Replies
2
Views
210
Replies
2
Views
308
Replies
0
Views
493

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.

### Which adblocker are you using?

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

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