Automation of task routing that I can not solve.

Jericho730

New Member
Joined
Dec 28, 2018
Messages
3
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

New Member
Joined
Dec 28, 2018
Messages
3
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?
 

Forum statistics

Threads
1,082,371
Messages
5,365,055
Members
400,819
Latest member
Gossow

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top