Automation of task routing that I can not solve.

[Jericho730]

https://photos.app.goo.gl/JiRjueNTERUmu2ev6

I'm trying to auto populate the area in yellow in the screen shot (see link) with the tasks.

Each week a person does a task, the next week they move on to a different task. They never repeat the same task in a 4 week period. Its always a 4 week period and there is always at least 4 tasks.

The other objective would be to make the crew on each task different, so that you don't work with the same person all 4 weeks. You can work with the same person 2-3 weeks as needed but not 4. So I'm trying to keep anyone form having the exact same schedule.

Variables include the number of tasks, the number of people, and the number of people needed to do each task.

Limits and parameter range:
Always 4 weeks
People: 4 minimum, There is no maximum. The average is around 30 but I would like the excel sheet to work with over 100.
The # of people doing each task during a week can range from 1 minimum - 8 maximum. 5-6 is the sweet spot. We add tasks to accommodate the people to try and achieve 5-6 people doing each task. Each task needs a balanced number of people I cant have 8 people doing one task and 1 person alone on a task. Ideally we would have the exact same amount of people doing each task however the math doesn't always work out.

I have the excel sort of working to the point that I can get a random, balanced order for each week, but I cant figure out how to keep the task from repeating for the same person form week to week. As you can see "Name 1" repeats "Task 5" however "Name 6" has unique task week to week.

The pink column in the screen shot is just what I was using to double check the repeats, I don't need it. I input values in the red cells.

I'm currently using a non repeating random number to assign tasks for each week based on my total task list. I just don't know how to get it to not repeat from week to week.​

Thank you in advance!

​

 

[Jericho730]

Another way to think about this is that I have a deck of cards to chose from.

Round 1: Fresh full deck of cards.
Person A: draws a 2 as their first card, (deck depletes by the selection)
Person B: draws a 3 as their first card (deck depletes by the selection)
Person C: draws a 2 as their first card (deck depletes by the selection)

Continues for however many people

Round 2: Fresh deck of cards but with the following stipulations on each draw
Person A: All 2’s are removed so they cant draw 2 again. Draws a 4 as their second card (deck depletes by the selection)
Person B: The 2’s are put back in, and all 3’s are removed so they cant draw 3 again. Draws a 2 as their second card (deck depletes by the selection)
Person C: The 3’s are put back in, and all 2’s are removed so they cant draw 2 again. Draws a 5 as their second card (deck depletes by the selection)

Continues for however many people

Round 3: Fresh deck of cards but with the following stipulations on each draw
Person A: All 2’s and 4’s are removed. Draws a 5 as their third card (deck depletes by the selection)
Person B: The 2’s and 4’s are put back in, and all 3’s and 2’s are removed. Draws a 4 as their third card (deck depletes by the selection)
Person C: The 3’s and 2’s are put back in, and all 2’s and 5’s are removed. Draws a 4 as their second card (deck depletes by the selection)

Continues for however many people

This could go on for whatever the parameters are in terms of rounds and card count.

So you end up with

Person A – 2,4,5
Person B – 3,2,4
Person C – 2,5,4

Now how do I automate this with adjustable parameters?

Thoughts?

 

[Jericho730]

Anyone???