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

We have already covered two approaches to address this requirement; this will be Part 2 of the series.

This method is inspired by solution from Rick Rothstein in our previous post.

Method-1:

Formula in E3 cell is,

=LET(d,SORT(A3:A11),r,SEQUENCE(ROWS(d)),c,TOROW(r),TEXTSPLIT(TEXTAFTER(TOCOL(

IF(c>r,"|"&d&"|"&TOROW(d),z),2),"|",{1,2}),"|"))

d -> contains the sorted range A3:A11.

r -> a sequence of numbers corresponding to the number of rows in 'd'.

{1;2;3;4;5;6;7;8;9}.

c -> the transposed form of 'r'.

{1,2,3,4,5,6,7,8,9}.

let's get into calculation part,

Construction in value_if_true argument inside IF function is,

"|"&d&"|"&TOROW(d)

and this delivers 9 row and 9 column arrays as shown below,

The logical test 'c>r' within the IF function allows us to select only the green-colored arrays, thereby removing combinations such as

"|A C Milan|A C Milan" in cell B17,

"|Chelsea|A C Milan" in cell B18, because the combination |A C Milan|Chelsea is present in cell C17.

The 'z' in the value_if_false argument is used to fill errors in cells that are not green.

The TOCOL function will exclude errors and transform this array into a 36-row by 1-column array.

The TEXTAFTER function allows us to split using "|" as the delimiter, and the {1,2} in the instance number argument helps us obtain a 2-column result, as demonstrated below.

TEXTSPLIT function will split the first column by delimiter "|" to get the result as we expected.

Method-2:

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

This is the streamlined version of Method-1 from our earlier post, utilizing LAMBDA.

Method-3:

let

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

Result = [Lst = List.Sort(Source[Clubs]), fin =

Table.FromRows(List.Combine(List.Transform(Lst, (f) => List.Transform(

List.Skip(Lst, List.PositionOf(Lst,f) + 1), (x) => {f,x}))))][fin]

in

Result

This is the simplified version of Method-2 from our earlier post.