Coverting array data into single column
 

I have data in form of array (table). For example:
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15

i want to convert this data into sigle column as below:
1
6
11
2
7
12
..
..
5
10
15

Can anyone suggest me the way to do this?

Hi
on your second sheet in A1 enter
=OFFSET('sheet1'!$A$1,INT((ROW()-1)/5),MOD(ROW()-1,5)
and copy this down

--
Regards
Frank Kabel
Frankfurt, Germany

dgarg wrote:
I have data in form of array (table). For example:
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15

i want to convert this data into sigle column as below:
1
6
11
2
7
12
.
.
5
10
15

Can anyone suggest me the way to do this?

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook:

=ArrayReshape(A1:E3,15,1,"c") array entered into a 15-cell column.

Alan Beban

dgarg wrote:
I have data in form of array (table). For example:
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15

i want to convert this data into sigle column as below:
1
6
11
2
7
12
.
.
5
10
15

Can anyone suggest me the way to do this?

Frank Kabel wrote...
on your second sheet in A1 enter
=OFFSET('sheet1'!$A$1,INT((ROW()-1)/5),MOD(ROW()-1,5)
and copy this down

First, your formula is missing a final right parenthesis. Untested?

Secong, you do need to start reading what OPs write more carefully. OP wants
to interate by row then by column, not by column then by row. Your formula
above would need to change to

=OFFSET('sheet1'!$A$1,MOD(ROW()-1,3),INT((ROW()-1)/3))

Third, positionally dependent. As an alternative, the following returns an
array of numbers that could be used as a component in longer formulas. I'm
using x to denote the source range.

=N(OFFSET(x,MOD(ROW(INDIRECT("1:"&(ROWS(x)*COLUMNS (x))))-1,ROWS(x)),
INT((ROW(INDIRECT("1:"&(ROWS(x)*COLUMNS(x))))-1)/ROWS(x)),1,1))

For that matter, if the result were to be entered into a single column,
multiple row range of cells, a slightly shorter array formula could be used.

=INDEX(x,MOD(ROW(INDIRECT("1:"&(ROWS(x)*COLUMNS(x) )))-1,ROWS(x))+1,
INT((ROW(INDIRECT("1:"&(ROWS(x)*COLUMNS(x))))-1)/ROWS(x))+1)

Frank Kabel wrote...
on your second sheet in A1 enter
=OFFSET('sheet1'!$A$1,INT((ROW()-1)/5),MOD(ROW()-1,5)
and copy this down

First, your formula is missing a final right parenthesis. Untested?
No but just not able to copy and paste correctly on my side :-)


Secong, you do need to start reading what OPs write more carefully.
OP wants to interate by row then by column, not by column then by
row. Your formula above would need to change to

=OFFSET('sheet1'!$A$1,MOD(ROW()-1,3),INT((ROW()-1)/3))
Thanks for the correction


Third, positionally dependent. As an alternative, the following
returns an array of numbers that could be used as a component in
longer formulas. I'm using x to denote the source range.

=N(OFFSET(x,MOD(ROW(INDIRECT("1:"&(ROWS(x)*COLUMNS (x))))-1,ROWS(x)),
INT((ROW(INDIRECT("1:"&(ROWS(x)*COLUMNS(x))))-1)/ROWS(x)),1,1))

For that matter, if the result were to be entered into a single
column, multiple row range of cells, a slightly shorter array formula
could be used.

=INDEX(x,MOD(ROW(INDIRECT("1:"&(ROWS(x)*COLUMNS(x) )))-1,ROWS(x))+1,
INT((ROW(INDIRECT("1:"&(ROWS(x)*COLUMNS(x))))-1)/ROWS(x))+1)

Both formulas are quite nice
Regards
Frank