Need help with a round robin tournament schedule

[BeachRock]

Hi everyone,

I found this round robin tournament scheduler after quite a bit of searching. It works wonderfully exept for one thing and I've been trying to figure it out now for almost a week with no luck. The problem is that whether there are an even or odd number of teams, it needs to schedule each team Home and then Away rotating every other week without ever being at Home or Away multiple weeks in a row for any team, rotating through until each team has played each other one time.

I thought this would be a simple thing to do but it turned out to be way more difficult than I ever imagined it could be... Does anyone have experience with this and can you help me understand how it can be done? I can't tell you how many different ways I've tried but there are MANY.

Thanks very much for any help you can offer!

[Kaper]

Hi Tony,
Honestly, I would rather doubt if at all (not how)

it can be done

Try to achieve it "by hand" for even some small number of teams (4?)
Successfull?

If I am right, probably there is mathematical proof. Somehow it associates in my head with Dirichlet's drawer principle.
Anyway, its excel forum, not maths!

One can for sure make some attempts to limit a number of consequtive Home or Away (in presented workbook team 1 is always home !).

To show some positive input (not just complaining: cant be done etc.)-> have a look on attached file.
Team 1 is now in most comfortable situation - have H-A-H-A-... ideal sequence.

All other teams (twice and in one case once) in a while have it with slight irregularity

H-A-H-A-A-H-A-H-A... but for 31 matches two in row at Home and some time later 2 in a row Away is anyway not bad.

I used conditional formatting to show this in main table (in orange) that it is second Home in sequence or second Away.

The helper columns AK and on are just for the formatting. Can/shall be removed in final file.

And the formulas used: I left first row as it was.
Then in second row I moved all cells on Away columns to Home position and all Home to Away. I moved cells, so formulas in third row (referring to second) adopted to this situation.
Finaly copied rows 2 and 3 to 4 and 4, then to 6 and 7 and so on.

May be some improvement would be possible, but as I wrote in the beginning - don't think it is possible to have round robin tournament with AHAHAHA or HAHAHA... sequence for all teams.

Enjoy!

[tigerd1972]

this is great thanks... been looking for this ....

[tigerd1972]

this is great

[FDibbins]

this is great

Please do not repeat

[BeachRock]

Hi Kaper,

Thank you for replying and with so much feedback and obvious time spent looking at this. Yes, I've tried the way you described also. The attachment I posted was the way I had found the file online without any of the attempts I've made in changes. The one thing I have seen is that to do a round robin like this at all, the first team has to remain a constant in the first column as does the formula layout in the first row. My stubborness is kicking in on this... It seems like there "should" be a way to do it, some pattern that could be followed and all I need to do is find that pattern. But, I agree with you that having some irregularity may be the best I'll be able to come up with. I've been able to accomplish the Home and Away rotation with an odd number of teams but even is a completely different story...

Thanks again for helping. I'm going to let this post stay on here for a while in case anyone else has any ideas.

Cheers!

[Kaper]

But ... if you keep team 1 in first column, it is always Home. Or may be I understand something wrong.
In my Proposition Team 1 is always in Game 1 of each round but in a sequence Home-Away-Home-Away....

By the way, have you:

Try to achieve it "by hand" for even some small number of teams (4?)
Successfull?

if yes, post it and then may be try 5 or 6 teams

PS. How about Bye - is Home-Bye-Home-Away-Home-Away proper sequence or shall be rather Home-Bye-Away-Home-Away ?

[BeachRock]

You are correct, team 1 would always be home which is why I was tryign to figure out another way to do it. :-)

Yes, I have another workbook where I've been trying to put them all in by hand, minus using any of the formulas or the named formulas. I am able to get it to 9 weeks before it has a problem. In my pool league for which I'm intending to use this, we'll only ever go up to 24 teams maximum so that's how many I've been working with on my other workbook. See the attached.

With the BYE weeks, I kept the BYE team in the Home slot the entire time and all of the other teams would rotate in as their Away week. It works out perfect that way and there is a definite pattern to easily follow, but that was done all by hand.

[BeachRock]

For anyone looking for information on the best way to create round robin schedules, take a look at this:

Ian Wakeling's spreadsheet will generate a balanced schedule for up to 100 items.

This seems to me to represent the best that any tournament schedule can be. This Ian guy is a genius. Excellent work!

[Kaper]

Nice, but ...

1) Impossible is still impossible :-) starting from 4 teams there are sentences HH and AA.

2) IanBAlancedRoundRobin in Court or Field option does not care of Team 1 playing always Game 1, while you want it, don't you.

3) if you select Court or Field format the sequences of contignous home or away assignments (HHHH... or AAAA...) are very long. For instance for 32 teams, team1 a is assigned HHHHHHHHHHHHHHHAAAAAAAAAAAAAAAH

4) if you select Home/Away format it does not present Game number in given round. Of course in home-away all matches could be played the same time. But anyway you should be able to show the planned matches to spectators.

To stop complaining and show my positive (as always :-D) attitude, have a look on a piece of code to be inserted at the end of CommandButton1_Click (just before application.screenupdating = true):

' 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

I think it could be quite usefull. Moreover, because teams are listed in column A, the output of my procedure keeps Team 1 playing in Game 1 in all rounds !!!

Enjoy!

[ian_w]

It is good to see that you found the round robin generator program useful. And thanks to BeachRock for the kind words

I believe that in home/away mode the generator always alternates between home and away as much as it is possible to do. If there are an even number of teams, it is easy to see that only two of the teams can alternate perfectly as follows:

team1: HAHAHAHA
team2: AHAHAHAH

if team 3 were to have

team3: HAHAHAHA

then they are at home whenever team 1 is home, and away whenever team 1 is away, so team 1 and 3 can never play each other. The same argument would apply if another team had team2's sequence. The consequence is that all but two of the teams must have at least one repeated HH or AA sequence.

[Kaper]

Hi Ian,

Nice to see you here, and thanks for usefull comments.

Yes, I think the Home/Away algorithm really does as much as possible.

[poetikpunk]

Ian, I found your Excel Sheet do you have an updated version that works with never versions of excel. I keep getting error when not running in compatibility mode. Also is there a way to save the out come of said sheet?

[ian_w]

Are you using version 5.1 from this time last year? Can you tell me about the error you get and in which version of Excel. Thanks.

[ian_w]

Now I look again, perhaps you are using Kaper's attachment above. Here is version 5.1 which is an xlsm file for newer Excel. As for saving the results, you can either copy and paste to a new worksheet, or you could keep a note of the random key as the same key and the same schedule parameters will always give the same result.

[obiwank]

Alas, we are limited to 3 courts, but may have as many as 8 teams, say.

I don't see that your sheet allows for this - or am I missing something?

Thanks,
Matthew

[obiwank]

How simple!

Yes, that helps. Thanks.

[Jprovencher]

I know this is an old thread but I'm trying to find out how to solve a RoundRobin scheduling issue. I manage the schedule for a dart league. I've been using Ian Wakelings Excel scheduler to generate the schedules and I just got a complaint because one of the teams played the better teams 4 times while playing the weaker teams twice. Our season is 30 weeks - two 15 week halves. I usually flip the first half schedule to make the second half, but that isn't working in this case.

I manually went through and have it so that each team plays other teams the same number of times, has the same number of home and away games, and the same number of byes.

The problem is that a couple teams play the same team twice in a row - first home then away or vice versa. If I move them around then the byes get messed up.

Does anyone have any idea how I might approach this?

[FDibbins]

Hi, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.