Randomizer with conditions

Copper Contributor

SUBJECT: RANDOMIZER

I am having an issue generating an assignment randomizer on Excel. 

 

Only certain people are trained on a certain assignment. With 11 Assignments I want it to be able to choose randomly from 15+ people without one person getting 2 assignments and the right people who are trained to do the assignment. I have it set up with the names and assignments and I fill in "approved" if they are trained. 

 

The following script is what I have so far. Unfortunately, I have the same name get 2 assignments and I do not know how to prevent this. 

 

=INDEX($A$1:$A$30,INDEX(LARGE((B1:B30="Approved")*ROW($A$1:$A$30),ROW($A$1:$A$30)),1+INT(RAND()*COUNTIF(B1:B30,"Approved"))))

2 Replies
What about you add the UNIQUE() formula
I cannot seem to get it to work with the UNIQUE Formula