Hi to everyone, is it possible to view a matrix in table mode?

I have a matrix like this
b c d e f ...
b 1 0 3 4 5
c 2 5 8 6 5
d 2 3 6 8
e
f
....

I want to convert it in a 3 row table:
ROW COL VALUE
b b 1
b c 0
b d 3
b e 4
....



In one word, that's the opposite of what the pivot table does! :S

thanks!

Dave

Hi

Then use a Pivot Table to achieve it.
You will have to insert an entry in cell A1, as the PT will complain
there is no header value for the column.

Alt+D+P to invoke the PT wizardMultiple consolidation
rangesNextCreate a single Page fieldNextSelect the range of your
dataAddNextSelect where you want the OutputFinish
Double click on the bottom right value, Grand Total by Row and Column,
and a new sheet will open with the data the way you want it.
--
Regards
Roger Govier

Dave wrote:
Hi to everyone, is it possible to view a matrix in table mode?

I have a matrix like this
b c d e f ...
b 1 0 3 4 5
c 2 5 8 6 5
d 2 3 6 8
e
f
...

I want to convert it in a 3 row table:
ROW COL VALUE
b b 1
b c 0
b d 3
b e 4
...



In one word, that's the opposite of what the pivot table does! :S

thanks!

Dave

On 13 Mai, 13:43, Dave wrote:
Hi to everyone, is it possible to view a matrix in table mode?

I have a matrix like this
* * *b * c * d * e * f * ...
b * 1 * 0 * 3 * 4 * 5
c * 2 * 5 * 8 * 6 * 5
d * 2 * 3 * 6 * 8
e
f
...

I want to convert it in a 3 row table:
ROW * *COL * *VALUE
b * * * * *b * * * * 1
b * * * * *c * * * * 0
b * * * * *d * * * * 3
b * * * * *e * * * * 4
...

In one word, that's the opposite of what the pivot table does! :S

thanks!

Dave

Hello Dave,

If you are willing to use a UDF:
http://sulprobil.com/html/reshape.html

The example on my page is just the other way round but can easily
apply the way you want to.

Regards,
Bernd

Dave wrote:
Hi to everyone, is it possible to view a matrix in table mode?

I have a matrix like this
b c d e f ...
b 1 0 3 4 5
c 2 5 8 6 5
d 2 3 6 8
e
f
...

I want to convert it in a 3 row table:
ROW COL VALUE
b b 1
b c 0
b d 3
b e 4
...



In one word, that's the opposite of what the pivot table does! :S

thanks!

Dave



Name your current table "Source". Enter the following three formulas where you
want the new table:

=INDEX(Source,INT((ROW(A1)-1)/(COLUMNS(Source)-1))+2,1)

=INDEX(Source,1,MOD(ROW(A1)-1,COLUMNS(Source)-1)+2)

=INDEX(Source,INT((ROW(A1)-1)/(COLUMNS(Source)-1))+2,
MOD(ROW(A1)-1,COLUMNS(Source)-1)+2)



Copy down as far as necessary.