Golf paring over multiple tournaments

In column A, List the players.
In column B, type =rand() and copy down
In column C, type =RANK(B3,B$3:B$52)
In column D, list the team nr, ie. D1 =1, D2=1, D3=1, D4=1, D5=2, D6=2, etc.

Copy columns B:D accross until you have 12 tournaments.

This is then a completely randomized selection.

As a matter of fact, I did some minor test and after several repetitions - there are always some players who during these 12 rounds play 4 or 5 times within one flight with the same player.

See Attached test file - interesting part is table in AP2:CN52 with conditional formatting showing where such situations occur. and summary left to it - sometimes there are sitiutions when the same player is assigned even more frequently to the other sometimes 5 or even 6 times (half of tournaments!). On the other hand vast majority of pairs palyerA and Player B are in the same flight only once or they never meet on green.

@provoersel2: have you chance to look at some macro solutions with more systematic than random assignment - for instance here: http://www.excelforum.com/excel-form...-schedule.html

Hm, but with fully randomized assigning, one could easily end up with some players meet other more frequent than average (and may be more than acceptable 2-3 times), while some do not meet at all.

one tiny issue - golf flights (3 or 4 players) - always x of 3players y of 4players or again - random division?

Hm, third in a row. but ...
I did some "checked on-line for quality" random assignment the above data layout.
It is not quick, because it heavily depends on cells and formulas.
But it takes into account prevoius assignments and tries to find a bood one for given tournament. See the code (to be copied into standard module Alt-F11 and then Alt_I, M);

Sub simple_mostly_formula_solution()
Dim round As Integer, allowed_max As Integer, i As Integer
Dim j As Integer, counter As Integer

'setup stage
Randomize
allowed_max = 1
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For i = 1 To 12 'prepare space - every second column starting E
  Cells(2, 2 * i + 3).Resize(50, 1).ClearContents
Next i

'main loop
For i = 1 To 12 'tournaments
  Application.StatusBar = "working on tournament " & i
  Do 'try several times different random assignments
    counter = counter + 1
    If counter > 25 * allowed_max Then 'but loosen limits after several attempts
      counter = 0
      allowed_max = allowed_max + 1
    End If
    
    For j = 1 To 50 'players
      Cells(j + 2, 2 * i + 3).Value = Rnd()
    Next j
    Application.Calculate 'fire the formulas in the sheet
  Loop Until Application.WorksheetFunction.Max(Range("AQ3:CN52")) <= allowed_max
Next i

'finishing
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.StatusBar = ""
End Sub

I did several test runs (observe status bar for the progress information) and only once there were any 4-times with the same player situations. The rest are mostly 0,1,2 and some ca. 30 with 3-times.

The quality shall be even beter if in

counter > 25 * allowed_max

some higher critical counter value is used, but what is reasonable limit here depends on the computing power of machine used, and user patience :-P. 25 shall be few seconds, may be some half minute on slower processor.