Modified Round Robin Tennis Tournament

[smckenzie04]

I am taking over a doubles tennis tournament with a group of 12 guys we have each year and in the past they would assign everyone a card and the order picked seeds the session. The problem becomes when partners and matches repeat more than whats required, which would be playing with everyone twice and two people 3 times (11+11+2=24).
We play 3 sessions of 8 matches each, totaling 24 matches per person. I would like a way to randomly pick who each person plays with and also who you play against as much as possible.

The following picture is what I have set up so far but its simply a modified round robin schedule and the order you play with your partners doesn't vary. I would appreciate any help to make it truly random.

 

[Dendro]

Hi,

you could use RANDBETWEEN() to create a random nr between 1 and 12, combine this with a VLOOKUP in Q2:R13 to get the names. The problem however is that one person could play against himself. This can be solved with a macro if you don't mind.

 

[smckenzie04]

I don't mind using macro's but its been almost 10 years since I made one. Could you elaborate a little?

 

[Dendro]

I'm trying something now, but i'm stuck on an error(will be able to fix it, something with the syntax...). I'll post it later on today

 

[Dendro]

Hi,

I was able to find the error. Here is the code, i copied the Q2:R13 range on an empty sheet, i suggest you do that too since you made such an effort on creating a nice layout and I ...didn't. I'll leave adjustments in the macro regarding the layout over to you. You could also keep this new sheet and create references in your old sheet to this sheet you just created. I'm aware that the macro could use some polishing, but I'm happy it works.

Sub tennis()
Dim TR As Integer
Dim players As New Collection
t = 0
c = 0
Cells(1, 1).Value = "Court#"
Cells(1, 2).Value = "Team#"
For j = 1 To 12
    If ((j - 1) Mod 2) = 0 Then
    t = t + 1
    End If
    If ((j - 1) Mod 4) = 0 Then
        c = c + 1
    End If
    Cells(j + 1, 1).Value = "court " & c
    Cells(j + 1, 2).Value = "team " & t
Next j
For TR = 1 To 8
    For i = 2 To 13
        players.Add Sheets("Session 1").Cells(i, 18).Text
    Next
    Cells(1, TR + 2).Value = "Round " & TR
    For p = 1 To 12
        randomplayernr = Application.WorksheetFunction.RandBetween(1, 13 - p)
        Cells(p + 1, TR + 2).Value = players.Item(randomplayernr)
        players.Remove (randomplayernr)
    Next
Next
End Sub

 

[smckenzie04]

Thanks for the hard work Dendro but I'm not getting the results I had expected. I placed the numbers/names in Q2:R13 as you suggested on a new sheet, actually had to open a whole new file in Excel because apparently Apple's Numbers program doesn't do macro's. I had hoped we would play with everyone twice and a random 2 people 3 times however the output that's generated has me play with Ray 7 times over the 3 sessions, Dennis 4 and never with John. I have attached a pic of the first session results, please let me know if I did something wrong.

 

[Dendro]

Oh right, I was getting to much into trying to solve my problem with collections in vba I lost track of the actual problem to solve

It would only need a small adjustment, but i have a question for you though. All possible combinations of players are 66 combinations...not any of the mentioned numbers above, or am I missing something here?

Example with 5 players, let's call them A, B, C, D and E:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]np
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]x
[/TD]
[TD]np
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]np
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]np
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]x
[/TD]
[TD]np
[/TD]
[/TR]
</tbody>[/TABLE]
x = possible, np=not possible, empty=already in combination(reading top to bottom starting from the left column)

This makes: 4+3+2+1=10 combinations of players. A game holds 2 teams so you can have 5 games to hold all combinations.

If you do this with 12 players, you will get 66 combinations. If you want to play with each player twice+2random combinations: 2*66= 132+2=134. This would get you to 67 games<>24 games you mentioned??

 

[smckenzie04]

Honestly I have re-wrote this post a dozen times thinking through the math but I just can't seem to put it into words and I think thats because we are looking at different things so here is my attempt to explain my thinking. If I play with everyone that is 11 unique pairings, multiply by 2 and thats 22 matches, play with 2 people 3 times and thats 24 total matches. I agree with your calculations and there are 66 ways to uniquely pair 12 people once but I don't need to pair everyone in every possible combination, I only need everyone paired once with everyone else (when looking at the first set of unique pairings). To put it another way, the first pick is me paired randomly with 11 people so if I'm paired with Jeff thats the first line of your table (if it was for 12 players). Lets say the next step is to pick Ray's partner, I don't need to play a match with every combination of Ray's possible partners, I only need one to be selected randomly. At least thats where I think the confusion is but I could be way wrong. I honestly don't care how it works as long as it follows the following criteria: Each player plays with everyone else 2 times and two people 3 times (because our goal was 24 total matches).

 

[smckenzie04]

Is it possible to take a column of numbers (w in the Sessions 3 worksheet) and correlate the highest number to a cell on the next worksheet (FINALS!), along with the 2nd, 3rd and so on to their respective cells on the FINALS! worksheet?

 

[Dendro]

I only need everyone paired once with everyone else

,

I don't need to pair everyone in every possible combination

Your explanation seems a bit contradictory,
if you mean with 'each player plays with everyone else', to play with or against then we could get to a solution.