Here's one way to do it. Not a simple formula, but
a VBA solution.
1. From the sheet enter the VBA editor with <Alt><F11>
2. Choose Insert > Module
3. Copy and paste the code below to the righthand window.
4. Return to the sheet with <Alt><F11> and save the workbook.
5. Enter the names of the 10 teams in e.g. B2:B11
6. Select B2:B11 and run the macro with Tools > Macro > Macros,
or create a button to call the macro.
Sub RoundRobinTournament()
'Leo Heuser, 28-10-2003
Dim Counter1 As Long
Dim Counter2 As Long
Dim Dummy As Variant
Dim ListStartCell As Range
Dim Participants As Variant
Dim ParticipantsRange As Range
Dim RoundRange As Range
Dim SavePlayer1 As Variant
Dim TestCollection As New Collection
On Error GoTo Finito
Set ListStartCell = ActiveSheet.Range("D7")
Participants = Selection.Value
If Selection.Cells.Count = 1 Then GoTo Finito
On Error Resume Next
For Counter1 = LBound(Participants, 1) To UBound(Participants, 1)
TestCollection.Add Item:=0, key:=CStr(Participants(Counter1, 1))
If Err.Number > 0 Then
MsgBox "Duplicate names are not allowed."
GoTo Finito
End If
Next Counter1
If UBound(Participants, 1) Mod 2 = 1 Then
Participants = Selection. _
Resize(Selection.Rows.Count + 1, 1)
Participants(UBound(Participants, 1), 1) = "Sitting out"
End If
Set ParticipantsRange = ListStartCell. _
Resize(1, UBound(Participants, 1))
ParticipantsRange.Offset(0, -1). _
Resize(1000, 200).ClearContents
ParticipantsRange.Value = _
Application.WorksheetFunction.Transpose(Participants)
Set RoundRange = ParticipantsRange.Offset(2, 0)
For Counter1 = 1 To UBound(Participants, 1) - 1
For Counter2 = 1 To UBound(Participants, 1) / 2
RoundRange.Offset(0, -1).Cells(1, 1).Value = _
"Round " & Counter1
Dummy = _
Application.WorksheetFunction. _
Match(Participants(Counter2, 1), ParticipantsRange, 0)
RoundRange.Cells(1, Dummy).Value = _
Participants(UBound(Participants, 1) + 1 - Counter2, 1)
Dummy = _
Application.WorksheetFunction. _
Match(Participants(UBound(Participants, 1) + 1 - _
Counter2, 1), ParticipantsRange, 0)
RoundRange.Cells(1, Dummy).Value = _
Participants(Counter2, 1)
Next Counter2
SavePlayer1 = Participants(1, 1)
For Counter2 = 1 To UBound(Participants, 1) - 2
Participants(Counter2, 1) = Participants(Counter2 + 1, 1)
Next Counter2
Participants(UBound(Participants, 1) - 1, 1) = SavePlayer1
Set RoundRange = RoundRange.Offset(1, 0)
Next Counter1
Finito:
On Error GoTo 0
End Sub
