Can this example be modified to create a true round robin draw? i.e. using a 6 team scenario, for any round there will be three games/matches, but obviously Team A could not compete against say Team B and Team C during that round as can occur using the above formula.
A round-robin tournament or all-play-all tournament is a type of tournament in which each contestant meets all other contestants in turn. Source: Wikipedia
This example is on a double round-robin basis, in which every team plays all others in its league once at home and once away randomly.
I am very close to a solution but I am stuck. This is as far as I get.
Problem: Sometimes a #num error is rendered in column D.
The formula selects a team randomly in column C and column D, with the use of each criterion.
Formula criteria in column C
COUNTIF($C$1:C1, $A$2:$A$5)<3. Each team can only play each opponent once, thus totally three times in column C.
COUNTIF($C$1:D1, $A$2:$A$5)<ROUNDUP(ROWS($C$1:D1)/2, 0). Each team can only play once in a round
Formula criteria in column D
COUNTIF($D$1:D1, $A$2:$A$5)<3. Each team can only play each opponent once, thus totally three times in column D.
COUNTIF($C$1:D1, $A$2:$A$5)<ROUNDUP(ROWS($C$1:D1)/2, 0. Each team can only play once in a round
COUNTIF(C2, $A$2:$A$5)=0. A team can not play against it self
ISERROR(MATCH($A$2:$A$5, IF(C2=$C$1:C1, $D$1:D1, ""), 0). There can not be any duplicate matches. Each team can only play once against each opponent "home" and once "away".
Sometimes the formula creates a true round-robin tournament, sometimes it creates a #num error in column D. This is because a random team in column C is not valid. It creates a duplicate match and returns a #num error in column D.
If I add another criteria in column C containing adjacent cell in column D a circular reference is created.