Dynamic Schedule Generator

[SoccerGuy]

I am new at VBA is hoping I can get some advice or recommendations on the following:

• I am trying to create a worksheet that would, randomly generate a schedule for my soccer league.
• I looked at the coding for Sudoku and thought this may require a similar approach
• The aim is to randomly generate a number in a cell (or square on a form) to represent a particular week during the season, and assuming:

1. "0" for when team is matched up against itself
   
2. Assuming home and away game (option can be added to play each other once)
3. May include added features such as:
• Manual input of week # into a box to assign particular home/away game to a team, then the generator fills in the blanks
• Could also include restriction of not more that 3 consecutive home or away games

• The schedule generator would be able to be used for 4 to 20 teams
  

Below is a general layout match-up of 10 teams (without the week #s filled in):


It's a big challenge for me. Any help is appreciated.

Thanks

[traveller...@gmail.com]

Hi All,

I have attached a word document with a sample for the first 2 weeks that I manually filled into Excel.

Thanks,
Robbie

[Bruce Mcpherson]

It's unlikely anyone will help you with this on a forum like this (which is not really about vanilla VBA), or indeeed any other forum. Looks like you are asking for someone to develop a complete application for you. Although people are very generous with their time, I doubt they will do something like this. 

You should show what you've tried, and ask for help if you're stuck with an algorithm or something. If you need someone to develop an app from scratch you can use a site like getacoder.com.

Bruce

--
You received this message because you are subscribed to the Google Groups "Excel Liberation" group.
To unsubscribe from this group and stop receiving emails from it, send an email to excel-ramblin...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[SoccerGuy]

Hi Bruce,

Thanks for the advice. I have attached a copy of the Excel file as you suggested.

I have basic skills in VBA, so I would definitely appreciate any coding advise to improve my skills.

I was thinking to try the recursion procedure to see if can improve the logic, but I do not know how to use it.

The reason I am trying to create this scheduling system is to help manage my community soccer league.

Thanks,
Robbie

[Bruce Mcpherson]

 I dont know anything about soccer, so can you briefly describe what you need to happen and what are the constraints

here's what i understand as base requirement

-you have a grid of teams by teams

-you want it to generate on which week who plays who

-presumably every cell should have 1 week number against it

does there have to be the same number of matches every week?

how many weeks are there?

does everybody have to play each other at some point? how many times?

What is specifically wrong with what you've done so far?

bruce

[SoccerGuy]

Bruce,

Your understanding is correct.

• The objective is to randomly generate who plays who on a specific week, by filling the week #s in the grid
  
• The grid provides an outline of the teams (home and away)
• Each team have a game each week, except for when there is an odd # of team where 1 team will be off each week
• Under a round-robin, each team plays home & away against all other teams
  
• The number of matches played each week is based on # of team in the league:
• (a) (Even # of teams) / 2, or
• (b) (Odd # of teams - 1) / 2
• The number weeks in the league is also based on # of team in the league (includes home & away):
• (a) (Even # of teams - 1) x 2, or
• (b) (Odd # of teams) x 2

The issue I am having is completing the grid without having conflicts or ending up with blank areas that cannot be filled without violating the constraint a team cannot be scheduled to play 2 games in the same week.

This is how I searched to see if I can find a logic for Sudoku solver, as some similar constraint exist. Do you think I need to create a form to generate a solver (using brute force) or continue with the worksheet as I do now?

Please let me know if this additional information helps. Thanks.

-Robbie

[Bruce McPherson]

Ok .. Got it. Ill see if I can help you with an algorithm

Sent from my iPad

[Travellers]

Thanks Bruce.

Sent from Robbie's IPhone

You received this message because you are subscribed to a topic in the Google Groups "Excel Liberation" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/excel-ramblings/0Hzc8XU5ET4/unsubscribe.
To unsubscribe from this group and all of its topics, send an email to excel-ramblin...@googlegroups.com.

[Bruce Mcpherson]

I came across this, which is an vba free round robin tournament scheduler

[Travellers United FC]

Bruce, thanks for the scheduler. It is very useful when doing tournaments, but is limited for scheduling home and away.

I am learning on using VBA since I took on this small project, engaging me to want to learn even more. I wonder if I am over complicating the process of the tasks...?

I will try another simplified approach, still using VBA, but a bit more logical. But please do keep me in mind if you come across any algorithm or recommended approach.

I never knew programming in VBA would be so interesting, I love it! 

[Bruce Mcpherson]

actually the best algorithm for this is to use a berger table.. http://www.tournamentdirector.co.uk/berger_pairings.html 

and randomly populate the team names with yours. splitting it in two allows you deal with home and away

If i find some time i'll try to do a vba version of the algorithm for you

bruce

[Travellers United FC]

Hi Bruce,

It's been a while.

I have a question regarding Backtracking using VBA. How difficult is it to do, create or modify one? I was thinking of using that concept to fill in the numbers for a two-dimension (6 rows x 6 columns) table to create my schedule. I want to take a similar concept as that taken for the 8 queens solutions on the chess board.

Please, do you have any sample VBA Backtracking code I could use to practice with and then try to modify to see if it can work for what I need?

Thanks again.

[Bruce Mcpherson]

hello .. there's a VBA example of backtracking here. Generating a random sudoku is exactly the same as trying to solve a blank one by brute force, and relies totally on being able to backtrack

- try to solve the problem, keeping an instance of the grid class for every successful step

- if it fails, throw away that instance, and try a different possibility using the last good instance

- if you run out of possibilties, then throw away that instance and go back to the last unfinished instance

.. repeat for ever until you have solved it or have no good instances left

You need to be comfortable with recursion, and watch out for memory leaks, aside from that its not that difficult. 

http://ramblings.mcpher.com/Home/excelquirks/recursionlink/sudoku-generator-and-solver/generation

[Travellers United FC]

Thanks Bruce. I will give it a go!

[Travellers United FC]

Hi Bruce,

How are you?

I gave it a try but got very confused trying to understand. My difficulty is that I am not a programmer, but I am willing to learn and will put in the time to learn.

Do you a simpler algorithm for backtraking and one that shows how to keep an instance of the class grid, if it fails it is discarded until successful? I am still trying to better understand how to use classes effectively.

I attached a sample of the sheet I updated, selecting random numbers to solve the grid, but not efficient and it has timing issues. Do you have any recommendation? I am not asking you to provide a full solution, but any help you have that can guide me in the right direction.

I am determined to get a good understanding of these topics

Your advise is always appreciated. Thanks.

-Robert