Random assignment of teams with certain requirements

[czulaica]

Hi all,

I was wondering what would be the easiest way to solve this problem:

*I have a team of 35 people that I want to divide into groups for 21 events. That is, repetition is possible, because all people will participate in different events.

*Each member of the team has a name, gender, skill level/type (A, M, B), and has a maximum number of participations (that is, that the person may be assigned to teams in different events this max amount of times).

*However, I must do so under certain conditions. For each event there is a required number of participants (say 10 or 12); a fixed number of male participants; a fixed number of female participants; a fixed (or max/min, I'm flexible there) number of people required with certain skills (A, M, B); and a max number of people that we can take from each department.

What's the best to generate the distribution of teams taking into consideration all these conditions?

Your suggestions would be amazing!

Here's my file with the list of possible team members and requirements per event:

Download here

Thanks much
C

[MarvinP]

Hi C and welcome to the forum,

I'd sort the People using a column of =Rand() numbers. Then check the criteria for this selection. If it worked, I'd copy those people using "Values Only" to another place. Then do another random selection (press F9). Do the above until you have done all 21 events.