Round Robin Tournament Scheduling

Court Balanced Round Robin - Home of Ian Wakeling's Excel workbook

Dingo

  • Newbie
  • *
    • Posts: 1
Reply #15 on: March 06, 2014, 01:37:03 AM
Is there a version of this file that will work on Office 2011 for Mac?


Kaper

  • Newbie
  • *
    • Posts: 1
Reply #16 on: March 15, 2014, 04:31:02 AM
Hi,
I was missing table showing games if Team/court is selected.
So added a short piece of code at the end of CommandButton1_Click Sub, just before final application.screenupdating = true.
it is described (as well as rationale shown) in the thread http://www.excelforum.com/excel-formulas-and-functions/995572-need-help-with-a-round-robin-tournament-schedule.html
the code reads:
Code: [Select]
       ' code for listing added by Kaper, see thread:
        ' http://www.excelforum.com/excel-formulas-and-functions/995572-need-help-with-a-round-robin-tournament-schedule.html
          If Not OptionButton1.Value Then
            Dim gameno As Integer
            For j = 1 To ((nplayers - 1) \\ 2) * 2 + 1
              gameno = 0
              For i = 1 To ((nplayers - 1) \\ 2) * 2 + 1
                If WorksheetFunction.CountIf(Cells(nplayers + j + 4, 1).Resize(1, 1 + gameno * 2), Cells(i + 2, 1)) = 0 Then
                  gameno = gameno + 1
                  If Cells(i + 2, j + 1).Interior.ColorIndex = 36 Then
                    Cells(nplayers + j + 4, 2 * gameno) = Cells(i + 2, j + 1)
                    Cells(nplayers + j + 4, 2 * gameno + 1) = Cells(i + 2, 1)
                  Else
                    Cells(nplayers + j + 4, 2 * gameno) = Cells(i + 2, 1)
                    Cells(nplayers + j + 4, 2 * gameno + 1) = Cells(i + 2, j + 1)
                  End If
                  Cells(nplayers + j + 4, 2 * gameno).Resize(1, 2).Interior.ColorIndex = 34 + ((gameno + j) Mod 2) * 3
                End If
              Next i
              Cells(nplayers + j + 4, 1) = Cells(2, j + 1)
            Next j
            For i = 1 To ((nplayers - 1) \\ 2) + 1
              Cells(nplayers + 4, 2 * i) = "Game " & i
            Next i
          End If
For those who do not feel competent enough to play with Excel VBA I also attach modified file.
Hope it could be usefull for some users.

Best Regards,

Kaper


Ian Wakeling

  • Forum Moderator
  • God Member
  • *****
    • Posts: 1141
Reply #17 on: April 02, 2014, 02:54:45 PM
Here is version 5.1 of the generator.  There are two significant changes.

In Home/Away mode, use the checkbox 'Multi-Table layout for H/A' to produce a schedule with one small table for each round.  Thanks to Kaper(above), and BeachRock over at Excel Forum for suggesting this improvement.  The new layout is much more suitable for passing around to participants.

5.1 also implements a double round-robin option.  The same opponents are repeated, but the schedule is tweaked to retain best possible court balance in court/field mode, and tweaked in H/A mode to give both best possible alternation of venues and to ensure that when each game is repeated the team that previously played away, now plays at home.

Let me know if you find any problems.


gerrit098

  • Newbie
  • *
    • Posts: 1
Reply #18 on: August 28, 2014, 04:37:59 PM
Hello, Thank you very much this sheet is very helpful.

I have 2 questions,

is it possible to set the number of fields to a maximum?

is it possible to a third player to every game (arbiter)

if this is possible, I would be very VERY grateful .

kinds regards
Gerrit


Ian Wakeling

  • Forum Moderator
  • God Member
  • *****
    • Posts: 1141
Reply #19 on: August 29, 2014, 03:38:07 AM
Unfortunately I have no plans to add options for referees/arbiters or to vary the maximum number of fields :'(

Note that sometimes it is possible to slice the round-robin.  For example with 12 players and 6 fields, you can easily make each round of 6 games in to two mini-rounds of 3 games each.


Ian Wakeling

  • Forum Moderator
  • God Member
  • *****
    • Posts: 1141
Reply #20 on: September 01, 2014, 03:10:02 PM
Off topic replies have been moved to This Thread


rtmoore

  • Newbie
  • *
    • Posts: 1
Reply #21 on: October 17, 2015, 04:43:30 PM
I am using version 5.1 of your very useful spreadsheet for preparing schedules for a curling league and assigning ice sheets (i.e. courts) to games. To do this I generate a schedule in both Home/Away and Court/Field formats and copy these to my spreadsheet. This has worked well until today when I was generating a schedule for a 6 team round-robin league. My assumption has been up to this point that the actual game schedule would remain the same as I switched format. I guess I have been lucky until now, as that assumption has worked out. However today, it didn't work.

What I have found is that when switching formats the rounds are shuffled. For example for a 6 game round-robin (not randomized) I get the following:

Court/Field (CF) format

