Prepare the given matrix from problem table. Where X has been put in diagonal. In the grid, score need to be tabulated considering Column E as Team 1 and row headers as Team 2. Hence, when we document Chelsea vs Real Madrid, 1-7 will come in cell J4. Reverse of this i.e. 7-1 will go into cell G7 as cell G7 is for Real Madrid vs Chelsea.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 482
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Score Matrix for Team Matches with Power Query
Power Query solution 1 for Score Matrix for Team Matches, proposed by Konrad Gryczan, PhD:
let
Source = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Team 1", type text}, {"Team 2", type text}, {"Result", type text}}
),
#"Appended Query" = Table.Combine({#"Changed Type", Tabela2}),
#"Pivoted Column" = Table.Pivot(
#"Appended Query",
List.Distinct(#"Appended Query"[#"Team 2"]),
"Team 2",
"Result"
),
#"Reordered Columns" = Table.ReorderColumns(
#"Pivoted Column",
{"Team 1", "A C Milan", "Chelsea", "F C Barcelona", "Juventus", "Real Madrid"}
),
#"Replaced Value" = Table.ReplaceValue(
#"Reordered Columns",
null,
"X",
Replacer.ReplaceValue,
{"A C Milan", "Chelsea", "F C Barcelona", "Juventus", "Real Madrid"}
),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value", {{"Team 1", "Team"}})
in
#"Renamed Columns"Power Query solution 2 for Score Matrix for Team Matches, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Results = Table.TransformColumns(
Source,
{"Result", each [S = Text.Split(_, "-"), R = List.Reverse(S), C = Text.Combine(R, "-")][C]}
),
Rename = Table.RenameColumns(Results, {{"Team 1", "Team 2"}, {"Team 2", "Team 1"}}),
Pivot = Table.Pivot(
Source & Rename,
List.Sort(List.Distinct(Rename[Team 2])),
"Team 2",
"Result",
each _{0}? ?? "X"
),
Return = Table.RenameColumns(Pivot, {"Team 1", "Team"})
in
ReturnPower Query solution 3 for Score Matrix for Team Matches, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Teams = List.Sort(List.Distinct(Source[Team 1])),
Sol = List.Accumulate(
Teams,
Table.FromColumns({Teams}, {"Team"}),
(s, c) =>
Table.AddColumn(
s,
c,
each
let
a =
if [Team] = c then
"X"
else
let
b = List.Transform(Table.ToRows(Source), each List.RemoveLastN(_)),
c = try
Source[Result]{List.PositionOf(b, {[Team], c})}
otherwise
Text.Combine(
List.Reverse(
Text.Split(
Source
[Result]
{
List.PositionOf(
List.Transform(b, each List.Sort(_)),
List.Sort({[Team], c})
)
},
"-"
)
),
"-"
)
in
c
in
a
)
)
in
SolPower Query solution 4 for Score Matrix for Team Matches, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
L = List.Transform,
a = L(Table.ToRows(S), each L(_, each Text.Split(_, "-"))),
b = L(a, each List.Combine(_)),
c = L(b, each List.Reverse(_)),
d = b & L(c, each Text.Split(_{2} & "-" & _{3} & "-" & _{0} & "-" & _{1}, "-")),
e = Table.AddColumn(Table.FromRows(d), "R", each [Column3] & "-" & [Column4])[
[Column1],
[Column2],
[R]
],
f = Table.Pivot(e, List.Distinct(e[Column2]), "Column2", "R"),
g = List.Sort(List.Skip(Table.ColumnNames(f))),
h = Table.RenameColumns(Table.ReorderColumns(f, g), {"Column1", "Team"}),
Sol = Table.ReplaceValue(h, null, "X", Replacer.ReplaceValue, Table.ColumnNames(h))
in
SolPower Query solution 5 for Score Matrix for Team Matches, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
SplitRes = Table.SplitColumn(Source, "Result", Splitter.SplitTextByDelimiter("-"), {"R1", "R2"}),
Concat = Table.AddColumn(SplitRes, "Custom", each
[Team 2] & "_" & [Team 1] & "_" & [R2] & "-" & [R1])[[Custom]],
SplitAll = Table.SplitColumn(Concat, "Custom",
Splitter.SplitTextByDelimiter("_"),
{"Team 1", "Team 2", "Result"}),
Union = Source & SplitAll,
Pivot = Table.Pivot(Union, List.Distinct(Union[#"Team 2"]), "Team 2", "Result"),
Sorting = Table.ReorderColumns(Table.Sort(Pivot,{{"Team 1", Order.Ascending}}),
{List.First(Table.ColumnNames(Pivot))} &
List.Sort(List.RemoveFirstN(Table.ColumnNames(Pivot),1))),
ReplaceNull = Table.ReplaceValue(Sorting,null,"X",
Replacer.ReplaceValue, Sorting[Team 1]),
Result = Table.RenameColumns(ReplaceNull,{{"Team 1", "Team"}})
in
Result
🧙♂️🧙♂️🧙♂️
Solving the challenge of Score Matrix for Team Matches with Excel
Excel solution 1 for Score Matrix for Team Matches, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
A3:A12,
b,
B3:B12,
c,
C3:C12,
PIVOTBY(
VSTACK(
a,
b,
a
),
VSTACK(
b,
a,
a
),
VSTACK(
c,
TEXTAFTER(
c,
"-"
)&-TEXTSPLIT(
c,
"-"
),
IF(
a>"",
"X"
)
),
SINGLE,
,
0,
,
0
)
)Excel solution 2 for Score Matrix for Team Matches, proposed by John V.:
=LET(
a,
A3:A12,
b,
B3:B12,
c,
C3:C12,
v,
VSTACK,
PIVOTBY(
v(
a,
b,
a
),
v(
b,
a,
a
),
v(
c,
TEXTAFTER(
c,
"-"
)&-TEXTSPLIT(
c,
"-"
),
REPT(
"X",
a>""
)
),
SINGLE,
,
0,
,
0
)
)Excel solution 3 for Score Matrix for Team Matches, proposed by محمد حلمي:
=LET(
a,
A3:A12,
i,
SORT(
UNIQUE(
a
)
),
j,
TOROW(
i
),
V,
XLOOKUP(
j&i,
a&B3:B12,
C3:C12,
""
),
e,
V&IFERROR(
TRANSPOSE(
TEXTAFTER(
V,
"-"
)&-TEXTBEFORE(
V,
"-"
)
),
""
),
IF(
e="",
"X",
e
)
)Excel solution 4 for Score Matrix for Team Matches, proposed by Kris Jaganah:
=LET(
a,
A3:A12,
b,
B3:B12,
c,
C3:C12,
d,
SORT(
UNIQUE(
VSTACK(
a,
b
)
)
),
e,
TOROW(
d
),
f,
VSTACK(
HSTACK(
"Team",
e
),
HSTACK(
d,
IFNA(
XLOOKUP(
d&e,
a&b,
c
),
XLOOKUP(
e&d,
a&b,
TEXTAFTER(
c,
"-"
)&"-"&TEXTSPLIT(
c,
"-"
),
"X"
)
)
)
),
f
)Excel solution 5 for Score Matrix for Team Matches, proposed by Julian Poeltl:
=LET(
T,
A3:C12,
To,
TAKE(
T,
,
1
),
Tt,
CHOOSECOLS(
T,
2
),
R,
TAKE(
T,
,
-1
),
UT,
SORT(
UNIQUE(
To
)
),
TuT,
TRANSPOSE(
UT
),
VSTACK(
HSTACK(
"Team",
TuT
),
HSTACK(
UT,
IFERROR(
IFERROR(
XLOOKUP(
UT&TuT,
To&Tt,
R
),
LET(
X,
XLOOKUP(
UT&TuT,
Tt&To,
R
),
TEXTAFTER(
X,
"-"
)&"-"&TEXTBEFORE(
X,
"-"
)
)
),
"X"
)
)
)
)Excel solution 6 for Score Matrix for Team Matches, proposed by Timothée BLIOT:
=LET(
A,
A3:A12,
B,
B3:B12,
C,
C3:C12,
PIVOTBY(
VSTACK(
A,
B,
A
),
VSTACK(
B,
A,
A
),
VSTACK(
C,
TEXTAFTER(
C,
"-"
)&-TEXTBEFORE(
C,
"-"
),
REPLACE(
C,
1,
4,
"X"
)
),
SINGLE,
,
0,
,
0
)
)Excel solution 7 for Score Matrix for Team Matches, proposed by Oscar Mendez Roca Farell:
=LET(
a,
A3:A12,
u,
SORT(
UNIQUE(
a
)
),
t,
TOROW(
u
),
c,
C3:C12,
g,
"-",
d,
TEXTAFTER(
c,
g
)&g&TEXTBEFORE(
c,
g
),
VSTACK(
HSTACK(
"Team",
t
),
HSTACK(
u,
IFS(
u=t,
"X",
1,
XLOOKUP(
u&t,
a&B3:B12,
c,
d
)
)
)
)
)Excel solution 8 for Score Matrix for Team Matches, proposed by Duy Tùng:
=LET(
a,
PIVOTBY(
A3:A12,
B3:B12,
C3:C12,
SINGLE,
,
0,
,
0
),
b,
TRANSPOSE(
a
),
c,
IFNA(
IF(
a="",
TEXTAFTER(
b,
"-"
)&-TEXTBEFORE(
b,
"-"
),
a
),
"X"
),
IF(
TAKE(
c,
1
)&TAKE(
c,
,
1
)="XX",
"Team",
c
)
)Excel solution 9 for Score Matrix for Team Matches, proposed by Sunny Baggu:
=LET(
a,
A3:A12,
b,
B3:B12,
c,
C3:C12,
_t1,
SORT(
UNIQUE(
a
)
),
_t2,
TOROW(
SORT(
UNIQUE(
b
)
)
),
_t,
_t1 & _t2,
_v,
IFNA(
IFNA(
XLOOKUP(
_t,
a & b,
c
),
XLOOKUP(
_t,
b & a,
TEXTAFTER(
c,
"-"
) & "-" & TEXTBEFORE(
c,
"-"
)
)
),
"X"
),
VSTACK(
HSTACK(
"Team",
_t2
),
HSTACK(
_t1,
_v
)
)
)Excel solution 10 for Score Matrix for Team Matches, proposed by LEONARD OCHEA 🇷🇴:
=LET(
a,
A3:A12,
b,
B3:B12,
c,
C3:C12,
d,
SORT(
a
),
V,
VSTACK,
PIVOTBY(
V(
a,
b,
d
),
V(
b,
a,
d
),
V(
c,
TEXTAFTER(
c,
"-"
)&-TEXTSPLIT(
c,
"-"
),
IF(
d<>"",
"X"
)
),
SINGLE,
,
0,
,
0
)
)Excel solution 11 for Score Matrix for Team Matches, proposed by Hamidi Hamid:
=LET(
ut,
TRANSPOSE(
SORT(
UNIQUE(
A3:A12
)
)
),
w,
SORT(
UNIQUE(
A2:A12
)
),
q,
SORT(
UNIQUE(
A3:A12
)
),
HSTACK(
DROP(
VSTACK(
"Team",
w
),
-1
),
VSTACK(
ut,
LET(
w,
TEXTAFTER(
C3:C12,
"-",
)&"-"&TEXTBEFORE(
C3:C12,
"-",
),
IFERROR(
IF(
q=ut,
"x",
XLOOKUP(
q&ut,
B3:B12&A3:A12,
w
)
),
XLOOKUP(
ut&q,
B3:B12&A3:A12,
C3:C12,
""
)
)
)
)
)
)Excel solution 12 for Score Matrix for Team Matches, proposed by Asheesh Pahwa:
=LET(
_t1,
A3:A12,
_t2,
B3:B12,
r,
C3:C12,
tc,
SORT(
UNIQUE(
TOCOL(
HSTACK(
_t1,
_t2
)
)
)
),
tp,
TOROW(
tc
),
cn,
tc&"-"&tp,
cnt,
_t1&"-"&_t2,
cnr,
_t2&"-"&_t1,
ts,
TEXTAFTER(
r,
"-"
)&"-"&TEXTSPLIT(
r,
"-"
),
x,
IFNA(
XLOOKUP(
cn,
cnt,
r
),
XLOOKUP(
cn,
cnr,
ts,
"X"
)
),
& x
)Excel solution 13 for Score Matrix for Team Matches, proposed by Ankur Sharma:
=LET(
a,
A3:A12,
b,
B3:B12,
c,
C3:C12,
d,
SORT(
UNIQUE(
a
)
),
e,
MAP(
c,
LAMBDA(
z,
LET(
e,
TEXTSPLIT(
z,
"-"
),
TAKE(
e,
,
-1
) & "-" & TAKE(
e,
,
1
)
)
)
),
f,
TEXTSPLIT(
TEXTJOIN(
"$",
,
MAP(
d,
LAMBDA(
y,
TEXTJOIN(
"@",
,
MAP(
d,
LAMBDA(
x,
XLOOKUP(
y & x,
a & b,
c,
XLOOKUP(
y & x,
b & a,
e,
"X"
)
)
)
)
)
)
)
),
"@",
"$"
),
HSTACK(
VSTACK(
"Team",
d
),
VSTACK(
TRANSPOSE(
d
),
f
)
)
)Excel solution 14 for Score Matrix for Team Matches, proposed by Bilal Mahmoud kh.:
=LET(a,
HSTACK(
"team",
TOROW(
UNIQUE(
A2:A11
)
)
),
b,
HSTACK(UNIQUE(
A2:A11
),
TEXTSPLIT(TEXTJOIN("|",
,
MAP(UNIQUE(
A2:A11
),
LAMBDA(x,
TEXTJOIN("-",
,
MAP(UNIQUE(
A2:A11
),
LAMBDA(y,
IF(x=y,
"X",
FILTER(C2:C11,
((A2:A11=x)*(B2:B11=y))+((A2:A11=y)*(B2:B11=x)),
0)))))))),
"-",
"|")),
c,
VSTACK(
a,
b
),
c)Excel solution 15 for Score Matrix for Team Matches, proposed by Sandeep Marwal:
=LET(
range,
VSTACK(
HSTACK(
A2:A11&":"&B2:B11,
C2:C11
),
TEXTSPLIT(
TEXTJOIN(
"/",
,
MAP(
A2:A11,
B2:B11,
C2:C11,
LAMBDA(
a,
b,
c,
TEXTJOIN(
",",
,
b&":"&a,
TEXTAFTER(
c,
"-"
)&"-"&TEXTBEFORE(
c,
"-"
)
)
)
)
),
",",
"/"
)
),
team,
SORT(
UNIQUE(
A2:A11
)
),
teamtranpose,
TRANSPOSE(
team
),
output,
XLOOKUP(
team&":"&teamtranpose,
TAKE(
range,
,
1
),
TAKE(
range,
,
-1
),
"x"
),
HSTACK(
VSTACK(
"Team",
team
),
VSTACK(
teamtranpose,
output
)
)
)Excel solution 16 for Score Matrix for Team Matches, proposed by El Badlis Mohd Marzudin:
=LET(
_t,
A3:B12,
_u,
SORT(
UNIQUE(
TOCOL(
_t
)
)
),
_s,
C3:C12,
_s2,
MAP(
_s,
LAMBDA(
a,
TEXTJOIN(
"-",
,
SORTBY(
TEXTSPLIT(
a,
"-"
),
{2,
1}
)
)
)
),
_t2,
VSTACK(
HSTACK(
_t,
_s
),
HSTACK(
SORTBY(
_t,
{2,
1}
),
_s2
)
),
VSTACK(
HSTACK(
"Team",
TOROW(
_u
)
),
HSTACK(
_u,
XLOOKUP(
_u & TOROW(
_u
),
INDEX(
_t2,
,
1
) & INDEX(
_t2,
,
2
),
INDEX(
_t2,
,
3
),
"X"
)
)
)
)Excel solution 17 for Score Matrix for Team Matches, proposed by Burhan Cesur:
=LET(
l,
SORT(
UNIQUE(
A3:A12
)
),
a,
MAKEARRAY(
5,
5,
LAMBDA(
x,
y,
XLOOKUP(
INDEX(
l,
x,
)&INDEX(
TOROW(
l
),
,
y
),
A3:A12&B3:B12,
C3:C12,
XLOOKUP(
INDEX(
l,
x,
)&INDEX(
TOROW(
l
),
,
y
),
B3:B12&A3:A12,
TEXTAFTER(
C3:C12,
"-"
)&"-"&TEXTBEFORE(
C3:C12,
"-"
),
"x",
0
),
0
)
)
),
VSTACK(
HSTACK(
"TEAM",
TOROW(
l
)
),
HSTACK(
l,
a
)
)
)Excel solution 18 for Score Matrix for Team Matches, proposed by Mehmet Çiçek:
=LET(
a,
A3:A12,
b,
B3:B12,
c,
C3:C12,
d,
SORT(
UNIQUE(
a
)
),
e,
TOROW(
d
),
VSTACK(
HSTACK(
"Team",
e
),
HSTACK(
d,
IFNA(
XLOOKUP(
d&e,
a&b,
c
),
XLOOKUP(
d&e,
b&a,
TEXTAFTER(
c,
"-"
)&"-"&TEXTBEFORE(
c,
"-"
),
"X"
)
)
)
)
)Excel solution 19 for Score Matrix for Team Matches, proposed by Ricardo Alexis Domínguez Hernández:
=IFERROR(
IFERROR(
XLOOKUP(
$E3:$E7&F$2:J$2,
$A:$A&$B:$B,
$C:$C
),
MAP(
XLOOKUP(
E3:E7&F2:J2,
B:B&A:A,
C:C
),
LAMBDA(
x,
TEXTJOIN(
"-",
FALSE,
CHOOSECOLS(
TEXTSPLIT(
x,
"-"
),
2
),
CHOOSECOLS(
TEXTSPLIT(
x,
"-"
),
1
)
)
)
)
),
"X"
)Excel solution 20 for Score Matrix for Team Matches, proposed by Josh Brodrick:
=LET(
a,
A3:A12,
b,
B3:B12,
c,
C3:C12,
d,
UNIQUE(
TOCOL(
A3:B12
)
),
HSTACK(
VSTACK(
"Team",
SORT(
d
)
),
VSTACK(
TOROW(
SORT(
d
)
),
WRAPROWS(
CHOOSECOLS(
IFNA(
SORT(
HSTACK(
VSTACK(
a,
b,
d
),
VSTACK(
b,
a,
d
),
VSTACK(
c,
TEXTAFTER(
c,
"-"
)&"-"&TEXTBEFORE(
c,
"-"
)
)
),
{1,
2}
),
"X"
),
3
),
5
)
)
)
)Excel solution 21 for Score Matrix for Team Matches, proposed by Bevon Clarke:
=VSTACK(
HSTACK(
"Team",
TOROW(
SORT(
UNIQUE(
VSTACK(
A3:A12,
B3:B12
)
)
)
)
),
HSTACK(
SORT(
UNIQUE(
VSTACK(
A3:A12,
B3:B12
)
)
),
LET(
uniquteams,
SORT(
UNIQUE(
VSTACK(
A3:A12,
B3:B12
)
)
),
team1,
A3:A12,
team2,
B3:B12,
results,
C3:C12,
forward,
XLOOKUP(
uniquteams&TOROW(
uniquteams
),
team1&team2,
results,
""
),
reverse,
XLOOKUP(
uniquteams&TOROW(
uniquteams
),
team2&team1,
results,
""
),
IF(
IF(
forward<>"",
forward,
reverse
)="",
"X",
IF(
forward<>"",
forward,
reverse
)
)
)
)
)Excel solution 22 for Score Matrix for Team Matches, proposed by Tyler Cameron:
=LET(
z,
A3:A12,
y,
B3:B12,
x,
C3:C12,
a,
SORT(
UNIQUE(
z
)
),
b,
TOROW(
a&TOROW(
a
)
),
IFNA(
INDEX(
VSTACK(
x,
TEXT(
DATEVALUE(
x
),
"M-d"
)
),
WRAPCOLS(
XMATCH(
b,
VSTACK(
z&y,
y&z
)
),
5
)
),
"X"
)
)Excel solution 23 for Score Matrix for Team Matches, proposed by Tyler Cameron:
=LET(
z,
A3:A12,
y,
B3:B12,
x,
C3:C12,
a,
SORT(
UNIQUE(
z
)
),
b,
a&TOROW(
a
),
d,
MUNIT(
5
),
VSTACK(
HSTACK(
"Team",
TOROW(
a
)
),
HSTACK(
a,
IF(
d,
"X",
IFNA(
XLOOKUP(
b,
z&y,
x
),
XLOOKUP(
b,
y&z,
x
)
)
)
)
)
)Excel solution 24 for Score Matrix for Team Matches, proposed by Tyler Cameron:
=LET(
z,
A3:A12,
y,
B3:B12,
x,
C3:C12,
a,
SORT(
UNIQUE(
z
)
),
b,
a&TOROW(
a
),
e,
VSTACK(
HSTACK(
z&y,
x
),
HSTACK(
y&z,
TEXTAFTER(
x,
"-"
)&"-"&TEXTBEFORE(
x,
"-"
)
)
),
VSTACK(
HSTACK(
"Team",
TOROW(
a
)
),
HSTACK(
a,
XLOOKUP(
b,
CHOOSECOLS(
e,
1
),
CHOOSECOLS(
e,
2
),
"x"
)
)
)
)Excel solution 25 for Score Matrix for Team Matches, proposed by Pawan Keswani:
=IF($E3=F$2,"X",IF(TEXT(SUMPRODUCT($C$3:$C$12*($A$3:$A$12=$E3)*($B$3:$B$12=F$2)),"d-m")="0-1",TEXT(SUMPRODUCT($C$3:$C$12*($A$3:$A$12=F$2)*($B$3:$B$12=$E3)),"m-d"),TEXT(SUMPRODUCT($C$3:$C$12*($A$3:$A$12=$E3)*($B$3:$B$12=F$2)),"d-m")))Solving the challenge of Score Matrix for Team Matches with Python
Python solution 1 for Score Matrix for Team Matches, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = '482 Soccer Result Grid.xlsx'
input = pd.read_excel(path, usecols="A:C", skiprows=1, nrows=10)
test = pd.read_excel(path, usecols="E:J", skiprows=1, nrows=5)
rev_input = input.copy()
rev_input['Team 1'], rev_input['Team 2'] = input['Team 2'], input['Team 1']
rev_input['Result'] = input['Result'].str.split('-').str[::-1].str.join('-')
result = pd.concat([input, rev_input], ignore_index=True)
result = result.pivot(index='Team 1', columns='Team 2', values='Result')
.fillna('X')
.reset_index()
.rename(columns={'Team 1': 'Team'})
result.columns.name = None
print(result.equals(test)) # True