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

 

[Mark W.]

On 2002-09-10 08:17, cgreen wrote:
How am I picking my group of 7 per week?

The 7 pairings are listed by 'Week' number (column C).

 

[cgreen]

I see the week 1 (7) times, week 2 (7) times, ...., week 13 (7) times that is in column C. I do not see how the pairs are being picked, what columns am I getting the pairs from. I know that once I see it, the light will come on like it did for the macro.

 

[Mark W.]

That would be the 'Home' and 'Visitor' columns (A:B).

 

[cgreen]

Looking at column A (home) row 2 thru 14 has a value of 1 in all the cells. Column B (visitor) row 2 thru 14 has a value of 2,3,4,5,6,7,8,9,10,11,12,13,14. I do not understand how I am getting my pair for week 1.

 

[Mark W.]

Re-read my postings on...

8/29/02 12:24
9/03/02 07:01

 

[cgreen]

Mark, thank you very much for your help. I have printed out all of the postings for this question. I will continue to play with it and hope the light comes on. I have recommended this sight to several people .. you guys are great!

 

[cgreen]

Mark, you would be proud of me. I told you all I needed was for the light to come on. I created the macro, but didn't run it. It's amazing the things a person can do when they follow directions

When I run the macro it shuffles column A & B (like you said), however, the shuffle is getting stuck in a loop, when I hit the escape button, it is giving me a debug option. It is highlighting the selection.sort sentence ... any thoughts would be helpful.

Sub Macro1()
Do

Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Loop Until Worksheets("Sheet1").Range("J1").Value = 0
End Sub

Sorry it took me so long to see it.

 

[Mark W.]

If you configured your worksheet exactly like I described the macro doesn't get stuck in a loop. It just takes a while for a random solution to be found for all weeks. Watch the values in column J go to 0 as each week's pairings are resolved. As you discovered you can press [ESC] at anytime and then resume the macro's execution where it left off.

 

[cgreen]

Hi again, the error I keep getting is -

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

I haven't given up yet. I'm goting to try and re-do the whole worksheet (since I'm understanding more, maybe I can see what formating I did wrong). If you have any ideas, please send them on ... Thanks for your help and understanding Mark ... keep smiling.

 

[Mark W.]

Select cell A1 before you execute the macro.