Hello Excelusers,
for work I have to create a transition matrix - I was hoping you could help a rat-race participant's life a little bit easier. Problem / situation is detailed below. Preferred method of solving this problem would be using the Pivot Table function in Excel 2007.
Thank you in advance.
Here is the transition matrix:
X axis = Old Group
Y axis = New Group
X = no change in Groups
.. 1 2 3 4 5 6 7
1 X - - - - - -
2 - X - - - - -
3 - - X - - - -
4 - - - X - - -
5 - - - - X - -
6 - - - - - X -
7 - - - - - - X
Here is the data I need to plot on the transition matrix:
....................... TOM .......... JIM ......... SUE
Old Group: ........ 7 ............ 6 ............ 4
New Group: ...... 5 ............ 4 ............ 5
Any idea on how to get excel to automatically fill in the matrixes based on a table with iformation on Tom, Jim, Sally and Sue?
In the end the transition matrix should look like this: (T = Tom, J = Jim and S = Sue)
.. 1 2 3 4 5 6 7
1 X - - - - - -
2 - X - - - - -
3 - - X - - - -
4 - - - X - J -
5 - - - S X - T
6 - - - - - X -
7 - - - - - - X
Bookmarks