How do I convert a row of cells into a two-dimensional array?
 

Using Excel 2002. I need to take a row of 60 cells of data and convert it
into a 4x15 array. Ideally, I would create the array on a separate worksheet.

Hi!

Assume the data on sheet1 is in the range A1:BH1

On sheet2 enter this formula:

=INDEX(Sheet1!$A$1:$BH$1,(ROW(1:1)-1)*15+COLUMN(A1))

Copy across 15 columns then down 4 rows.

Biff

"Glenn@stress" wrote in message
...
Using Excel 2002. I need to take a row of 60 cells of data and convert it
into a 4x15 array. Ideally, I would create the array on a separate
worksheet.

One way ..

a row of 60 cells of data

Assume it's meant that source data is in a column
in say, A1:A60 in Sheet1

In Sheet2
--------
To extract into a 15R x 4C grid

Put in say, A1:
=INDEX(Sheet1!$A$1:$A$60,ROWS($A$1:A1)*4-4
+COLUMNS($A$1:A1))

Copy A1 across to D1, fill down to D15

In Sheet3
-------
To extract into a 4R x 15C grid

Put in say, A1:
=INDEX(Sheet1!$A$1:$A$60,ROWS($A$1:A1)*15-15
+COLUMNS($A$1:A1))

Copy A1 across to O1, fill down to O4

The above 2 options should cover <g
what is meant by:
.. a 4x15 array

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Glenn@stress" wrote in message
...
Using Excel 2002. I need to take a row of 60 cells of data and convert it
into a 4x15 array. Ideally, I would create the array on a separate
worksheet.

Thanks,

Very interesting.

I meant by "4x15 array" an array or matrix of 4 columns by 15 rows.

Rgds
Glenn@stress

"Max" wrote:

One way ..

a row of 60 cells of data

Assume it's meant that source data is in a column
in say, A1:A60 in Sheet1

In Sheet2
--------
To extract into a 15R x 4C grid

Put in say, A1:
=INDEX(Sheet1!$A$1:$A$60,ROWS($A$1:A1)*4-4
+COLUMNS($A$1:A1))

Copy A1 across to D1, fill down to D15

In Sheet3
-------
To extract into a 4R x 15C grid

Put in say, A1:
=INDEX(Sheet1!$A$1:$A$60,ROWS($A$1:A1)*15-15
+COLUMNS($A$1:A1))

Copy A1 across to O1, fill down to O4

The above 2 options should cover <g
what is meant by:
.. a 4x15 array

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Glenn@stress" wrote in message
...
Using Excel 2002. I need to take a row of 60 cells of data and convert it
into a 4x15 array. Ideally, I would create the array on a separate
worksheet.

You're welcome !
Thanks for the feedback and clarification ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Glenn@stress" wrote in message
...
Thanks,

Very interesting.

I meant by "4x15 array" an array or matrix of 4 columns by 15 rows.

Rgds
Glenn@stress

How do you want to parse the data in the 60 cells? Is it the same for all
cells?

"Glenn@stress" wrote in message
...
Using Excel 2002. I need to take a row of 60 cells of data and convert it
into a 4x15 array. Ideally, I would create the array on a separate
worksheet.

Hi, I'm not sure if the OP would respond further to your query, Barb, as he
appears quite happy with the 2 earlier responses given <g,
re-thread archived at google's: http://tinyurl.com/at2g5
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Barb Reinhardt" wrote in message
...
How do you want to parse the data in the 60 cells? Is it the same for
all
cells?

"Glenn@stress" wrote in message
...
Using Excel 2002. I need to take a row of 60 cells of data and convert
it
into a 4x15 array. Ideally, I would create the array on a separate
worksheet.

Another approach if the functions in the file at
http://home.pacbell.net/beban are available to your workbook:

Array enter into the resulting 2C x 3R range

=ArrayReshape(A1:F1,3,2) to get

1 2
3 4
5 6

out of 1 2 3 4 5 6

or =ArrayReshape(A1:F1,3,2,False)

to get

1 4
2 5
3 6

Alan Beban


Glenn@stress wrote:
Thanks,

Very interesting.

I meant by "4x15 array" an array or matrix of 4 columns by 15 rows.

Rgds
Glenn@stress

"Max" wrote:


One way ..


a row of 60 cells of data

Assume it's meant that source data is in a column
in say, A1:A60 in Sheet1

In Sheet2
--------
To extract into a 15R x 4C grid

Put in say, A1:
=INDEX(Sheet1!$A$1:$A$60,ROWS($A$1:A1)*4-4
+COLUMNS($A$1:A1))

Copy A1 across to D1, fill down to D15

In Sheet3
-------
To extract into a 4R x 15C grid

Put in say, A1:
=INDEX(Sheet1!$A$1:$A$60,ROWS($A$1:A1)*15-15
+COLUMNS($A$1:A1))

Copy A1 across to O1, fill down to O4

The above 2 options should cover <g
what is meant by:

.. a 4x15 array

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Glenn@stress" wrote in message
...

Using Excel 2002. I need to take a row of 60 cells of data and convert it
into a 4x15 array. Ideally, I would create the array on a separate

worksheet.