# Complex Sports Fixture algorithm

• I'm trying to set up a spreadsheet to organise a Sports Fixture such that each team competes with each other team only once.

I had initially thought the solution would be simple but after some research I've found quite complex formulae apply.

I'm trying to apply the formula referred to in http://www.cs.colostate.edu/~m…nal/massey_parallel97.pdf (5 An Optimal Factorization Algorithm).

Does anyone know how to code this in vba?

Robert Hind
Perth - Western Australia

• Re: Complex Sports Fixture algorithm

Hi there,

Converting the methodology in that paper into a vba algorithm is a big job for a freeby! There might be an easier way out though. You're problem could be represented as a binary programming problem. With an appropriate setup you could have a go at using Excel solver. Not sure how it would go at crunching out a solution for large numbers of teams though. I might have a go at setting up a sample after work tonight if it doesn't turn out to be too much of a big job. How many teams do you want to schedule?

Paul

• Re: Complex Sports Fixture algorithm

r_hind,

I'm not sure what you're trying to do exactly but it would seem to me that a simple loop would accomplish the task.

For the following example. Range A2:A27 contained the values "Team A, Team B, Team C, . . . etc"

Now this simply creates a list of "Team1 / Team2" pairs. More code would be needed to actually schedule the teams for play. This is where the hard part will lie. Being able to determine the number of weeks of play and the number of games to schedule each week. Whether a team can play 1 or more times each week. With a roster of 26 teams there are 25 games that each team will have to play.

Regards,
Barry

My Favorite New Thing:
Dynamic Named Ranges

If you get a response that answers your query or you see a response that explains something for you. Maybe think about "LIKING" that response let the member know he/she has helped another.

• Re: Complex Sports Fixture algorithm

Thanks guys. I've now found a solution using a posting from TinyJack in response to a thread titled "Extracting Combinations".

Cheers
Robert

Robert Hind
Perth - Western Australia

• Re: Complex Sports Fixture algorithm

Please post your solution here, in the spirit of the forum, so those that search can find their answer.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!