VBA or formulas: randomize games for each participant

[AcidKid]

Hi

I'm working on a sheet for a local Darts Club. We meet once every month, and I need to randomize the games for all present participants. I have an Excel sheet, and for each month I have a worksheet.
I can select the present participants in Column A. This is variable for each month (one month we have 21 participants, next month 18). I managed to get all unique games based on all participants I have.
So for instance; I have 11 participants, the formulas generates a list of 55 unique games that are available to play (I also have a VBA script that generates me the list). These games are stored in a seperate column (let's say column D, in this format "Participant X vs Participant Y")

The harder part of the work starts now: I need to be sure that from those 55 unique games, Excel selects 3 random games for each participant and put these in a new column (Column E).
Let's say the script works and starts generating for the first entered participant (Thomas):
Thomas vs Ben
Thomas vs Joe
Thomas vs Viktor
I have 3 games for Thomas, but also 1 game for Ben, Joe and Viktor. So these participants need to be assigned two more games. Ben is the second participant in the list

Ben vs Peter
Ben vs Joe
Ben now has three games assigned. Joe has two games assigned. Joe is the third participant in the list in column A.

Joe vs Viktor
Joe now has three games assigned. Viktor has two games assigned.
And so on.

I could go with a VBA script to select random games, but the part of 3 games per participant is giving me a headache. Anyone could help me?

 

[AcidKid]

Here's my VBA code for generating all 55 unique games

Sub CreateMatchSheet()
    Dim players As Collection
    Set players = New Collection

    Dim matchSheet As Worksheet
    Set matchSheet = ThisWorkbook.Sheets.Add

    matchSheet.Name = "Maand"

    For i = 1 To ThisWorkbook.Sheets("Deelnemers").Cells(ThisWorkbook.Sheets("Deelnemers").Rows.Count, "A").End(xlUp).Row
        Dim player As String
        player = ThisWorkbook.Sheets("Deelnemers").Cells(i, "A").value
        
        Dim exists As Boolean
        exists = False
        
        For j = 1 To players.Count
            If players(j) = player Then
                exists = True
                Exit For
            End If
        Next j
        
        If Not exists Then
            players.Add player
        End If
    Next i

    Dim matches As Collection
    Set matches = New Collection

    Dim player1 As Variant
    Dim player2 As Variant

    For i = 1 To players.Count
        For j = i + 1 To players.Count
            matches.Add Array(players(i), players(j))
        Next j
    Next i

    Dim data As Variant
ReDim data(1 To matches.Count, 1 To 2)

For i = 1 To matches.Count
    data(i, 1) = matches(i)(0)
    data(i, 2) = matches(i)(1)
Next i

matchSheet.Range("A1").Resize(matches.Count, 2).value = data
End Sub

 

[NdNoviceHlp]

The Round Robin code at this link will generate however many random games you need. The participants are also stored in "A". HTH Dave

Round Robin

Hi, very new to this so please be gentle. Im trying to make a round robin draw for my local league. 20 players to play each other 3 times, `Ive searched the web and tried all sorts of different things to no avail, please if any help is available would much appreciate it.

www.mrexcel.com

 

[AcidKid]

HI @NdNoviceHlp
Thanks for the link to that, it solved my question for the most part. I used the last posted answer there.

I ran it a couple of times, but I can see in some runs there are participants playing against themselves.. Is there any way to add a check to the code so we're not matching players against themselves?

 

[NdNoviceHlp]

Hmmm. I trialed the code (again) quite a bit and it seems like playing against yourself doesn't happen so I really don't understand? The player names must be distinct. To start, the player names are in A1 to A whatever. Then on operation, Column C lists the name of the players with Column D to whatever listing whom they play in each game. So, player 1 is in C2 and the 1st game they play is against player in D2, the 2nd game is E2, the 3rd game is F2, etc. Not sure what else could be wrong. Dave

 

[AcidKid]

Hmmm. I trialed the code (again) quite a bit and it seems like playing against yourself doesn't happen so I really don't understand? The player names must be distinct. To start, the player names are in A1 to A whatever. Then on operation, Column C lists the name of the players with Column D to whatever listing whom they play in each game. So, player 1 is in C2 and the 1st game they play is against player in D2, the 2nd game is E2, the 3rd game is F2, etc. Not sure what else could be wrong. Dave

I had a duplicate name in the list, yeah, makes sense!
Sorry for the misunderstanding here.

 

[NdNoviceHlp]

Thanks for posting your outcome. Have a nice day. Dave