Randomize a game schedule

[Kent]

Hi

I have a list in column A with 14 team names
How can i create a game schedule so that each team will meet each other
twice (Home and away) in column C and D?



Thanks for any help

Kent

 

[Tom Ogilvy]

This should do it:

Sub RoundRobin14()
Dim varr As Variant
Dim varr1(1 To 7, 1 To 2)
Range("C").ClearContents
varr = Worksheets("sheet1").Range("A1:A14")
For i = 1 To 13 * 8 Step 8
rotate varr
Split1 varr, varr1
Cells(i, 3).Resize(7, 2).Value = varr1
Next
Set rng = Cells(Rows.Count, 3).End(xlUp)(2)
Range(Range("C1"), rng).Copy rng.Offset(1, 1)
Range(Range("D1"), rng.Offset(0, 1)).Copy rng.Offset(1, 0)
End Sub

Sub Split1(arr1, arr2)
l = -1
For i = 1 To 14
If i <= 7 Then
j = i
ii = i
k = 1
Else
l = l + 1
ii = 14 - l
j = i - 7
k = 2
End If
arr2(j, k) = arr1(ii, 1)
Next
End Sub

Sub rotate(arr)
arr1 = arr
For i = 2 To 13
arr(i, 1) = arr1(i + 1, 1)
Next
arr(14, 1) = arr1(2, 1)
End Sub