Just some thoughts .. Try this play
In Sheet1
-------------
This set-up is aligned along the lines of yours as posted
Put in A1: Coup1 (Coup1 = Couple1)
Copy down to A16
(this fills in: Coup2, ... Coup16)
Select A1:A16
Copy paste to duplicate into A17:A32
Put in V1: =RAND()
Copy across to AC1, fill down to AC12
Copy Paste into N1:Q12, the "base" table below
Coup5 Coup9 Coup13 Coup1
Coup6 Coup10 Coup14 Coup2
Coup7 Coup11 Coup15 Coup3
Coup8 Coup12 Coup16 Coup4
Coup9 Coup13 Coup1 Coup5
Coup10 Coup14 Coup2 Coup6
Coup11 Coup15 Coup3 Coup7
Coup12 Coup16 Coup4 Coup8
Coup13 Coup1 Coup5 Coup9
Coup14 Coup2 Coup6 Coup10
Coup15 Coup3 Coup7 Coup11
Coup16 Coup4 Coup8 Coup12
Duplicate the "base" table above into R1:U12
Put in F1:
=INDEX(N$1:N$12,MATCH(SMALL(V:V,ROW(A1)),V:V,0))
Copy down to F12, fill across to M12
Cols F to M will generate a non-repeating, randomized shuffle
of the 12 couples per col listed in the 2 "base" tables in cols N to U
Now to map the "12 couples" randomized sets in cols F to M
into cols B to D, with each set of 12 fitted exactly with
each set of 4 host couples in A1:A4, A5:A8, .. A29:A32
(in the ratio of 1 host couple : 3 guest couples)
Put in B1:
=OFFSET(INDIRECT(CHAR(INT((ROW(A1)+3)/4)+69)&"1"),(MOD(ROW(A1)-1,4)+1)*3-3+C
OLUMN(A1)-1,)
Copy across to D1, fill down to D32
What's in F1:F12 will map into B1:D4 to give a randomized match
of guest couples # 5 to 16 with host couples # 1 to 4,
in the zig-zag sequence within each set of 12:
1-2-3
4-5-6
7-8-9
10-11-12
Similarly what's in G1:G12 will map into B5:D8, and so on
The above completes the set-up in Sheet1 to randomize and
"fit" the guest couples with the host couples over the 8 month season
(Table in cols A to D, in A1:D32)
(Just tap/press F9 to recalc/regenerate)
In Sheet2
-------------
We'll use this sheet to set-up for us to monitor
the results of the randomization in Sheet1
to achieve the objectives posted
Put in A2: Coup1
Copy down to A17
Put in B1: Coup1
Copy across to Q1
Put in B2:
=COUNTIF(Sheet1!$B1:$D1,B$1)+COUNTIF(Sheet1!$B17:$ D17,B$1)
Copy across to Q2, fill down to Q17
For a cleaner view,
suppress extraneous zeros from showing in this sheet via:
Tools Options View tab Uncheck "Zero values" OK
In B2:Q17 will be the counts of the couples matched
with each other from Sheet1's randomized table in cols A to D
Meaning of the counts in B2:Q17:
"1" = matched once, "2" = matched twice, blank** = unmatched
within the 8 month season
**excludes of course, the identical: Coupx to Coupx intersection cells
To align with the objectives stated in the post,
the randomization in Sheet1's cols A to D
should achieve as high a percentage of "1"s as possible,
as "2"s would mean duplication of "matched" couples
within the season, either as hosts or as guests
Put in R1: =COUNTIF($B$2:$Q$17,COLUMN(A1))
Copy across to S2
R1 gives the total of all the "1"s,
S2 gives the total of all the "2"s
Put in T1: =R1/(R1+S1*2)
Format T1 as percentage / bold
T1 gives the percentage of "1"s over "2"s
Now to play:
Tap/Press F9 to recalc/regenerate, watch the % figure in T1
(It'll fluctuate up and down)
Stop pressing F9 when you're sufficiently happy <g
with the % figure in T1 (the higher, the better)
Then just go over and copy Sheet1's cols A to D (A1:D32),
and freeze the results elsewhere with
a paste special values OK
--
If you'd like to have a sample book with the above set-up,
just post a "readable" email address in response here
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bob" wrote in message
...
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