6 person roster, randomised pairings with no repeats

[pcab]

Hi all, could someone please help me create a 6 person roster with randomised pairings and no repeats?

2 people (1 pair) are to be rostered on each week, and cannot be rostered on again the week after they were rostered on (no repeats).
The example below shows what I mean, the letters are the person's initials.

The 6 people involved: HN, DU, MC, SP, TM, SK

Eg/ Week 1 - HN and DU
Week 2 - MC and SP
Week 3 - TM and SK
Week 4 - DU and SP
Week 5 - SK and HN
Week 6 - MC and TM
and so on.. making sure that no one who was rostered on the week before is rostered on the following week.

Ideally I would have a spreadsheet that spits out a randomised roster, lets say 30 combinations to cover me a few months ahead.

Please let me know if this is possible.

Thank you.

[Akuini]

Hi, pcab. Welcome to the Forum.
Are you open to using VBA?

[Pete_UK]

Take a look at this thread from a few days ago:

https://www.excelforum.com/excel-pro...ml#post5580811

It's a bit more complicated than yours, in that the pairing was between Directors and Managers, and more of them, but the logic is still valid, i.e. produce a list of all combinations and then choose each pairing from that, in a manner that spreads the workload evenly.

Hope this helps.

Pete

[pcab]

Thank you, apologies for the delayed response.

I'm a beginner user, however I'm open to your suggestions if you think a beginner could use it.

[GeoffW283]

Here is another possible option. It's not too complicated I hope and the attached workbook is a working implementation. It produces a 6 week schedule that meets your constraints and in addition makes sure that everyone gets 2 matches over the 6 week period. There are a couple of constraints: (a) it assumes exactly 6 persons and (b) it produces exactly a 6 week schedule. To generate the subsequent 6 weeks you would recalculate the worksheet and manually check that your constraints are met between week-6 and week-7. If necessary I could expand this solution to produce a 12 week or an 18 week or a longer schedule if there's interest.

This is a "trial and error" approach. Six weeks worth of pairings are generated on an entirely random basis. I then check to see if the generated schedule meets the constraints of nobody playing 2 consecutive weeks and everyone getting exactly 2 matches over the 6 week period.

6_person.png

Cell A9 indicates the result of the constraints check and will show "Good" if we have a successful solution or "F9 again!" if the solution is not valid. The F9 key on your keyboard causes Excel to recalculate the worksheet and generate new random numbers. So to use this worksheet you would simply hit F9 again and again until cell A9 says "Good". Then carefully copy your roster from A1:C7 and paste-values to somewhere safe - before doing anything to cause the sheet to recalculate!

Over a dozen attempts it took me an average of 20 recalculations to reach a good solution and a maximum of 51 recalculations. To save wearing out you index finger or the F9 key I have included a simple macro (doCalc) that simply loops around "recalculate" until a good solution is found. To use this macro you will have to make sure macros are enabled on your machine.

Details:

In G2:H7:
=rand()
This simply generates a set of random humbers

In I2:I7:
=RANK.EQ(G2, G$2:G$7)
This takes the 6 randon numbers in G2:G7 and ranks them in order of size. J2:J7 works similarly.

In B2:B7:
=INDEX($E$2:$E$7,I2)
This simply looks up the initials of the person corresponding to the 1 to 6 rank in I2:I7. C2:C7 works similarly.

In A9:
=IF(AND(C2<>B2,
C3<>B3, B3<>B2, B3<>C2, C3<>B2, C3<>C2,
C4<>B4, B4<>B3, B4<>C3, C4<>B3, C4<>C3,
C5<>B5, B5<>B4, B5<>C4, C5<>B4, C5<>C4,
C6<>B6, B6<>B5, B6<>C5, C6<>B5, C6<>C5,
C7<>B7, B7<>B6, B7<>C6, C7<>B6, C7<>C6), "GOOD", "F9 again!")
This is more lengthy than it is complicated. It's simply checking each pair in turn and making sure that it meet the constraints of not playing on consecutive weeks.

Here is the VBA:

Please Login or Register  to view this content.

Attached is the workbook that implements the above.

Any questions, let us know.