Random names without duplicates and multiple criteria

**finch2o** · 07-18-2016, 07:01 PM

Hello everyone,

I'm trying to create a list of random names based on different job functions that people are trained on. So far I can do each job function separately using

=INDEX($B$15:$B$44,LARGE(IF($D$15:$D$44=$G$14,ROW($D$15:$D$44)-ROW($D$15)+1),INT(RAND()*COUNTIF($D$15:$D$44,$G$14)+1)))

but I'm having trouble when it comes to combining all of the lists. Some people are trained in multiple functions and others only in one but I don't want those that are trained in multiple functions to show up on multiple lists. Is there a way to have excel excluded a persons same in a new random list if they are already showing up in another? I'm not sure if I'm explaining this correctly but any advise would be much appreciated.

**Kaper** · 07-19-2016, 02:52 AM

Hi,
Not seeing your data and layout I could just suggest moving RAND() to separate cell, in a formula refer to this cell and include additional COUNTIF for already generated job function(s).
On the other hand, seems that solution with VBA (either stand alone macro or UDF) could be better and more flexible solution.

**finch2o** · 07-19-2016, 11:31 AM

Thanks for the input Kaper. I have attached what I hope will help understand what it is I'm trying to accomplish. I've just manually inputted the outputs that I'm seeing. I've somewhat solved the repeating issue within the same job function, now it's just a matter of making sure that the same employee isn't assigned to multiple job functions. I'm also curious if there is a way to limit the number of outputs, for instance if job W needs 5 people today but only 3 tomorrow.

Job Functions What I'm getting now
W X Y Z random selection for Job W random selection for job X random selection for job Y Random selection for job Z
Employee A B A D
A yes yes B
B yes yes yes yes
C yes yes
D yes yes
E yes yes random selection for Job W random selection for job X random selection for job Y Random selection for job Z
A C D E
B