Sports Fixtures Generator

In this post we see how to use Excel VBA to generate fixtures for any sports competition. We use the round robin model and rotation algorithm to create either single or double robin schedules. As an example, we generate fixtures for a fictional schedule of the Spanish football premier league (La Liga). Note that professional leagues need to take into consideration additional factors to create the schedule. We will first start by learning how to obtain all possible combinations of two elements (two teams or players) from a list of n total elements. That´s achieved with a simple macro. Then we´ll implement the round robin method and rotation algorithm to create fixtures in Excel (for both single and double round robin schedules).

Combinations of teams

The number of possible combinations of 2 teams playing each other from a full list of n teams is given by the multiplication of n times n-1. This assumes the order does not matter and teams play each other twice (known as double round robin or home and away). This is the case for most football leagues and other team sports.

Let´s see an example to illustrate this. All possible combinations of 2 teams playing each other from a group of 4 teams is 4 x 3  = 12 matches in total (see all possible combinations below).

team1-team2     team2-team1

team1-team3     team3-team1

team1-team4     team4-team1

team2-team3     team3-team2

team2-team4     team4-team2

team3-team4     team4-team3

For a group of 6 teams is 6 x 5 = 30 matches, and for a group of 20 teams is 20 x 19 = 380 matches, which is often the number of matches for a regular football season.

But if they play each other just once (single round robin), the number of possible combinations is half that number, or n times n -1 divided by 2. That may be the case for classification games of some tennis tournaments.

The following macro gets all possible combinations of 2 elements (without repetition) from an array obtained from column values of a selected range, and debugs the list to the immediate window. Alternatively, we can write it to the sheet or create a list or subarray with those values.

 

  Sub GetCombinations()
      Dim col As New Collection, arr As Variant, i As Integer
      Dim elem1 As Variant, elem2 As Variant, pair As String
  
      arr = Application.Transpose(Selection)
 
      For Each elem1 In arr
          For Each elem2 In arr
              If elem1 <> elem2 Then  'without repetition
                  pair = elem1 & "-" & elem2
                  col.Add pair
              End If
          Next elem2
      Next elem1
 
      For i = 1 To col.Count
          Debug.Print col.Item(i)  'or create subarray or list
      Next i
  End Sub

Here´s how that looks like for a list of 6 teams with double round robin (home and away matches). As mentioned earlier, there is a total of 30 possible match combinations. Note that the order of the matches differs from the arbitrary order shown earlier for 4 teams. That´s irrelevant at this point because we are going to use a slightly different method to sort matches and generate fixtures for a football league (or other tournament) in Excel.

Round Robin Method and Rotation Algorithm

We could use the macro above to get all possible combinations of matches for teams in a football league (or any other competition). But all those combinations need to be organized within rounds where teams play each other just once per round, and where they alternate playing home vs away as much as possible (for double round robin). We can achieve that using the round robin method and rotation algorithm, which implementation makes the code more complex than just the macro above.

The principle behind the rotation algorithm used to create football fixtures in Excel consists of fixing the position of one team throughout the schedule of the tournament or league, and letting the other teams rotate around it until all the teams have played each other. Let´s see a simple example with only 6 teams, letting team1 fixed. As explained earlier, there are 30 possible combinations of matches (double round robin) or 15 matches if they play each other just once (single round robin). This will require 5 rounds, which is always calculated from the number of teams minus 1 for a single round robin. 

Round1
team1 - team6
team2 - team5
team3 - team4

Round2 (teams will swap position - see later)
team1 - team5
team6 - team4
team2 - team3

Round3
team1 - team4
team5 - team3
team6 - team2

Round4 (teams will swap position - see later)
team1 - team3
team4 - team2
team5 - team6

Round5
team1 team2
team3 team6
team4 team5

For double round robin (playing home and away), the number of rounds is just double (10 rounds in this example), and the combination of matches for the other 5 rounds is just opposite to those of the first phase. But before doing that, there are a couple of other things that we need to consider.

