Various Approaches to Create a List of All Possible Matches Between Teams

Today, we will explore various methods to generate a list of all possible matchups between teams.

Method-1:

Formula in E3 cell is,

=LET(a,SORT(A3:A11),b,ROWS(a),c,SEQUENCE(b),d,TOROW(c),fx,LAMBDA(n,TOCOL(IF(c<d,n,x),3)),SORT(INDEX(a,HSTACK(fx(c),fx(d)))))

a -> variable name which holds the range A3:A11.

b -> holds the count of rows of range 'a' which is 9.

c -> sequence of numbers 1 to 9 in rows => {1;2;3;4;5;6;7;8;9}.

d -> sequence of numbers 1 to 9 in columns => {1,2,3,4,5,6,7,8,9}.

_s1 -> IF function creates a 2-dimensional array as illustrated below.

We intentionally used 1/0 in the value_if_false argument to generate errors. Afterward, we enclosed this array, which contains both numbers and errors, within the TOCOL function to convert it into a single-column array and remove the errors.

_s2 -> IF function creates a 2-dimensional array as illustrated below.

Again, TOCOL function helps us to convert this array into a single-column array and removes errors.

HSTACK function stacks _s1 and _s2 horizontally and INDEX function helps us to extract team names from variable 'a' based on the co-ordinates of _s1 and _s2.

Method-2:

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

List = List.RemoveNulls(List.TransformMany(Source[Clubs], (x) => Source[Clubs], (x,y) => if

x = y then null else {x,y})),

Result = Table.FromRows( List.Sort(List.Distinct(List, each List.Sort(_)), each _{0}) )

in

Result

The List.TransformMany function allows us to pair each team with 9 other teams. However, this results in pairing A C Milan with itself. To prevent this, we implemented a condition: if both teams are the same, return null; otherwise, return a list of the two teams.

This will result in 72 combinations, with 2 matches for each combination of teams but in different positions.

For example,

{A C Milan, Chelsea}

{Chelsea, A C Milan}

To remove this redundancy, we can sort the inner list and obtain unique lists.

The Table.FromRows function assists in creating a table from lists of lists.