Trying to transpose and format large matrix

[wild_fish99]

I have a large matrix (90x180). I am trying to format it into thre
columns: Row Reference; Column Reference; Value. Does anyone have an
suggestions on how to go about it. I used the ADDRESS function, bu
that only gives me the address of each cell ( in the correct order) bu
i can't seem to get the value. HELP ME PLEASE!!

 

[hgrove]

wild_fish99 wrote...

I have a large matrix (90x180). I am trying to format it into
three columns: Row Reference; Column Reference; Value. . . .

In other words, convert it into a normalized table. Don't use the ter
'transpose' for this!

If your matrix were named MAT, and the top-left cell of your resul
range were X99, try this.

X99:
=1+INT((ROW()-ROW($X$99))/COLUMNS(MAT))

Y99:
=1+MOD(ROW()-ROW($X$99),COLUMNS(MAT))

Z99:
=INDEX(MAT,X99,Y99)

Select X99:Z99, Edit > Copy, Edit > Goto, enter X100:Z16298 in the GoT
dialog, Edit > Paste

 

[Peo Sjoblom]

Since you provide so little information I can only recommend to look in help
for OFFSET,


--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[hgrove]

Peo Sjoblom wrote...

Since you provide so little information . . .

Little but sufficient

 

[aeg]

i think i have the same problem ie:

current matrix:

A B C
1 ch1 ch2
2 rh1 y n
3 rh2 n y

required table:

A B C
1 rh1 ch1 y
2 rh1 ch2 n
3 rh2 ch1 n
4 rh2 ch2 y



rh=row headings
ch=column headings

but the answer doesnt give the desired solution, or does it??

could you pls help.

 

[Myrna Larson]

but the answer doesnt give the desired solution, or does it??

You tell me. Did you try it?

 

[aeg]

yes i did. it doesnt.

You tell me. Did you try it?

 

[aeg]

also i need to generate the required table dynamically so if the matrix size
changes we can regnerate the table by just running some sort of a macro/code.
any help much appreciated.

 

[Frank Kabel]

Hi
do you have only two columns or could this be also dynamic

 

[aeg]

currently i have 20 columns and 15 rows...but tomorrow i may have 22 clms
and/or 20 rows.. so as long as i can change the code before i run it, it
would be ok.