ExcelBanter - How to build a rotating schedule

Hi!

I'm looking for some help. I have 10 employees who share 40 job duties. Currently each employee does 4 per week and rotate to the next duties. So by the time the person doing job duty #1 gets back to #1 it's 10 weeks later and they often forget that duty or new employees severely struggle.

As a result I am trying to redesign how they do their work. I want to pair them and make "buddy groups" and so that would make 5 job rotations with 8 job duties each. I don't want the duties to rotate (they will stay as fixed job duty groups, #1, #2, #3, #4 and #5). However after every complete cycle, I want the "buddies" to rotate.

And I want to build it all in Excel. I have never built a schedule like this in Excel so don't really know how to start even. Ideally I'd like to see it come out as an annual spreadsheet so people can see in advance what they will be assigned and with who.

Any ideas? Can anyone help me get this created?

Many thanks!

Jeff