Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP question | Excel Discussion (Misc queries) | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
I would like to make a schedule for my dept. Who works what days . | Charts and Charting in Excel | |||
I would like to make a schedule for my dept. Who works what days . | Charts and Charting in Excel | |||
COUNTIF Question | Excel Worksheet Functions |