Randomly assign a grade based on an exact uniform distribution and size (excel/VBA)

[vadim21]

I have a 3x3 migration matrix and I would like, based on the transition rates, to randomly choose which items to move from a state to another and keep the same distribution, for instance if i have this array:


Pupil Grade Grade N+1
1 A A
2 A A
3 B A
4 B B
5 A B
6 A C
7 A A
8 C C
9 A A
10 A A
11 A A
12 C C
13 A A
14 A A
15 A A
16 C C
17 A A
18 A A
19 C C
20 A A


and this transition matrix:


FROM/TO
A B C
A 0.8 0.1 0.1
B 0.5 0.5 0
C 0 0 1


My question is: is it possible to randomly distribute the grades n+1 such as to keep the same transition rates and number of migrations but assign them to different pupils? Like this (notice certain pupils now transitioned differently although the transition frequencies overall is the same):


Pupil Grade Grade N+1
1 A B
2 A A
3 B B
4 B A
5 A A
6 A A
7 A C
8 C C
9 A A
10 A A
11 A A
12 C C
13 A A
14 A A
15 A A
16 C C
17 A A
18 A A
19 C C
20 A A

Thanks a lot for your answer!

Vadim.