List the matches where goal difference is same.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 290
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Matches With Equal Goal Difference with Power Query
Power Query solution 1 for Matches With Equal Goal Difference, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Table.AddColumn(Source, "Goal Diff", each Number.Abs(Expression.Evaluate([Result]))),
"Goal Diff",
{"Match", each Text.Combine([Match], ", ")}
),
Ans = Table.SelectColumns(
Table.SelectRows(Group, each Text.Contains([Match], ",")),
List.Reverse(Table.ColumnNames(Group))
)
in
AnsPower Query solution 2 for Matches With Equal Goal Difference, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
C = List.Transform(Table.ToRows(Source), each _ & {Number.Abs(Expression.Evaluate(_{3}))}),
S = Table.FromRows(
List.Accumulate(
List.Accumulate(
List.Zip(C){4},
{},
(s, c) =>
if List.Count(List.PositionOf(List.Zip(C){4}, c, 2)) > 1 then List.Union({s, {c}}) else s
),
{},
(s, c) =>
s & {{Text.Combine(List.Transform(List.Select(C, each _{4} = c), each _{0}), ", "), c}}
),
{"Match", "Goal Diff"}
)
in
SPower Query solution 3 for Matches With Equal Goal Difference, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"Goal Diff",
each
let
a = List.Transform(Text.Split([Result], "-"), Number.From),
b = Number.Abs(a{0} - a{1})
in
b
),
Sol = Table.SelectRows(
Table.Group(
Result,
{"Goal Diff"},
{{"Count", each List.Count([Result])}, {"Match", each Text.Combine([Match], ", ")}}
),
each [Count] > 1
)[[Match], [Goal Diff]]
in
SolPower Query solution 4 for Matches With Equal Goal Difference, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
tab = Table.TransformColumns(Fonte, {{"Result", each Number.Abs(Expression.Evaluate(_))}}),
res = Table.SelectRows(
Table.Group(
tab,
{"Result"},
{{"Match", each Text.Combine([Match], ", ")}, {"Count", each List.Count([Result]) > 1}}
),
each [Count] = true
)[[Match], [Result]]
in
resPower Query solution 5 for Matches With Equal Goal Difference, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = Table.SplitColumn(
Origen,
"Result",
Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),
{"Result.1", "Result.2"}
),
b = Table.AddColumn(
a,
"Goal Diff",
each Number.Abs(Number.From([Result.1]) - Number.From([Result.2]))
),
c = Table.Group(b, {"Goal Diff"}, {{"All", each _}}),
d = Table.AddColumn(
c,
"Repeated",
each Table.AddColumn([All], "Rep", (x) => Table.RowCount([All]))
)[[Repeated], [Goal Diff]],
e = Table.SelectRows(
Table.ExpandTableColumn(d, "Repeated", {"Match", "Rep"}, {"Match", "Rep"}),
each [Rep] >= 2
)[[Match], [Goal Diff]],
f = Table.SelectColumns(
Table.Group(e, {"Goal Diff"}, {{"Match", each Text.Combine([Match], ", ")}}),
{"Match", "Goal Diff"}
)
in
fPower Query solution 6 for Matches With Equal Goal Difference, proposed by Szabolcs Phraner:
let
Source = Excel.CurrentWorkbook(){[Name = "Table"]}[Content],
Goal_Diff = Table.AddColumn(Source, "Goal Diff", each Number.Abs(Expression.Evaluate([Result]))),
Group_TextCombine = Table.Group(
Goal_Diff,
{"Goal Diff"},
{{"Match", each Text.Combine([Match], ", ")}}
),
Filter = Table.SelectRows(Group_TextCombine, each Text.Contains([Match], ","))
in
FilterSolving the challenge of Matches With Equal Goal Difference with Excel
Excel solution 1 for Matches With Equal Goal Difference, proposed by Bo Rydobon 🇹🇭:
=LET(
d,
ABS(
TEXTSPLIT(
D2:D11,
"-"
)-TEXTAFTER(
D2:D11,
"-"
)
),
f,
FILTER(
d,
DROP(
FREQUENCY(
d,
d
),
-1
)>1
),
HSTACK(
MAP(
f,
LAMBDA(
x,
TEXTJOIN(
", ",
,
REPT(
A2:A11,
d=x
)
)
)
),
f
)
)Excel solution 2 for Matches With Equal Goal Difference, proposed by John V.:
=LET(
r,
D2:D11,
d,
ABS(
TEXTAFTER(
r,
"-"
)-TEXTSPLIT(
r,
"-"
)
),
m,
MAP(
d,
LAMBDA(
x,
IF(
SUM(
N(
d=x
)
)>1,
ARRAYTOTEXT(
FILTER(
A2:A11,
d=x
)
),
)
)
),
UNIQUE(
FILTER(
HSTACK(
m,
d
),
m>0
)
)
)Excel solution 3 for Matches With Equal Goal Difference, proposed by محمد حلمي:
=LET(
d,D2:D11,
r,ABS(TEXTAFTER(d,"-")-TEXTSPLIT(d,,"-")),
v,DROP(FREQUENCY(r,r),-1),
X,FILTER(r,v>1),
HSTACK(MAP(X,LAMBDA(i,
TEXTJOIN(", ",,REPT(A2:A11,i=r)))),X))Excel solution 4 for Matches With Equal Goal Difference, proposed by Kris Jaganah:
=LET(a,
A2:D11,
b,
TAKE(
a,
,
1
),
c,
TAKE(
a,
,
-1
),
d,
TEXTBEFORE(
c,
"-"
),
e,
ABS(
d+TEXTAFTER(
c,
d
)
),
f,
MAP(e,
LAMBDA(x,
SUM(--(x=e)))),
g,
UNIQUE(
FILTER(
e,
f>1
)
),
HSTACK(
MAP(
g,
LAMBDA(
y,
ARRAYTOTEXT(
FILTER(
b,
e=y
)
)
)
),
g
))Excel solution 5 for Matches With Equal Goal Difference, proposed by Timothée BLIOT:
=LET(A,
ABS(
MAP(
SUBSTITUTE(
D2:D11,
"-",
"*-"
),
LAMBDA(
x,
SUM(
TEXTSPLIT(
x,
"*"
)*1
)
)
)
),
B,
MAP(A,
LAMBDA(x,
SUM(--(x=A)))),
C,
UNIQUE(
FILTER(
A,
B>1
)
),
HSTACK(
MAP(
C,
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
A2:A11,
A=x
)
)
)
),
C
))Excel solution 6 for Matches With Equal Goal Difference, proposed by Hussein SATOUR:
=LET(m,
A2:A11,
r,
MAP(
D2:D11,
LAMBDA(
x,
ABS(
MMULT(
-TEXTSPLIT(
x,
"-"
),
{1;-1}
)
)
)
),
TEXTSPLIT(TEXTJOIN("|",
,
UNIQUE(MAP(r,
LAMBDA(y,
IF(SUM((r=y)*1)>1,
TEXTJOIN(
", ",
,
FILTER(
m,
r=y
)
) &"/"&y,
""))))),
"/",
"|"))Excel solution 7 for Matches With Equal Goal Difference, proposed by Oscar Mendez Roca Farell:
=LET(_r,
D2:D11,
_t,
ABS(
TEXTBEFORE(
_r,
"-"
)-TEXTAFTER(
_r,
"-"
)
),
_u,
UNIQUE(
_t
),
_f,
TOROW(_u/(FREQUENCY(
_t,
_u
)>1),
2),
TRANSPOSE(
VSTACK(
BYCOL(
REPT(
A2:A11,
_t=_f
),
LAMBDA(
c,
TEXTJOIN(
", ",
,
c
)
)
),
_f
)
))Excel solution 8 for Matches With Equal Goal Difference, proposed by Sunny Baggu:
=LET(
_diff,
ABS(
TEXTBEFORE(
D2:D11,
"-"
) - TEXTAFTER(
D2:D11,
"-"
)
),
_cnt,
MAP(
_diff,
LAMBDA(
a,
SUM(
N(
_diff = a
)
)
)
),
_m1,
FILTER(
A2:A11,
_cnt > 1
),
_m1no,
FILTER(
_diff,
_cnt > 1
),
_udiff,
UNIQUE(
_m1no
),
_match,
MAP(
_udiff,
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
_m1,
_m1no = x
)
)
)
),
HSTACK(
_match,
_udiff
)
)Excel solution 9 for Matches With Equal Goal Difference, proposed by LEONARD OCHEA 🇷🇴:
=LET(m,A2:A11,r,D2:D11,d,ABS(TEXTBEFORE(r,"-")-TEXTAFTER(r,"-")),u,UNIQUE(d),TEXTSPLIT(TEXTJOIN("/",,TOCOL(MAP(u,LAMBDA(a,LET(f,FILTER(m,d=a),IF(COUNTA(f)>1,ARRAYTOTEXT(f)&"@"&a,NA())))),3)),"@","/"))Excel solution 10 for Matches With Equal Goal Difference, proposed by Abdallah Ally:
=LET(
a,
DROP(
REDUCE(
"",
D2:D11,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
b,
--TEXTSPLIT(
y,
"-"
),
ABS(
TAKE(
b,
,
1
)-TAKE(
b,
,
-1
)
)
)
)
)
),
1
),
UNIQUE(
DROP(
REDUCE(
"",
a,
LAMBDA(
x,
y,
IF(
COUNTA(
FILTER(
a,
a=y
)
)>1,
VSTACK(
x,
HSTACK(
TEXTJOIN(
", ",
TRUE,
FILTER(
A2:A11,
a=y
)
),
y
)
),
x
)
)
),
1
)
)
)Excel solution 11 for Matches With Equal Goal Difference, proposed by Abdallah Ally:
=LET(
a,
ABS(
TEXTAFTER(
D2:D11,
"-"
)-TEXTBEFORE(
D2:D11,
"-"
)
),
UNIQUE(
DROP(
REDUCE(
"",
a,
LAMBDA(
x,
y,
IF(
COUNTA(
FILTER(
a,
a=y
)
)>1,
VSTACK(
x,
HSTACK(
ARRAYTOTEXT(
FILTER(
A2:A11,
a=y
)
),
y
)
),
x
)
)
),
1
)
)
)Excel solution 12 for Matches With Equal Goal Difference, proposed by Asheesh Pahwa:
=LET(a,
A2:A11,
b,
D2:D11,
c,
TEXTSPLIT(
b,
,
"-"
),
d,
--TEXTARER(
b,
"-"
),
e,
ABS(
c-d
),
f,
MAP(e,
LAMBDA(x,
SUM((e=x)*1))),
g,
UNIQUE(
FILTER(
e,
f>1
)
),
HSTACK(
MAP(
g,
LAMBDA(
y,
TEXTJOIN(
", ",
,
FILTER(
a,
e=y
)
)
)
),
g
))Excel solution 13 for Matches With Equal Goal Difference, proposed by Pieter de Bruijn:
=LET(
t,
D2:D11,
l,
--TEXTBEFORE(
t,
"-"
),
r,
--TEXTAFTER(
t,
"-"
),
d,
IF(
l>r,
l-r,
r-l
),
c,
MMULT(
N(
TOROW(
d
)=d
),
SEQUENCE(
ROWS(
d
),
,
,
0
)
)>1,
f,
FILTER(
d,
c
),
m,
FILTER(
A2:A11,
c
),
u,
UNIQUE(
f
),
HSTACK(
MAP(
u,
LAMBDA(
x,
ARRAYTOTEXT(
TOROW(
IFS(
f=x,
m
& ),
2
)
)
)
),
u
)
)Excel solution 14 for Matches With Equal Goal Difference, proposed by Nicolas Micot:
=LET(_goalDif;ABS(TEXTE.AVANT(D4:D13;"-")-TEXTE.APRES(D4:D13;"-"));
_unique;UNIQUE(_goalDif);
_compte;BYROW(_unique;LAMBDA(l_unique;SOMME(SI(_goalDif=l_unique;1;0))));
_compteSupA1;FILTRE(_unique;_compte>1);
ASSEMB.H(BYROW(_compteSupA1;LAMBDA(l_goalDif;JOINDRE.TEXTE(", ";VRAI;FILTRE(A4:A13;_goalDif=l_goalDif))));_compteSupA1))Excel solution 15 for Matches With Equal Goal Difference, proposed by Ziad A.:
=LET(
d,
ARRAYFORMULA(
ABS(
MMULT(
SPLIT(
D2:D11,
"-"
),
{1;-1}
)
)
),
MAP(
UNIQUE(
FILTER(
d,
COUNTIF(
d,
d
)>1
)
),
LAMBDA(
_,
{JOIN(
", ",
FILTER(
A2:A11,
d=_
)
),
_}
)
)
)
We first calculate the differences.
ABS(
MMULT(
SPLIT(
D2:D11,
"-"
),
{1;-1}
)
)
(This is an array formula so it has to be array-enabled)
Then we iterate over each UNIQUE value of that array (which we will call "d") that occurs more than once.
MAP(
UNIQUE(
FILTER(
d,
COUNTIF(
d,
d
)>1
)
),
[...]
)
And we FILTER the corresponding values in col A,
JOIN the results together and use array literals to horizontally stack them with their corresponding difference.
{JOIN(
", ",
FILTER(
A2:A11,
d=_
)
)Excel solution 16 for Matches With Equal Goal Difference, proposed by Giorgi Goderdzishvili:
=LET(
rs,
D2:D11,
diff,
ABS(
TEXTBEFORE(
rs,
"-"
)-TEXTAFTER(
rs,
"-"
)
),
mt,
"Match "&SEQUENCE(
ROWS(
rs
)
),
grp,
UNIQUE(
MAP(
diff,
LAMBDA(
x,
TEXTJOIN(
",",
TRUE,
IF(
x=diff,
mt,
""
)
)
)
)
),
flt,
FILTER(
grp,
ISNUMBER(
FIND(
",",
grp
)
)
),
HSTACK(
flt,
XLOOKUP(
TEXTBEFORE(
flt,
","
),
mt,
diff
)
)
)Excel solution 17 for Matches With Equal Goal Difference, proposed by Abdelrahman Omer, MBA, PMP:
=LET(a,
D2:D11,
f,
A2:A11,
b,
ABS(
TEXTBEFORE(
a,
"-"
)-TEXTAFTER(
a,
"-"
)
),
d,
BYROW((TRANSPOSE(
b
)=b)*1,
LAMBDA(
x,
SUM(
x
)
)),
e,
(d>1)*d,
g,
FILTER(
f,
e>0
),
h,
FILTER(
b,
d>1
),
HSTACK(
MAP(
UNIQUE(
h
),
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
g,
h=x
)
)
)
),
UNIQUE(
h
)
))Excel solution 18 for Matches With Equal Goal Difference, proposed by Daniel Garzia:
=LET(
r,
D2:D11,
d,
ABS(
TEXTBEFORE(
r,
"-"
)-TEXTAFTER(
r,
"-"
)
),
f,
FILTER(
d,
DROP(
FREQUENCY(
d,
d
),
-1
)>1
),
HSTACK(
MAP(
f,
LAMBDA(
x,
TEXTJOIN(
", ",
,
IF(
d=x,
A2:A11,
""
)
)
)
),
f
)
)Excel solution 19 for Matches With Equal Goal Difference, proposed by Diarmuid Early:
=LET(mat,A2:A11,
res,D2:D11,
gd,ABS(TEXTBEFORE(res,"-")-TEXTAFTER(res,"-")),
rws,SEQUENCE(ROWS(res)),
repGD,UNIQUE(FILTER(gd,XMATCH(gd,gd)<>rws)),
matList,MAP(repGD,LAMBDA(gdif,
TEXTJOIN(", ",,FILTER(mat,gd=gdif)))),
HSTACK(matList,repGD))Excel solution 20 for Matches With Equal Goal Difference, proposed by samir tobeil:
=LET(s,MAP(D2:D11,LAMBDA(x,ABS(SUM({1,-1}*TEXTSPLIT(x,"-"))))),
e,FILTER(s,DROP(FREQUENCY(s,s),-1)>1),HSTACK(MAP(e,LAMBDA(t,
TEXTJOIN(",",,FILTER(A2:A11,s=t)))),UNIQUE(e)))Solving the challenge of Matches With Equal Goal Difference with Python in Excel
Python in Excel solution 1 for Matches With Equal Goal Difference, proposed by Bo Rydobon 🇹🇭:
df=xl("A1:D11", headers=True)
df['Goal Diff']=df.Result.apply(eval).abs()
gr=df.groupby(['Goal Diff']).Match.apply(lambda x: ', '.join(x)).reset_index().sort_values(by=['Goal Diff'], ascending=0)
gr[gr.Match.str.find(',')>0].set_index('Match').reset_index()
Python in Excel solution 2 for Matches With Equal Goal Difference, proposed by John V.:
Hi everyone!
One (Python) option could be:
df = xl("A1:D11", headers=True)
df["Goal Diff"] = df.Result.apply(lambda x: abs(int(x.split("-")[0]) - int(x.split("-")[1])))
g = df.groupby('Goal Diff')['Match'].apply(lambda x: ", ".join(x) if len(x) > 1 else None).dropna().reset_index()
g = g[["Match", "Goal Diff"]]
Blessings!
Solving the challenge of Matches With Equal Goal Difference with R
R solution 1 for Matches With Equal Goal Difference, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Teams Goal Diff is Same.xlsx", range = "A1:D11")
test = read_excel("Teams Goal Diff is Same.xlsx", range = "F2:G4")
result = input %>%
separate(Result, into = c("home", "away")) %>%
mutate(`Goal Diff` = as.character(abs(as.numeric(home)-as.numeric(away)))) %>%
select(Match, `Goal Diff`) %>%
group_by(`Goal Diff`) %>%
mutate(match_count = n_distinct(Match)) %>%
filter(match_count > 1) %>%
summarise(Match = paste0(Match, collapse = ", ")) %>%
select(Match, `Goal Diff`) %>%
arrange(desc(`Goal Diff`))
&&