View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson[_3_] Dave Peterson[_3_] is offline
external usenet poster
 
Posts: 2,824
Default transpose to matrix

But you're only using 9 combinations (A & B, High to Low)???

How about a worksheet formula suggestion:

I put your data in A1:C7

then in A10:A12, I put B's. (my header for the row)
In B10:B12, I put: High, Med, Low

In C8:E8, I put A's (my header for the column)
in C9:E9, I put Low, Med, High

then in C10, I put this array formula:
=INDEX($A$1:$A$7,MATCH(C$9&"--"&$B10,$B$1:$B$7&"--"&$C$1:$C$7,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

And then I dragged it across from C10 to E10
and then I dragged down from C10:E10 to C12:E12

And I got this:

a a a
low med high
b high #4 #N/A #2
b med #1 #3 #N/A
b low #N/A #6 #N/A

If I really cared about getting rid of the n/a's, I could modify my formula:

=IF(ISERROR(MATCH(C$9&"--"&$B10,$B$1:$B$7&"--"&$C$1:$C$7,0)),"--",
INDEX($A$1:$A$7,MATCH(C$9&"--"&$B10,$B$1:$B$7&"--"&$C$1:$C$7,0)))
(all one cell and still an array formula).

This gave me:

a a a
low med high
b high #4 -- #2
b med #1 #3 --
b low -- #6 --


And that's pretty close.




bill wrote:

Unfortuantely, column A is not numeric - they're sequential, representing a line item number - could be anything text. Thanks.


--

Dave Peterson