CODEWITHSUNDEEP
excelexcel-formula
Lasith Malinga
Lasith Malinga

Reputation: 135

divide data in one column into more column in excel

i want to do this in Microsoft Excel 2007

This a one column. I have 20098 data in one column like below.

1
2
3
4
5
6
7
8
131
1
31
31
31
31
41

I want to rearrange those data like this how can i do it

1 4 7 1 31
2 5 8 31 31
3 6 131 31 41

Upvotes: 1

Views: 3970

Answers (2)

brettdj
brettdj

Reputation: 55682

If your data was in column A then in cell B1 put
=OFFSET($A1,3*(COLUMN()-COLUMN($B$1)),)
and copy down and right to split your data as desired

enter image description here

Upvotes: 3

Aprillion
Aprillion

Reputation: 22324

use this formula in all 3 rows and 6700 columns of the resulting range:

=INDEX($A:$A;(COLUMN()-first_column)*3 + ROW()-first_row+1)

where first_column is =column(..) and first_row is =row(..) of the cell where you want to have the 1st value

e.g. if you use B1:IWS3 range to list the results, the formula will be:

=INDEX($A:$A;(COLUMN()-2)*3 + ROW())

Upvotes: 2

Related Questions