Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bob
 
Posts: n/a
Default Schedule Rotation Question

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


  #2   Report Post  
Max
 
Posts: n/a
Default

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




  #3   Report Post  
Max
 
Posts: n/a
Default

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


Typos, sorry:

In the above, S2 should read as S1

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----


  #4   Report Post  
Bob
 
Posts: n/a
Default

Max,

Thank you very much for the great reply. I've just set up the sheets as you
instructed, and it all looks very promising. I don't have the time right
now to experiment with it, but I should be able to do so within the next
couple days and will then let you know for sure how it works out.

I'm pretty sure I have correctly reconstructed your worksheets, but if the
offer is still open I'll take you up on the offer to send a sample book,
which you can send to rschwarz<atnetins<dotnet. Thanks again.

Bob

"Max" wrote in message
...
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


Typos, sorry:

In the above, S2 should read as S1

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----




  #5   Report Post  
Max
 
Posts: n/a
Default

You're welcome, Bob !
Sample book sent over ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bob" wrote in message
...
Max,

Thank you very much for the great reply. I've just set up the sheets as

you
instructed, and it all looks very promising. I don't have the time right
now to experiment with it, but I should be able to do so within the next
couple days and will then let you know for sure how it works out.

I'm pretty sure I have correctly reconstructed your worksheets, but if the
offer is still open I'll take you up on the offer to send a sample book,
which you can send to rschwarz<atnetins<dotnet. Thanks again.

Bob



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP question KG Excel Discussion (Misc queries) 9 May 21st 05 07:12 PM
Formating Dates for production schedule dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 09:43 PM
I would like to make a schedule for my dept. Who works what days . Chiki Charts and Charting in Excel 1 December 9th 04 01:19 AM
I would like to make a schedule for my dept. Who works what days . Chiki Charts and Charting in Excel 0 December 6th 04 02:55 AM
COUNTIF Question zbert Excel Worksheet Functions 1 November 1st 04 02:59 AM


All times are GMT +1. The time now is 06:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"