Randomizer with conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-2699941%22%20slang%3D%22en-US%22%3ERandomizer%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2699941%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20having%20an%20issue%20generating%20an%20assignment%20randomizer%20on%20Excel.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnly%20certain%20people%20are%20trained%20on%20a%20certain%20assignment.%20With%2011%20Assignments%20I%20want%20it%20to%20be%20able%20to%20choose%20randomly%20from%2015%2B%20people%20without%20one%20person%20getting%202%20assignments%20and%20the%20right%20people%20who%20are%20trained%20to%20do%20the%20assignment.%20I%20have%20it%20set%20up%20with%20the%20names%20and%20assignments%20and%20I%20fill%20in%20%22approved%22%20if%20they%20are%20trained.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20following%20script%20is%20what%20I%20have%20so%20far.%20Unfortunately%2C%20I%20have%20the%20same%20name%20get%202%20assignments%20and%20I%20do%20not%20know%20how%20to%20prevent%20this.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(%24A%241%3A%24A%2430%2CINDEX(LARGE((B1%3AB30%3D%22Approved%22)*ROW(%24A%241%3A%24A%2430)%2CROW(%24A%241%3A%24A%2430))%2C1%2BINT(RAND()*COUNTIF(B1%3AB30%2C%22Approved%22))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2699941%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2703396%22%20slang%3D%22en-US%22%3ERe%3A%20Randomizer%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2703396%22%20slang%3D%22en-US%22%3EWhat%20about%20you%20add%20the%20UNIQUE()%20formula%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2704758%22%20slang%3D%22en-US%22%3ERe%3A%20Randomizer%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2704758%22%20slang%3D%22en-US%22%3EI%20cannot%20seem%20to%20get%20it%20to%20work%20with%20the%20UNIQUE%20Formula%3C%2FLINGO-BODY%3E
New 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