reshape array of numbers

M

mathdoc66

Hello all,

I would like to know if there is an easy(?) way to change the shape
of an array of numbers.

I have a data set consisting of 300 numbers. They are in an array of
12 rows and 25 columns. I would like to change the list to just one
column of 300 numbers. Of course I could (and did) cut and paste
until I had one column, but I was thinking that Excel should have an
easy way to put all the numbers into one column. Occasionally, I
receive data that comes in different sized arrays that should have
been in one column.

Is there an easy method to do this, without any major programming,
etc.

Thanks for your help
Phil
 
M

Max

One way ..

Assuming the top left corner cell of the 12R x 25C matrix is A1

Place in say, AA1:
=OFFSET(A$1,INT((ROW(A1)-1)/25),MOD(ROW(A1)-1,25))
Copy AA1 down by 300 rows to exhaust the matrix
 
L

Lori

Try filling down from the top row of the sheet as far as required:

=INDEX($A$1:$Y$25,1+(ROW()-1)/25,1+MOD(ROW()-1,25))

For other dimensions replace 25 by the number of columns.
 
R

RagDyeR

You didn't mention what configuration you wished to follow with the copying
of your array data.
*Both* of the above suggestions go across the the columns first, then down
the rows.

In case you would like to start *down the rows first*, try this:

=INDEX($A$1:$Y$12,MOD(ROWS($1:1)-1,12)+1,ROWS($1:12)/12)

This formula can be entered *anywhere*, and copied down 300 rows.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hello all,

I would like to know if there is an easy(?) way to change the shape
of an array of numbers.

I have a data set consisting of 300 numbers. They are in an array of
12 rows and 25 columns. I would like to change the list to just one
column of 300 numbers. Of course I could (and did) cut and paste
until I had one column, but I was thinking that Excel should have an
easy way to put all the numbers into one column. Occasionally, I
receive data that comes in different sized arrays that should have
been in one column.

Is there an easy method to do this, without any major programming,
etc.

Thanks for your help
Phil
 
B

Bernd

Hi Phil,

If your data resides in A1:Y12 then select Z1:Z300, for example, and
enter as array formula (enter with CTRL + SHIFT´+ ENTER):
=reshape(A1:Y12)

My UDF reshape you have to enter into a macro module:
Press ALT + F11
Insert a module
Enter macro code from http://www.sulprobil.com/html/reshape.html

Regards,
Bernd
 
M

mathdoc66

Thank you all for your quick replies. This are all very good, and
quite easy.

Thanks,
Phil
 
B

bosscar

Thank you all for your quick replies. This are all very good, and
quite easy.

Thanks,
Phil







- Show quoted text -
Click to expand...

If i have a column of 9 numbers, how can i get a 3x3 matrix?
 
R

RagDyeR

Same question as above ... if data is in A1 to A9, what configuration do you
wish to follow?

A1 A4 A7
A2 A5 A8
A3 A6 A9
=INDEX($A$1:$A$9,MOD(ROWS($1:1)-1,3)+3*COLUMNS($A:A)-2)
Enter *anywhere* and copy down 3 rows and then across 3 columns.

OR

A1 A2 A3
A4 A5 A6
A7 A8 A9
=INDEX($A$1:$A$9,3*ROWS($1:1)+COLUMNS($A:A)-3)
Enter *anywhere* and copy down 3 rows and then across 3 columns.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Thank you all for your quick replies. This are all very good, and
quite easy.

Thanks,
Phil







- Show quoted text -
Click to expand...

If i have a column of 9 numbers, how can i get a 3x3 matrix?
 
B

Bernd

Hello,

If your data resides in A1:A9 then select B1:D3, for example, and
enter as array formula (enter with CTRL + SHIFT´+ ENTER):
=reshape(A1:A9)

My UDF reshape you have to enter into a macro module:
Press ALT + F11
Insert a module
Enter macro code from http://www.sulprobil.com/html/reshape.html

Regards,
Bernd
 
Top