from table to vector
 

I have a table that reads
a b c
d e

I would like to map it to
a
b
c
d
e
Can I do this with a function?
Thanks,
Pierre

Hi
do you have a maximum of columns and are they always filled or could
you have empty cells in your source data

--
Regards
Frank Kabel
Frankfurt, Germany

"PierreL" schrieb im Newsbeitrag
...
I have a table that reads
a b c
d e

I would like to map it to
a
b
c
d
e
Can I do this with a function?
Thanks,
Pierre

Frank, Guten Tag,
Yes I may have empty cells, but then I would sort them out in the vector
later on, so assume its is all filled in. The maximum varies, but I can
easily detect the maximum length of all the rows and fill that in the formula.
Tx,
Pierre

"Frank Kabel" wrote:

Hi
do you have a maximum of columns and are they always filled or could
you have empty cells in your source data

--
Regards
Frank Kabel
Frankfurt, Germany

"PierreL" schrieb im Newsbeitrag
...
I have a table that reads
a b c
d e

I would like to map it to
a
b
c
d
e
Can I do this with a function?
Thanks,
Pierre

PierreL wrote:
I have a table that reads
a b c
d e

I would like to map it to
a
b
c
d
e
Can I do this with a function?
Thanks,
Pierre
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

=ArrayUniques(ArrayReshape(A1:C2,6,1)), array entered into a 6 cell
column will give you
a
b
c
d
e
0

If your data were
a b blank c d
e blank f blank g blank h

then =ArrayUniques(ArrayReshape(A1:G2,14,1) array entered into a 14-cell
column would return
a
b
0
c
d
e
f
g
h
#N/A
#N/A
#N/A
#N/A
#N/A

Alan Beban

"PierreL" wrote...
I have a table that reads
a b c
d e

I would like to map it to
a
b
c
d
e
Can I do this with a function?

Another alternative if the result would appear in a range of cells. If the
source range were named Src, and the top-left cell in the destination range
named Dest, enter the following formula in Dest

=OFFSET(Src,INT((ROW()-ROW(Dest))/COLUMNS(Src)),
MOD(ROW()-ROW(Dest),COLUMNS(Src)),1,1)

and fill this cell down as far as needed.

Thanks Harlan, worked beautifully!
Alan, I will implement yours when I am back on my own machine, I did not
want to download anything on someone's else machine. 'Reshape"reminds me of
APL, p-robably as powerful.
hanks again,
Pierre

"Harlan Grove" wrote:

"PierreL" wrote...
I have a table that reads
a b c
d e

I would like to map it to
a
b
c
d
e
Can I do this with a function?

Another alternative if the result would appear in a range of cells. If the
source range were named Src, and the top-left cell in the destination range
named Dest, enter the following formula in Dest

=OFFSET(Src,INT((ROW()-ROW(Dest))/COLUMNS(Src)),
MOD(ROW()-ROW(Dest),COLUMNS(Src)),1,1)

and fill this cell down as far as needed.

"PierreL" wrote...
....
. . . 'Reshape"reminds me of APL, p-robably as powerful.

Alan's ArrayReshape doesn't function the same as APL's rho operator, but it
does function more in the spirit of how Excel's built-in facilities work.