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
Asked
Active
Viewed 3,918 times
1
Lasith Malinga
- 125
- 1
- 4
- 11
-
Columns go from top to bottom, rows go from left to right. What do you have, and how would you like to have it organized? – Mats Adborn Jun 30 '12 at 18:11
-
no no its a column. it's problem when in pasting it – Lasith Malinga Jun 30 '12 at 18:51
-
But do you want it split into three rows or three columns? – Mats Adborn Jun 30 '12 at 19:21
-
you will need vba to achieve this i think – Pynner Jul 01 '12 at 02:39
2 Answers
3
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
brettdj
- 54,857
- 16
- 114
- 177
-
`offset` is a volatile function, it will slow down calculations for 20 000+ rows i'm affraid... – Aprillion Jul 01 '12 at 12:46
-
@deathApril perhaps you should test that before downvoting on a optimisation constraint the OP didn't raise. The 20,000 cells (not rows as you posted) calculated instantly on my machine. – brettdj Jul 01 '12 at 13:07
-
perhaps,, but there is no way for me to revoke the downvote if you don't edit your answer ;) – Aprillion Jul 01 '12 at 13:23
-
1@deathApril I ran FastExcel V3, your 21000 cell calc (using `INDEX($A:$A,(COLUMN()-2)*3 + ROW())`) ran in 13.04ms, my 21000 cell calc in 13.81ms. Edit forthcoming ... :) – brettdj Jul 01 '12 at 13:26
-
can you test regular excel optimised recalculation (where difference between volatile functions might be visible)? e.g. after entering formula in D5 `=D2` - but you are right, the performance is not an issue in this case – Aprillion Jul 01 '12 at 13:33
-
@LasithMalinga In that case why not close out the question by accepting an answer? :) – brettdj Aug 21 '12 at 05:26
2
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())
Aprillion
- 21,510
- 5
- 55
- 89