Trouble in Excel

[phil_livesey]

Hi everyone.

My problem is that i have a list of 27 people who need to work every weekend. I want 7 of these people to work one saturday, then one of those 7 people to work the sunday.
I have achieved this but my problem occurs when i want to do the next saturday, i don't want anyone that has already appeared in either the saturday or the sunday within the past 5 weeks to be chosen again. And i want this to continue on infinitely.

Thanks in advance.

 

[USAFRet]

If I'm reading this right, you don't have enough resources.

You need at least 35 people to go 5 weekends with no repeats.

 

[phil_livesey]

just as many weeks as i can before i have to use people again. not specifically 5 weeks.

 

[USAFRet]

With 27 people, on the 4th Saturday, 1 person will start to repeat.

Make a list of all 27. Take the first 7, plug them in on Saturday. Take the first of that 7, and plug them in on Sunday.
The next week, take the next 7. The first from that group works on Sunday.
Repeat for the next 2 weekends.

On the 4th Saturday, the 7th person will be one from the 1st Saturday.

 

[phil_livesey]

No you don't understand, i'm not doing it manually, i want it done automatically. There are a few reasons i can't do this, so i just want to know of a formula that can help.

 

[USAFRet]

Is this an actual work schedule? Or a class assignment?

If it's a real work schedule, it's faster and easier to just write the first 4 weeks manually, and then copy/paste indefinitely.
If it's a programming assignment, read the sticky at the top.

 

[phil_livesey]

I said I want it done automatically, I may want it to change and I don't want to write it out every time. I don't understand why you can't just answer my question, and no it's not for a class assignment, if it was I would of said

 

[USAFRet]

There is no magic formula for your specific requirements. Some things are just not worth writing a bunch of code for.

Could it be done in VBA within Excel? Sure. But that's a non-trivial bit of code. Not 'hard', just time consuming.
Adding that it might 'change' increases the complexity a lot.

Writing the 4 week schedule once, then copy/paste for however far out you want, would be very, very easy. It would take far longer to write the code to do this than to just write it out manually.
Again, you only have to do that once. If Mary can't do it on Aug 10, take her out and plug in John.

You could probably schedule out a whole year in 10 minutes.