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]

Mark, I think you did a wonderful job. It is exactly what I was wanting to do. I was just concerned about the time it took to run 18 teams.

 

[cgreen]

Hi Tushar,

In dealing with the 18 teams. In a 17 week play period the teams can only play each other once. Does the VB you wrote prevent repeats of the same teams playing in the 17 week period?

By the way, Beautiful writing!

Cheryl

 

[Mark W.]

Tushar, thanks for pointing that out. Looks like we're both dependent on Excel's questionable random number generator.

 

[cgreen]

Tushar,

With help, I have applied the VB you wrote with the Table Mark created (sheet1). When I run your VB the column called 'Rand' (which is column D)runs, then stops. Nothing else runs. How is it doing the random scheduling?

 

[tusharm]

I have no idea what you are doing. The code I shared was completely functional and tested *standalone* code. If you modified it to work with the table proposed by Mark, you, my friend, are on your own.

To use the code, all you have to do is copy it into a standard module, get back into XL, (optionally, create a new workbook,) and run the 'Schedule_Manager' sub (Tools | Macro > Run macro...). It will ask you for the number of teams to schedule and then do its thing. Run it a few times and you will see it generates a different *non predictable* schedule each time.

 

[cgreen]

Tushar,

Wow, the speed is great!!! Thanks for the explanation; I’m still dumb when it comes to VB.

I have two problems with this VB.

1st – I need to tell it how many teams (which you did) and how many weeks of play.
Example:
12 teams = 11 weeks
14 teams = 13 weeks
16 teams = 15 weeks
18 teams = 17 weeks

2nd – The first number is considered the home team and the game is played at the home location. Can the numbers be shuffled up more (It is considered unfair for to many home games in a row.)?
Example:
Team 1 has 15 home games in a row
Team 2 has 6 home games in a row

 

[cgreen]

Tushar,

Sorry for the misunderstanding ... my first problem is not a problem ... you have it working great. The second item listed is my only problem. Thanks for your help.
Cheryl

 

[tusharm]

I suspected that the home-away issue would come up sooner or later. However, it wasn't part of your original requirements. Wouldn't a quick and cheap way be to simply reverse the numbers in a bunch of combinations by hand?

 

[cgreen]

Moving the numbers around manually is something I was looking at, however, I am not the only person who will be using this spreadsheet and unfortunately the other people who will be using this are afraid of computers. I trying to fix it so that they have to do as little as possible.

 

[SIMON123]

Fixture Generator

Hi. I'm new here but have found the information in this forum invaluable. There are some VERY clever people here.

I have taken over a local pool league and have been dropped in the *&% and need to produce a fixture listing for 62 teams across 6 divisions.

I've spent the last 2 months trying to create something in Excel to do this but I am struggling, I'm not bad on excel functions but only just getting into VBA.

I've downloaded the code created by tusharm which is fantastic for producing the fixture lists.

I was just wondering, without I hope, being too cheeky! If anyone had managed to fix the HOME/AWAY issue so that it can automatically produce fixtures whilst ensuring that teams alternate as much as possible with playing a Home and then an Away match?

If I was suddenly transported to an ideal world, the fixtures would also be able to ensure that 2 teams who share the same venue CANNOT both be at home at the same time!!!!!!

Thanks Very Much

Simon