Sort of Transpose a large array

[rosnathan]

Hello,
I have a large array that has is currently setup in 10 columns and 4050 rows. However, I am trying to get the data in the format of 162 rows by 248 columns by appending rows with data from below, and then tightening once 248 columns have been reached.

The example is well illustrated with this simple example:
We start with this array:
1 2 3
4 5 6
7 8 9
10 11 12
13 14 15
16 17 18

and we are looking for an array that looks like this:
1 2 3 4 5 6 7 8 9
10 11 12 13 14 15 16 17 18

where the value 9 in the example would be 248 (columns) for my real problem.

Anyone, have any quick fixes?
Thanks so much!

[tony h]

There are lots of ways to do this. This will work and is easy to modify.

It expects the original data to be on a sheet called DataHere and will output on a sheet called Reformatted (which must exist)

Please Login or Register  to view this content.

[rosnathan]

I'm not that familiar with running macros in Excel... do you think you could explain how to get this script working? Thanks Tony!

[jindon]

rosnathan

1) Hit Alt + F11 to open VBE.
2) go to [Insert] - [Module] then paste the code on to the right pane.
3) Hit Alt + Q to get back to Excel
4) hit Alt + F8 - choose "test" then click on [Run]

Condition;
Sheet1 holds the data
Sheet2 is the destination for output.

Please Login or Register  to view this content.

[tony h]

largeTranspose.xlsm
Try this ... once you click on the button it takes a while to complete. Note you will need to enable macros