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.

Good luck with your project.

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