At the moment I have a problem with finding the right formula in Excel. The problem is this:
I have to prepare a schedule for people working together. The number of people involved differ from time to time. Everyday two people have to work together and I want to make sure that every time they work with another colleague. That is the easy part. But, also, I have to make sure that when I make the schedule, that I pick people that have not worked recently. Sometimes this is hard because I run out of combinations, but whenever possible I have to make sure this rule applies.
Normally I did this by hand, but since the number of people involved is increasing, it becomes harder and harder. Is there a way to do this more quickly in Excel?
What I used to do is this: For example, lets say that 5 people are involved. That means the following combinations are possible:
1 – 2
1 – 3
1 – 4
1 – 5
2 – 3
2 – 4
2 – 5
3 – 4
3 – 5
4 – 5
If I just followed this list, person 1 needs to work 4 days in a row. So I apply the other criteria (if possible making sure that people haven’t worked recently). The following list comes out (manually):
1 – 2
3 – 4
1 – 5
2 – 3
4 – 5
1 – 3
2 – 4
3 – 5
1 – 4
2 – 5
For every day I manually pick the person that hasn’t worked for as long as possible and at the same time make sure that the person who he/she has to work with also hasn’t worked recently (whenever possible).
The problem is that I now have to do this trick for a lot more people (up to 24). And it is much harder to do this by hand (for me at least.). The problem is also that I don’t seem to be able to translate this problem in some sort of mathematical formula.
Does anybody know of a way to automate this in Excel?
Thanks very much,
Ebo
I have to prepare a schedule for people working together. The number of people involved differ from time to time. Everyday two people have to work together and I want to make sure that every time they work with another colleague. That is the easy part. But, also, I have to make sure that when I make the schedule, that I pick people that have not worked recently. Sometimes this is hard because I run out of combinations, but whenever possible I have to make sure this rule applies.
Normally I did this by hand, but since the number of people involved is increasing, it becomes harder and harder. Is there a way to do this more quickly in Excel?
What I used to do is this: For example, lets say that 5 people are involved. That means the following combinations are possible:
1 – 2
1 – 3
1 – 4
1 – 5
2 – 3
2 – 4
2 – 5
3 – 4
3 – 5
4 – 5
If I just followed this list, person 1 needs to work 4 days in a row. So I apply the other criteria (if possible making sure that people haven’t worked recently). The following list comes out (manually):
1 – 2
3 – 4
1 – 5
2 – 3
4 – 5
1 – 3
2 – 4
3 – 5
1 – 4
2 – 5
For every day I manually pick the person that hasn’t worked for as long as possible and at the same time make sure that the person who he/she has to work with also hasn’t worked recently (whenever possible).
The problem is that I now have to do this trick for a lot more people (up to 24). And it is much harder to do this by hand (for me at least.). The problem is also that I don’t seem to be able to translate this problem in some sort of mathematical formula.
Does anybody know of a way to automate this in Excel?
Thanks very much,
Ebo