Need to split one long column into multiple rows

[agbiggs]

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

 

[HS Hartkamp]

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.

 

[kevindmorgan]

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