Microsoft Excel - 6 golfers playing 6 rounds of golf over 6 days

Larry
John
Peter
Brian
Mike
Trevor

Playing in 2 groups of 3
what is best way so that players are not playing with same 2 golfers every day

This is an Excel forum, are you looking for a formula or some VBA?  With six players in groups of three, there are only 9 combinations.  Do you want to be able to expand this to a larger group?

I am not sure of the best way but here is what i did using conditional formatting I set two groups up with different colors so you could create you own combinations and look for ways you want to split it up.

I found that there is no way you can only play with 2 of the others only twice at some point one or two players will have to be part of a team where they are with one other person 3 times. but never the same third person.

here is my example I hope this helps

Don
golf.zip

 Thanks a lot for that I will have a look and see if that will sort out my permutations.

Hi Brian,

Here I design some very draft...but quite complicate...just for light up some idea for any further development...

I use looping in Vba...try to give same logic..when we select the name manually...
I haven't try if it can be applied to other scenario...I think many adjustment needed to be amended in order to make it flexible for all scenario...let this be 1st step to start...
I list out the possibilities of group matching by number 1-6 then look up to find the name...see more in attachment!!!

• Here is the code
  • Sub GroupGolfer()
    lastList = WorksheetFunction.Max(Range("B:B"))
    frtGrpRow = Range("F" & Rows.Count).End(xlUp).Row + 1
    lastNmFrtGrp = Range("C1").Value
    nxtNmFrtGrp = Range("C1").Value
    Range("H2").Value = lastNmFrtGrp
    roundCnt = 0
    Do Until Range("H" & frtGrpRow).Value = ""
    For y = 6 To 8
        Cells(frtGrpRow, y).Select
        For x = 1 To lastList
        If ActiveCell.Value = "" Then
        If WorksheetFunction.CountIf(Range("F" & frtGrpRow & ":H" & frtGrpRow), x) = 0 Then ActiveCell.Value = x
        End If
        Next x
        Next y
        If lastNmFrtGrp < lastList Then
        lastNmFrtGrp = ActiveCell.Value + 1
        Else
        roundCnt = roundCnt + 1
        lastNmFrtGrp = nxtNmFrtGrp + roundCnt
        End If
        If ActiveCell.Value = lastNmFrtGrp Then
        MsgBox "First Group Done"
        For i = 2 To Range("F" & Rows.Count).End(xlUp).Row
            For Z = 12 To 10 Step -1
            Cells(i, Z).Select
                For x = 1 To lastList
                If WorksheetFunction.CountIf(Range("F" & i & ":L" & i), x) = 0 Then ActiveCell.Value = x
                Next x
            Next Z
        Next i
        MsgBox "Done"
        Exit Sub
        Else
        ActiveCell.Offset(1, 0).Value = lastNmFrtGrp
        If ActiveCell.Offset(1, 0).Value < ActiveCell.Value Then ndNmFrtGrp = ActiveCell.Offset(0, -1).Value + 1
        ActiveCell.Offset(1, -1).Value = ndNmFrtGrp
        frtGrpRow = Range("F" & Rows.Count).End(xlUp).Row + 1
        End If
    Loop
    End Sub
• Sample file for you...-->Vba_GolfArrangeGrp.zip

Hope you like it, let me know your feedback.

Pichart Y.

It might work better if you had 7 ppl in your rotation, when is tee time. :)

Hi Brian,
I had a similar problem but playing over only five days.  Also for player numbers ranging from 6 to 12.

Basically, what I did was to set up an array of combinations and then randomise the order of the players and then match the players with the position in the array.

An array for your case would be something like this.

AAAAAA
BCDEFC
CDEFBE
DEFBCB
EFBCDD
FBCDEF

I haven't done the sums to check on the evenness of the draw but eye-balling it it looks OK.

To optimise my larger arrays, I used a spreadsheet and just used brute force to come up with the most even distribution of partnerships and playing with the maximum number of different people.

Good luck with your project.

Harry

Hello again Brian

I fiddled around and came up with this matrix which seems to be a bit more even that the one that i put up before.



It does have the disadvantage that A never plays with B but all others play at least two or three times.  If everybody plays at least once this seems to be compensated by at least a pair playing four times.  The following is such a matrix.



Hope the games go well.

Harry