Random Scheduling

[cgreen]

I have 14 teams playing for 26 weeks. The teams play each other only twice in the 26-week frame. Is there a way to do a random schedule per week, then freeze (or do manual calculation) so that the schedule does not change for the 26 weeks unless I command it to? There will be 7 games per week, and again, each team can only play each other twice in the 26-week frame. The number listed first is understood to be where the game will be played, so it has to be random as well.

example(teams are identified as 1 thru 14):

Week One
2 and 13
3 and 4
11 and 6
5 and 14
1 and 7
8 and 12
10 and 9

Week Two
14 and 1
2 and 6
3 and 8
5 and 4
7 and 10
12 and 9
11 and 13

 

[cgreen]

My team number and weeks of play have changed (18 teams & 34 weeks). I changed the formulas but the random scheduling seems to be locked in a loop. Here are the changes I made:
Column A & B - no formula (153 lines of data)
Column C - =IF(ROW()<11,1,OFFSET(C2,-9,)+1)
Column D - =IF(SUM(OFFSET(G2,-E2,):OFFSET(G2,F2,)),RAND(),1)
Column E - =MOD(ROW()-2,9)
Column F - =8-E2
Column G - {=SUM((A2=OFFSET(A2,-E2,):OFFSET(A2,F2,))+(B2=OFFSET(A2,-E2,):OFFSET(A2,F2,))+(B2=OFFSET(B2,-E2,):OFFSET(B2,F2,)))-2}
Column H - blank
Column I - no formula (number 1 through 17)
Column J - =SUMIF(C:C,I2,G:G)

Any suggestions?
This message was edited by cgreen on 2002-11-07 08:43
This message was edited by cgreen on 2002-11-07 22:08

 

[cgreen]

This is the error I'm getting:

Run-time error '1004'
Sort method of Range class failed.

When I go to the help, I get the following information:

Macro Error

There is an error in the macro you were running. The specified method can't be used on the specified object for one of the following reasons.

·An argument contains a value that is not valid. A common cause of this problem is trying to gain access to an object that does not exist; for example, Workbooks(5) when only three workbooks are open.
·The method can't be used in the applied context. For example, some Range object methods require that the range contain data. If the range does not contain data, the method fails.
·An external error occurred, such as a failure to read or write from a file.

How can I debug this?

 

[Mark W.]

On 2002-11-07 12:40, cgreen wrote:
This is the error I'm getting:

Run-time error '1004'
Sort method of Range class failed.

When I go to the help, I get the following information:

Macro Error

There is an error in the macro you were running. The specified method can't be used on the specified object for one of the following reasons.

·An argument contains a value that is not valid. A common cause of this problem is trying to gain access to an object that does not exist; for example, Workbooks(5) when only three workbooks are open.
·The method can't be used in the applied context. For example, some Range object methods require that the range contain data. If the range does not contain data, the method fails.
·An external error occurred, such as a failure to read or write from a file.

How can I debug this?

There's nothing to debug. You are getting this error because you must select a cell (e.g., A1) within the list range before you execute the macro.

 

[cgreen]

Hi Mark, I'm so glad you responded. I am selecting the 'A1' cell. I still have my database for the 14 teams and it works great. When I adjust my formulas and data lines for 18 teams then I get the error. Do you think the file is to big?

 

[Mark W.]

No, but the last time we discussed this process I thought it was decided that it was best to run for only 1/2 of the total number of weeks. 17 in this case. How are you playing 18 teams within 17 weeks?

 

[cgreen]

With 18 teams I have 34 weeks of play, we play each team twice so I only need 17 weeks then I reverse the playing order for the next 17 weeks.

 

[Mark W.]

Sorry, I've been away from this too long. Okay, so 9 pairs of teams should play on each of 17 weeks. Do you remember how to construct the column A:B values? The 'Week' formula will need to be adjusted looking at that now.
This message was edited by Mark W. on 2002-11-07 15:51

 

[cgreen]

Column C, E, F, and J I thought needed to be adjusted since the numbers changed.

 

[Mark W.]

Okay, I've changed the following formulas...

[C2] =IF(ROW()<11,1,OFFSET(C2,-9,)+1)
[E2] =MOD(ROW()-2,9)
[F2] =8-E2
[K1] =SUM(K2:K18)

Copy down formulas in column C, E, and F. Don't forget to add the additional weeks to column J and fill down the SUMIF formulas in column K.

I'm about to run the model to see what happens.

P.S.: I have 153 data rows.
This message was edited by Mark W. on 2002-11-07 16:47

 

[cgreen]

Mark, did you have any luck making your model run? Mine still gets stuck in a loop.