Trying to make a few different schedules, fairly allocated

[John7861]

Hello,
I'm trying to figure out how to make a few different schedules from the same group of people. There's a few moving parts and I'm not sure what the best way to structure this is. Any advice on best functions to use, or how to do it would be amazing.

Given a list of people that are broken down into 4 groups, male/female and adult, not adult, I'm trying to find a way to populate a schedule for 3 different activities, and each requires a different composition.

For example,
activity 1 requires 1 adult male, 1 adult female and a non adult male.
activity 2 requires 2 adult females and 1 non adult female.
activity 3 requires 1 adult female.

These all happen approximately twice a week, and ideally I'm counting how often each person has been chosen over the course of a year so that if changes are made it's still fairly allocating people.

I have some understanding how I could do this for a single schedule, using a rand function to create groups, but trying to deal with the different compositions in different places has me unsure how to proceed.

Thanks in advance!

 

[John7861]

A few things I've been trying (not sure if they're good ideas):

I could possibly assign each type of person a value and have a requirement that each activity add up to a value, but I have no idea how to implement that requirement.

Alternatively, I've been trying to pair down my master list into 4 lists (so far failing to make an if + vlookup work) and then from those 4 lists use a choose(randbetween() from each separate column.