Playing Home and Away (Double Round Robin)

Fixtures are always created in a way that minimizes any possible advantage by any team. Hence, the number of times a team plays home vs away needs to be evenly distributed (as playing home is usually advantageous). We achieve that by alternating the position of teams for matches generated with the rotation algorithm every other round. In the previous example, that means swapping the position of teams for rounds 2 and 4 (rounds 1, 3 and 5 stay the same). Here´s how fixtures for phase 1 look like in Excel.

We can see that most teams play evenly home and away every other round (or every two rounds) in most cases. The more teams there are, the better the alignment. But it is the second phase where small differences get levelled up as teams play eachother for the second time swapping positions. As a result, all teams play eachother both home and away. The following 5 rounds for the example above looks as shown below.

In the event of an odd number of teams (or players), we need to add a dummy entry (BYE) in the rotation algorithm. Hence, for each round, a team will pair with "BYE", or in other words, a team will rest. That can be left in the schedule or removed altogether. Fixtures for a single round robin tournament with 5 teams (or players) would look as follows.

Algorithm Implementation with Excel VBA

There are many different ways to implement the round robin model and rotation algorithm with Excel VBA (or any other programming language). Download the file below to see and try an example of such an implementation. We can start putting the list of teams (or players) into an array. We can declare the array variable as a dynamic array or simply as Variant if we assigned values from a range. Assuming the list of teams is in column A and there at least 4 teams, the array can be built as shown below. Note that Option Base 1 is used (at module level) to assign array values starting at 1 instead of 0. 

 

  Dim teamsCount As Integer, teamsRange As Range, teamsArray As Variant
  teamsCount = WorksheetFunction.CountA(Range("A:A"))

  If teamsCount >= 4 Then
      Set teamsRange = Range("A1:A" & teamsCount)
      teamsArray = Application.Transpose(teamsRange.Value)

  End If

As explained earlier, if the number of teams is odd, we need to add a dummy entry to the array. We can use the VBA Modulus operator (Mod) to check whether the number of teams is odd, and in such case, re-dimension the array and add an element at the end as follows.

    

  If teamsCount Mod 2 <> 0 Then
      ReDim Preserve teamsArray(teamsCount + 1)

      teamsCount = teamsCount + 1
      teamsArray(teamsCount) = "BYE"
  End If

Then we start a loop that will run as many times as there are rounds in a single round robin (equals the number of teams - 1). The variables pos1 and pos2 store a value that is used to determine the position of each team other than the fixed team. These variables are initialized for each round and updated later. A second loop picks each team from the array to build pairs (teamA and teamB) for each match within the round, and writes the match to another sheet (e.g. Sheet2). The code below shows a simple implementation but does not optimize teams to play home vs away. Download the file at the bottom of this article to see the full code.

  

  For fixturesRound = 1 To teamsCount - 1
    
      'initialize variables that get the position of teams playing each round
      pos1 = teamsCount - fixturesRound + 1
      pos2 = pos1
     
      'loop through teams for each round
      For n = 1 To teamsCount/2
       
          'team A
          If n = 1 Then  'first team does not rotate in this algorithm
              teamA = teamsArray(1)
          Else  'other teams rotate forward
              pos1 = pos1 + 1
              If pos1 > teamsCount Then pos1 = 2  'skip position 1 (fixed team)
              teamA = teamsArray(pos1)
          End If
       
          'team B
          teamB = teamsArray(pos2)
          pos2 = pos2 - 1
          If pos2 = 1 Then pos2 = teamsCount
     
          'write teams pair to sheet
          r = r + 1
          Sheet2.Range("A" & r) = teamA & "-" & teamB       'requires Sheet2
    
      Next n
  Next fixturesRound

For a double round robin, another loop reads through matches of phase 1 (in Sheet2) and writes the schedule of phase 2 by swaping the position of teams (home plays away and vice-versa). That generates the full schedule of fixtures for home and away matches in a league or tournament in Excel.

Click Here to download Sports Fixtures Generator in Excel.