Create Match List From Another Worksheet

Chimp8471 · #1 (**permalink**) 06-24-08, 17:43

I need to create a full list of fixtures for a football (Soccer) league i am involved in.

i believe this is a multi stage project that i need to tackle in bite size chunks.

I have a list of teams, division that they play in and there home stadium name.

my spreadsheet has 2 sheets visible at the mo...

Rules:

I need to create a list of all games that require to be played.

All teams in the same division must play each other twice, once at the home stadium of each club.

Only division 1 clubs play against other division1 clubs

Only division 2 clubs play against other division2 clubs

I need a full list of matches to be generated in my worksheet called "required Matches"

can this be done via a macro? if so i really could do with some help please.

sco08y · #2 (**permalink**) 06-26-08, 22:43

Quote:

All teams in the same division must play each other twice, once at the home stadium of each club.

Specifically, it's a double round-robin tournament and that entry has the algorithm.

The algorithm says to "fix one competitor and rotate the others clockwise." Of course, VB has crap arrays that you can't rotate, but you can fake it just fine.

My strategy:

Code:

1. Looping over the divisions:
  2. Find the number of teams in each division.
  3. Build an array of row offsets.
  4. Spit out rounds by the round-robin algorithm.

sco08y · #3 (**permalink**) 06-27-08, 01:07

Anyway, here it is. I did this on Excel 2007 but in compatibility mode so it should work with 2003. The code is all in a single sub in Module1 and it's pretty well commented.

A few notes: I've added some ranges. If you decide to pretty up the worksheet, you'll want to change them. Make sure that they're still all parallel. An easy way to do this would be to add entire rows before the table.

The macro clears out the required matches before it runs. It doesn't check to make sure it's got enough space for all the rounds.

Divisions have to be numbered as integers. You could allow for division names, but you'll have to either ensure the team list is sorted or make your own list of divisions. That's easy in most other languages, just use a hash or associative array, but VB sucks at that.

sco08y · #4 (**permalink**) 06-27-08, 01:21

BTW... if you're unfamiliar with VB, bring up the editor (sorry, it's different for different versions of Excel) and just hit F1 on words you're not familiar with. Excel has great online help.

The trickiest part of that code is in the "clockwise rotation" thing with the Mod operator. If you don't understand modulus arithmetic, google it, it's not really all that complicated. I'd love to say that I carefully planned it out, but I really just futzed with the numbers until it did the same thing as on the wikipedia article.

You can test it: just sort it by Home or Away and you'll see that everyone plays everyone.

The algorithm also works just fine with an odd number of teams. I haven't tried it with too many edge cases; you might try it with just 2 teams or 3. It will skip 1 team entirely.

Chimp8471 · #5 (**permalink**) 06-27-08, 05:11

thats excellent thanks,

1 of the things i was going to mention was that teams should always play 1 game home and 1 game away, which you have catered for..

ok i need to take this a step further now.....

i have just spotted a major problem..

if you look at round 1

4 fixtures are scheduled for the same venue this can't happen as one venue can only host one fixture per round.

also i would like to split the fixtures about a bit so that teams don't play each other back to back.

... any ideas please.

p.s thanks again for what you have aleady done