Week by week random lists without repeats

Definition of problem: I have a main list of 30 names. I want to allocate these names into 5 groups of two or three on a weekly basis so that:

• Each of the 30 names is randomly allocated to each group once and only once
• During each week, each of the 30 names can of course only be allocated to one group

It's simple to create a single instance of 5 groups of 3 random names for example by using RAND() and picking the top 15 and assigning in order say however my issue arises from generating the groups each week with the above constraints.

1 Answer

Unless I'm missing something here, you only have something like 885 possible combinations with no repeats. So make a list of them, something on the order of:

1,2
1,3
1,4...etc. then
2,3
2,4
2,5...etc.

which is something like 450 combos. Then do the

1,2,,3
1,2,,4
1,2,,5...etc. then
2,3,,4
2,3,,5
2,3,,6...etc.

which is something like 435 combos.

Once you have the full list in a column, make another column with random numbers. Sort on the random number column to "randomize" them, quite adequately for your purpose. Then delete the random number column.

Use them in order (the random sort) with the 30 people assigned a number 1-30. However many you need in a week you just pick the next that many. Mark them when you take them with, say, an "X" in the column next to them.

If you need to make five groups one week and 20 another, you just choose the next five or 20.

When you run through them all, just resort and start over.

Add people? Add the combos they create. Easy peasy. Tack those onto the bottom of the list and resort. If there aren't many left (your definition, whatever it turns out to be), just tack them on, resort, and start over.

Unique, not duplicated until they have to be and even then, working on the "new" batch for the first full repeat. Then resort again and continue into the future.

Your description, as noted in comments, doesn't really add up to 30 people every week. Not a problem as you just take however many groups you need. This week's batch comes up to 29 or 31 people? Just go down the list for the next three or two person batch and switch the last one that needs switched (31? Take a three the randomizing made occur this wee, and pull up a two from the unused ones. Put the three into that one's slot. Everyone's happy. Works for whatever number you actually needed, perhaps it was 14 you needed and the random list gave you groups for 15. Same concept.)