transpose to matrix
 

I have a list of items that I want to transpose to a matrix. Scatter Chart won't quite work, particularly because it plots on points and it doesn't list the related item - I want the references to be inside a block or a cell. It seems like there should be a function to group two attributes together, and maybe I just don't know the proper name

From this
EffectA Effect
#1 Low Me
#2 High Hig
#3 Med Me
#4 Low Hig
#5 High Hig
#6 Med Lo

To this
A-Low A-Med A-Hig
B-High #4 -- #2 #
B-Med #1 #3 -
B-Low -- #6 -

Any ideas? Thanks

transpose to matrix
 

Can you treat those values in Column A (#1 - #6) as numeric?

If yes, then you could use a pivot table.

First, give column A a nice header (I used qty)

Then select your range.
data|pivottable
follow the wizard until you get to a dialog with a Layout button on it.
click that layout button.

Drag the effect-B "button" to the row area
drag the effect-a button to the column area
drag the Qty (or whatever you used) to the data area
If it says anything but Sum of qty, then double click on it and choose sum.

Click finish.

Now drag the effect-B titles (High, med, low in column A to the correct order
you want)
(same with effect-A titles if you need to).

I got this.

Sum of qty EffectA
EffectB Low Med High Grand Total
High 4 7 11
Med 1 3 4
Low 6 6
Grand Total 5 9 7 21

If that data in column A cannot be treated as numeric, then this won't work.
Pivottables summarize numbers.


bill wrote:

I have a list of items that I want to transpose to a matrix. Scatter Chart won't quite work, particularly because it plots on points and it doesn't list the related item - I want the references to be inside a block or a cell. It seems like there should be a function to group two attributes together, and maybe I just don't know the proper name.

From this:
EffectA EffectB
#1 Low Med
#2 High High
#3 Med Med
#4 Low High
#5 High High
#6 Med Low

To this:
A-Low A-Med A-High
B-High #4 -- #2 #5
B-Med #1 #3 --
B-Low -- #6 --

Any ideas? Thanks.

--

Dave Peterson

transpose to matrix
 

And by treat as numeric, I really mean get rid of those # signs and really enter
numbers.

Dave Peterson wrote:

Can you treat those values in Column A (#1 - #6) as numeric?

If yes, then you could use a pivot table.

First, give column A a nice header (I used qty)

Then select your range.
data|pivottable
follow the wizard until you get to a dialog with a Layout button on it.
click that layout button.

Drag the effect-B "button" to the row area
drag the effect-a button to the column area
drag the Qty (or whatever you used) to the data area
If it says anything but Sum of qty, then double click on it and choose sum.

Click finish.

Now drag the effect-B titles (High, med, low in column A to the correct order
you want)
(same with effect-A titles if you need to).

I got this.

Sum of qty EffectA
EffectB Low Med High Grand Total
High 4 7 11
Med 1 3 4
Low 6 6
Grand Total 5 9 7 21

If that data in column A cannot be treated as numeric, then this won't work.
Pivottables summarize numbers.

bill wrote:

I have a list of items that I want to transpose to a matrix. Scatter Chart won't quite work, particularly because it plots on points and it doesn't list the related item - I want the references to be inside a block or a cell. It seems like there should be a function to group two attributes together, and maybe I just don't know the proper name.

From this:
EffectA EffectB
#1 Low Med
#2 High High
#3 Med Med
#4 Low High
#5 High High
#6 Med Low

To this:
A-Low A-Med A-High
B-High #4 -- #2 #5
B-Med #1 #3 --
B-Low -- #6 --

Any ideas? Thanks.

--

Dave Peterson


--

Dave Peterson

transpose to matrix
 

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

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

transpose to matrix
 

Terrific!! This had me stumped for weeks. Thank you very much!!