PDA

View Full Version : Rotating Tasks



koala
08-13-2015, 02:40 AM
Hi Forum,

This is not a VBA problem, but maybe some sort of VBA looping and analysing marco will help solve it.

I have a weekly roster, with 6 tasks and 2 worker on each task (ie 12 workers) I have included my spreadsheet to show how it works.

I want the tasks to be set in such a way that each week every worker works with a different worker than previously worked with.

The tasks remain static in the roster location, but each week the workers move down one task

By the time they rotate through the entire roster (12 weeks) they will have worked with every other worker once. (they will always need to work with one worker twice because they cant work with themselves.)

I have achieved this with my old 10 worker roster (5 tasks) but am not able to work it out for the 12 worker (6 tasks) roster (the 10 worker roster is included to show how the result needs to be.)

The tasks can be in any order however preferably not consecutive

Is anyone able to assist?

andrew93
08-14-2015, 07:54 PM
Hi

The issue here is the spacing between tasks.

Take for instance your 5 task model, the number of weeks between tasks repeating is :
~ task A is 2 & 8
~ task B is 5 & 5
~ task C is 6 & 4
~ task D is 9 & 1
~ task E is 3 & 7

Notice everything adds to 10 - being the number of unique tasks (where A and A1 are considered unique). Notice task B repeats every 5 weeks - so this is the task on which people will "double up" with another person within the last 10 weeks. For instance, Workers 1 & 6 work on task B in week 2 and again (in reverse) in week 7.

Notice also none of the other numbers repeat - we have 1 through 9 with 5 in the list twice. This is why your 5 task model works.

If we look at your 6 task model, the number of weeks between tasks repeating is :
~ task A is 11 & 1
~ task B is 8 & 4
~ task C is 6 & 6
~ task D is 3 & 9
~ task E is 3 & 9
~ task F is 5 & 7

You can see the issue here is that 3, 6 and 9 are represented more than once, which means you will have a number of double ups.

The way to solve this is to find a sequence of tasks where the gaps between the weeks between tasks repeating number from 1 to 11, with 6 repeating once. Unfortunately I think there are 479m permutations of the 12 tasks which will take a while to crunch, but I'm not sure if there is a solution.

I trust this helps.
Andrew