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

Asked By Brian Gillan on 24-May-12 07:53 AM
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
wally eye replied to Brian Gillan on 24-May-12 05:20 PM
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?
Donald Ross replied to Brian Gillan on 24-May-12 10:24 PM
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
Brian Gillan replied to Donald Ross on 25-May-12 07:09 AM
Thanks a lot for that I will have a look and see if that will sort out my permutations.
Pichart Y. replied to Brian Gillan on 26-May-12 03:51 AM
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.
Donald Ross replied to Brian Gillan on 26-May-12 07:50 AM
It might work better if you had 7 ppl in your rotation, when is tee time. :)
Harry Boughen replied to Brian Gillan on 29-May-12 03:32 AM
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.

Harry
Harry Boughen replied to Brian Gillan on 30-May-12 01:00 AM
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