asked on

Google Sheets Youth Baseball Round Robin Scheduling

Hi all,

I've been put in charge of some Youth Baseball scheduling. Here is a screenshot of what I'm trying to achieve:

I'd like to automate this process, and here are the variables:

1. Teams (could be any reasonable number from 2 to 20).
2. varTotalGames (this is the total number of games that each team needs to play). In this case, it's 20.

So given those variables, we need each team to play a total of 20 games, 10 games as the Home Team, and 10 Games as the Away Team.

Is there a way I can auto-populate Columns C and D?

If there isn't an easy solution in Google Sheets, I'm willing to relocate to MS Excel.

Thanks in advance.

Mike in IT

I don't know if this is exactly what you are looking for, but this works in Excel:

Sub RoundRobin()
    Dim x, y, Counted, Number
    Set wsSheet3 = ThisWorkbook.Worksheets("Sheet3")
    
    Number = wsSheet3.Range("B2").Value
    x = wsSheet3.Range("A" & Rows.Count).End(xlUp).Row
    For Each c In wsSheet3.Range(wsSheet3.Range("A2"), wsSheet3.Range("A" & Rows.Count).End(xlUp))
        Counted = 1
        y = wsSheet3.Range("D" & Rows.Count).End(xlUp).Row
        For i = 2 To x Step 1
            If c.Value <> wsSheet3.Range("A" & i).Value And Counted <= Number / 2 Then
                wsSheet3.Range("D" & y + Counted).Value = c.Value
                wsSheet3.Range("E" & y + Counted).Value = wsSheet3.Range("A" & i).Value
                Counted = Counted + 1
            ElseIf c.Value <> wsSheet3.Range("A" & i).Value And Counted <= Number Then
                wsSheet3.Range("E" & y + Counted).Value = c.Value
                wsSheet3.Range("D" & y + Counted).Value = wsSheet3.Range("A" & i).Value
                Counted = Counted + 1
            End If
        Next i
    Next
End Sub

Open in new window

Go-Bruins

ASKER

Thanks so much - this seems Excel-centric, but if I can't find a similar solution for Google Sheets, I will switch over...

Go-Bruins

ASKER

Mike - would you be so kind as to attach an Excel file so that I could learn by example?

Thanks.

ASKER CERTIFIED SOLUTION

Mike in IT

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Go-Bruins

ASKER

Tremendous - thank you.