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

[Guest]

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.

 

[Biff]

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

 

[Max]

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

.. a 4x15 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.

 

[Guest]

Thanks,

Very interesting.

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

Rgds
Glenn@stress

 

[Max]

You're welcome !
Thanks for the feedback and clarification ..

 

[Barb Reinhardt]

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

 

[Max]

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

--

 

[Alan Beban]

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