I am trying to create a worksheet that can be used to randomly schedule host
and guest couples for a dinner club. There are 16 couples (nos. 1 thru 16),
and each month there are four separate dinners consisting of a host couple
and three guest couples. E.g., in month one couple 1 would host couples 5,
6 & 7; couple 2 would host couples 8, 9 & 10, and so on. This goes on for
eight months, so each couple ends up being a host two times (couples 1-4
host for months 1 and 5, couples 5-8 host for months 2 and 6, couples 9-12
host for months 3 and 7, and couples 13-16 host for months 4 and 8). Two
main objectives with the scheduling are (1) to avoid having the same couples
be guests at the same host during the year and (2) to mix couples up as much
as possible throughout the eight month season.

My attempt so far consists of a schedule which includes 4 columns and 32
rows. Column 1 contains the host couple and this just consists of the list
of 16 couples (1 - 16) two times. The first 4 rows (couples 1 - 4) take
care of the first month, the next four rows (couples 5 - 8) represent the
2nd month and so on. I then use columns 2, 3 and 4 for each month to spread
the remaining 12 couples among the four hosts. In another part of the
worksheet I list the remaining available 12 couples for each month and then
use the Rand() function (in a column next to the list of 12 available
couples) to come up with a random sort order to place these 12 available
couples among the three hosts for each month. This works fairly well but I
still do not get the level of mixing couples up as much as I would like. One
couple may end up with one or two other couples 4 or 5 times during the year
and possibly not at all with one or two others.

Does anyone have a suggestion on how to better accomplish this? Please let
me know if you need more information. Thanks in advance for any help.

Bob