Need to split one long column into multiple rows

I have a single column of numbers constituting about 42,000 rows.
They're from a "Monte Carlo" simulation of an economic model spanning
75 years, and the results from each of 500 simulations are stacked on
top of each other. Eg, for simulation 1 you get years 1 through 75,
then repeated for simulation 2, etc.

What I'd like to do is split the single column into multiple columns
representing each simulation. So, in this case, I'd have 500 columns
with 75 rows each rather than a single column with 42,000 rows. This
woudl make it much easier for me to analyze the distribution of
outcomes.

But is there any easy way to do this? With a smaller column I'd just
cut/paste, but this is a huge amount of data. Any suggestions on how to
do this??

Thanks!!

Andrew

Probably the easiest way to do this would be by using the INDIRECT function.

Assuming you have all data in column A1:A42000,
On sheet 2, make a table with row and column headers 0-74 and 0-499, and
fill the area with the formula:
=INDIRECT("Sheet1!A"&B$1*75+$A2+1)

After that, copy-paste values and do whatever you like.

Bas Hartkamp.

<agbiggs@hotmail.com> schreef in bericht
news:1141761267.819155.20010@p10g2000cwp.googlegroups.com...
>I have a single column of numbers constituting about 42,000 rows.
> They're from a "Monte Carlo" simulation of an economic model spanning
> 75 years, and the results from each of 500 simulations are stacked on
> top of each other. Eg, for simulation 1 you get years 1 through 75,
> then repeated for simulation 2, etc.
>
> What I'd like to do is split the single column into multiple columns
> representing each simulation. So, in this case, I'd have 500 columns
> with 75 rows each rather than a single column with 42,000 rows. This
> woudl make it much easier for me to analyze the distribution of
> outcomes.
>
> But is there any easy way to do this? With a smaller column I'd just
> cut/paste, but this is a huge amount of data. Any suggestions on how to
> do this??
>
> Thanks!!
>
> Andrew
>

Andrew,

This can be done...sort of.

You can only have 256 columns per sheet, so you are limited. You might be able to mess around with this code, but as it is, it will move data from column A (assuming 42,000 rows all in column A) to columns A-IF with 175 rows in each column.

Sub movedata()
Dim copycolumns As Long
Dim MoveAcross As Long

For copycolumns = 1 To 255
Cells(1, 1).Select
MoveAcross = MoveAcross + 1
ActiveCell.Offset(copycolumns * 175, 0).Select
Range(ActiveCell, ActiveCell.Offset(174, 0)).Select
Selection.Cut
Cells(1, 1).Select
ActiveCell.Offset(0, MoveAcross).Select
ActiveSheet.Paste
Next copycolumns

End Sub


Hope it helps!

Kevin

Originally Posted by agbiggs@hotmail.com

I have a single column of numbers constituting about 42,000 rows.
They're from a "Monte Carlo" simulation of an economic model spanning
75 years, and the results from each of 500 simulations are stacked on
top of each other. Eg, for simulation 1 you get years 1 through 75,
then repeated for simulation 2, etc.

What I'd like to do is split the single column into multiple columns
representing each simulation. So, in this case, I'd have 500 columns
with 75 rows each rather than a single column with 42,000 rows. This
woudl make it much easier for me to analyze the distribution of
outcomes.

But is there any easy way to do this? With a smaller column I'd just
cut/paste, but this is a huge amount of data. Any suggestions on how to
do this??

Thanks!!

Andrew