If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
convert matrices to table
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 |
#2
|
|||
|
|||
convert matrices to table
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 |
#3
|
|||
|
|||
convert matrices to table
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 |
#4
|
|||
|
|||
convert matrices to table
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. |
Thread Tools | |
Display Modes | |
|
|