Prev: How to look up a value?
Next: Variable Sumproduct Range
From: Dave on 13 May 2010 08:43
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
From: Roger Govier on 13 May 2010 10:41
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 wizard>Multiple consolidation
ranges>Next>Create a single Page field>Next>Select the range of your
data>Add>Next>Select where you want the Output>Finish
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
From: Bernd P on 13 May 2010 11:15
On 13 Mai, 13:43, Dave <dave...(a)gmail.com> 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
From: Glenn on 13 May 2010 12:46
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.
 | 
Pages: 1
Prev: How to look up a value?
Next: Variable Sumproduct Range