round   court 1    court 2    court 3
  1      (3 6)      (1 2)      (4 5)
  2      (6 2)      (5 3)      (1 4)
  3      (4 3)      (2 5)      (6 1)
  4      (2 4)      (3 1)      (5 6)
  5      (1 5)      (6 4)      (2 3)

Home Away (HA) format

team     R1     R2     R3     R4     R5
  1      2      3      4      5      6
  2      1      4      6      3      5
  3      6      1      5      2      4
  4      5      2      1      6      3
  5      4      6      3      1      2
  6      3      5      2      4      1

So, in CF format round 1 is the same as HA round 1. However, CF format round 2 is actually HA round 3. Other rounds are also shuffled.

So, my questions:
1. Am I missing an option that makes the round assignments consistent between the formats?
2. Am I the first person to need the 2 formats to be consistent?
3. Is there a work-around?
4. Could a future version of the scheduler make the 2 formats consistent?

Tom Moore


Ian Wakeling

  • Forum Moderator
  • God Member
  • *****
    • Posts: 1141
Reply #22 on: October 18, 2015, 07:22:57 AM
Tom,

This is rarely asked for in my experience. Usually when HA format is used each team has their own home venue, so the assignment to courts or fields is no longer useful as this is something associated with a central location shared by all teams.

I think up to now you may have been lucky as it depends on the number of teams (n).  For odd values of n, then I think there will always be the correspondence between the rounds of the two formats. Perhaps you have only had odd n up to now?

If n is of the form 6i + 4, where i >= 1, that is to say n = 10,16,22,...  Then the court schedule will be read from the hidden 'library' worksheet, and will be a completely different schedule to the HA format.

Otherwise when n = 4,6,8,12,14,18, etc., then as you have found above there will be a correspondence, but the rounds will be in different orders.  In these cases you need to reorder the rounds of the court schedule as follows.  Round 1 remains the same, then play round 1 + d, where d = (n-2)/2, then play round 1 + 2d, then round 1 + 3d, etc.  

For example if n = 6, then d = 2 and

 1 = 1
1+d = 3
1+2d = 5
1+3d = 7
1+4d = 9

where this number exceeds the maximum number of rounds, (n-1) then keep subtracting (n-1) until it is back in range.  So to un-shuffle the rounds of the court schedule above, rearrange them in the order R1,R3,R5,R2,R4.  Similarly for n = 8, play the rounds in the order R1,R4,R7,R3,R6,R2,R5.

Ian.


lilshan23

  • Newbie
  • *
    • Posts: 0
Hi Ian,

Hope you doing well.

I have been trying to edit your macro to suit  my weekly Family Badminton sessions.

We have a participants anywhere from 12-16 with 2 courts available at one time.

The idea is to have each player pair once with each player(Whist Algorithm) by the end of the session with ref the link below.

https://www.devenezia.com/downloads/round-robin/rounds.php


Shan
« Last Edit: March 05, 2020, 09:25:10 AM by lilshan23 »


Ian Wakeling

  • Forum Moderator
  • God Member
  • *****
    • Posts: 1141
The Excel macros only deal with singles play, so you will find it very hard to achieve any balance for the pairs of players who oppose each other.  I think your best strategy is to mould the whist schedules into something that will work with 2 courts.  For example if you use the generator that you have linked to, then you need to cut each round down by removing one game, and then pair up the removed games into new rounds - for example with 12 players [2 8 v 3 6] & [9 5 v 10 1] can form a new round, as can the two games underneath and so on.  16 players is really easy, simply partition each round of 4 games into 2 rounds of 2 games.  There is a Whist schedule for 13 players (see half way down the Durango Bill page) and you could probably do something similar.  14 or 15 players is problematic, there is no way to balance partners and opponents fairly.

Ian
« Last Edit: March 05, 2020, 10:50:29 AM by Ian Wakeling »


keeweekid

  • Newbie
  • *
    • Posts: 0
I am trying to download the spreadsheet that was paperclipped but it downloads as a Word file.  Any thoughts on how to get this file correctly as I assume it is an Excel file (I have excel on my computer), so not sure what is going wrong.  Thanks!


Ian Wakeling

  • Forum Moderator
  • God Member
  • *****
    • Posts: 1141
That's weird.   I just tried myself and I get the zipped Excel file (xlsm) as intended.  If you would like to click on the envelope icon under my name and then send me an e-mail, then I can reply with the Excel file attached.


raggamuffin

  • Newbie
  • *
    • Posts: 0
Thanx very much!


mortarman007

  • Newbie
  • *
    • Posts: 0
I need to schedule a 12-person Mixed Doubles Round Robin with only 3 Rounds with interchanging partners, and I don't want to duplicate partners nor have anyone play against the same person more than once. I have almost got it, but I am always left with one set of two women who oppose each other twice. Please help.


Ed77

  • Newbie
  • *
    • Posts: 0
Reply #29 on: November 07, 2021, 07:45:58 AM
I need a matrix that will show 6 to 15 players on 4 courts playing 10 games with no 2 players being paired. I realize their may be 2. players paired more than once in the latter games.