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

G

Glenn@stress

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.
 
B

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
 
M

Max

One way ..
a row of 60 cells of data
Click to expand...

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 said:
.. a 4x15 array
Click to expand...

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
Glenn@stress said:
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
Click to expand...
worksheet.
 
G

Glenn@stress

Thanks,

Very interesting.

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

Rgds
Glenn@stress
 
B

Barb Reinhardt

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

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
 
Top