List the Teams which have never lost even a single game.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 280
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Teams With No Losses with Power Query
Power Query solution 1 for Teams With No Losses, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
up = Table.UnpivotOtherColumns(Source, {"Result"}, "A", "T"),
Ans = List.Difference(
List.Distinct(up[T]),
Table.SelectRows(
up,
each Expression.Evaluate("(" & [Result] & ")*(3-2*" & Text.End([A], 1) & ")") < 0
)[T]
)
in
AnsPower Query solution 2 for Teams With No Losses, proposed by Zoran Milokanović:
let
Source = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Input"]}[Content]),
S = List.Difference(
List.Union(List.Transform(Source, List.RemoveLastN)),
List.Accumulate(
Source,
{},
(s, c) =>
let
r = Expression.Evaluate(c{2})
in
if r = 0 then s else if r < 0 then s & {c{0}} else s & {c{1}}
)
)
in
SPower Query solution 3 for Teams With No Losses, proposed by Rick de Groot:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45Wcs5IzSlOTVTSUQpKTcxR8E1MKcpMAfIMdc2VYnWi0US9SstS80pKi4FMA10DsAIkIYRZJromWHS7KTgrOCUWJafm5OeBVFnomoFVoYsjGWkENcgRqMQ3MycxD8Uac6gj0Q1AVm1ooGsJVoTQhq7cRNcUwxJUl5tiUYHkSmNdY6XYWAA=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [#"Team 1" = _t, #"Team 2" = _t, Result = _t]
),
Unpivot = Table.UnpivotOtherColumns(Source, {"Result"}, "Attribute", "Value"),
Select = Table.SelectRows(
Unpivot,
each [
Offset = Number.From(Text.End([Attribute], 1)) - 1,
List = Text.Split([Result], "-"),
Score = List{Offset} = "0"
][Score]
),
Teams = List.Distinct(Select[Value])
in
TeamsPower Query solution 4 for Teams With No Losses, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Teams = Table.FromColumns({Source[Team 1] & Source[Team 2]}),
TeamsGames = Table.Group(Teams, {"Column1"}, {{"Count", each Table.RowCount(_)}}),
Winner = Table.AddColumn(
Source,
"Win",
each
let
a = List.Transform(Text.SplitAny([Result], " -"), Number.From),
b =
if a{0} = a{1} then
{[Team 1]} & {[Team 2]}
else if a{0} > a{1} then
{[Team 1]}
else
{[Team 2]}
in
b
)[[Win]],
ExpandWin = Table.ExpandListColumn(Winner, "Win"),
GroupWin = Table.ToRows(Table.Group(ExpandWin, {"Win"}, {{"Games Win", each Table.RowCount(_)}})),
ListGames = List.Transform(List.Intersect({Table.ToRows(TeamsGames), GroupWin}), each _{0})
in
ListGamesPower Query solution 5 for Teams With No Losses, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
add = Table.FromColumns(
{
List.Transform(
List.Distinct(
List.Combine(
Table.AddColumn(
Fonte,
"Personalizar",
each [
t1 = List.Transform(
{Text.Split([Result], "-")},
(x) =>
if x{0} = x{1} then
{[Team 1] & "_ganhou"} & {[Team 2] & "_ganhou"}
else if Number.From(x{0}) > Number.From(x{1}) then
{[Team 1] & "_ganhou"}
else
{[Team 2] & "_ganhou"}
){0},
t2 = List.Transform(
{Text.Split([Result], "-")},
(x) =>
if x{0} = x{1} then
{[Team 1] & "_ganhou"} & {[Team 2] & "_ganhou"}
else if Number.From(x{0}) < Number.From(x{1}) then
{[Team 1] & "_perdeu"}
else
{[Team 2] & "_perdeu"}
){0},
res = List.Combine({t1, t2})
][res]
)[Personalizar]
)
),
each Text.Split(_, "_"){0}
)
}
),
res = Table.SelectRows(
Table.Group(add, {"Column1"}, {{"Contagem", each Table.RowCount(_)}}),
each [Contagem] = 1
)[Column1]
in
resPower Query solution 6 for Teams With No Losses, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Teams = List.Distinct(Origen[Team 1] & Origen[Team 2]),
Split = Table.SplitColumn(
Origen,
"Result",
Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),
{"Result.1", "Result.2"}
),
Type = Table.TransformColumnTypes(Split, {{"Result.1", Int64.Type}, {"Result.2", Int64.Type}}),
P1 = Table.AddColumn(Type, "Lost1", each if [Result.1] < [Result.2] then [Team 1] else null),
P2 = Table.AddColumn(P1, "Lost2", each if [Result.2] < [Result.1] then [Team 2] else null),
Exclude = List.Distinct(List.Select(P2[Lost1] & P2[Lost2], each _ <> null)),
NeverLost = Table.FromList(
List.Difference(Teams, Exclude),
Splitter.SplitByNothing(),
{"Team Never Lost"}
)
in
NeverLostPower Query solution 7 for Teams With No Losses, proposed by Szabolcs Phraner:
let
Source = Excel.CurrentWorkbook(){[Name = "Matches"]}[Content],
Losers = List.Distinct(
List.RemoveNulls(
Table.AddColumn(
Source,
"Losers",
each
let
Result = Expression.Evaluate([Result])
in
if Result = 0 then null else if Result > 0 then [Team 2] else [Team 1]
)[Losers]
)
)
in
List.Difference(List.Distinct(Source[Team 1] & Source[Team 2]), Losers)Solving the challenge of Teams With No Losses with Excel
Excel solution 1 for Teams With No Losses, proposed by Bo Rydobon 🇹🇭:
=LET(t,
A2:B11,
r,
C2:C11,
UNIQUE(TOCOL(IFS(ISNA(XMATCH(t,
TOCOL(IF((TEXTSPLIT(
r,
"-"
)-TEXTAFTER(
r,
"-"
))*{1,
-1}<0,
t)))),
t),
3)))Excel solution 2 for Teams With No Losses, proposed by John V.:
=LET(
r,
C2:C11,
d,
TEXTSPLIT(
r,
"-"
)-TEXTAFTER(
r,
"-"
),
UNIQUE(
VSTACK(
TOCOL(
IFS(
d<0,
A2:A11,
d>0,
B2:B11
),
2
),
UNIQUE(
TOCOL(
A2:B11
)
)
),
,
1
)
)Excel solution 3 for Teams With No Losses, proposed by محمد حلمي:
=LET(
a,
A2:A11,
c,
C2:C11,
i,
UNIQUE(
a
),
v,
TEXTSPLIT(
c,
"-"
)-TEXTAFTER(
c,
"-"
),
FILTER(
i,
ISNA(
XMATCH(
i,
IFS(
v<0,
a,
v>0,
B2:B11
)
)
)
)
)Excel solution 4 for Teams With No Losses, proposed by محمد حلمي:
=LET(
a,
A2:C11,
U,
UNIQUE(
TOCOL(
A2:B11
)
),
FILTER(
U,
BYROW(
U,
LAMBDA(
x,
LET(
V,
CHOOSECOLS(
FILTER(
a,
A2:A11=x
),
3
),
SUM(
IF(
1*TEXTBEFORE(
V,
"-"
)<1*TEXTAFTER(
V,
"-"
),
1,
0
)
)
)
)
)
+
BYROW(
U,
LAMBDA(
x,
LET(
V,
CHOOSECOLS(
FILTER(
a,
B2:B11=x
),
3
),
SUM(
IF(
1*TEXTBEFORE(
V,
"-"
)>1*TEXTAFTER(
V,
"-"
),
1,
0
)
)
)
)
)
=0
)
)Excel solution 5 for Teams With No Losses, proposed by Kris Jaganah:
=LET(
a,
A2:A11,
b,
B2:B11,
c,
C2:C11,
d,
TEXTBEFORE(
c,
"-"
),
e,
TEXTAFTER(
c,
d
)+d,
f,
UNIQUE(
VSTACK(
a,
b
)
),
FILTER(
f,
ISERROR(
XMATCH(
f,
TOCOL(
IFS(
e>0,
b,
e=0,
1/e,
1,
a
),
3
)
)
)
)
)Excel solution 6 for Teams With No Losses, proposed by Timothée BLIOT:
=LET(
A,
MAP(
SUBSTITUTE(
C2:C11,
"-",
"*-"
),
LAMBDA(
x,
SUM(
--TEXTSPLIT(
x,
"*"
)
)
)
),
B,
A2:A11,
C,
B2:B11,
D,
UNIQUE(
VSTACK(
B,
C
)
),
FILTER(
D,
D<>VSTACK(
FILTER(
B,
A<0
),
FILTER(
C,
A>0
)
)
)
))Excel solution 7 for Teams With No Losses, proposed by Hussein SATOUR:
=LET(
a,
A2:A11,
b,
B2:B11,
l,
--TEXTBEFORE(
C2:C11,
"-"
),
r,
--TEXTAFTER(
C2:C11,
"-"
),
w,
IFNA(
XMATCH(
VSTACK(
a,
b
),
IFS(
lr,
b,
1,
""
)
),
VSTACK(
a,
b
)
),
UNIQUE(
FILTER(
w,
ISTEXT(
w
)
)
)
) Excel solution 8 for Teams With No Losses, proposed by Sunny Baggu:
=LET(
_team,
UNIQUE(
A2:A11
),
FILTER(
_team,
MAP(
_team,
LAMBDA(
x,
LET(
_cond,
N(
A2:B11 = x
),
_LHS,
TEXTBEFORE(
C2:C11,
"-"
),
_RHS,
TEXTAFTER(
C2:C11,
"-"
),
AND(
TOCOL(
IFS(
TAKE(
_cond,
,
1
),
_LHS - _RHS,
TAKE(
_cond,
,
-1
),
_RHS - _LHS,
1,
1 / 0
),
3
) >= 0
)
)
)
)
)
)Excel solution 9 for Teams With No Losses, proposed by LEONARD OCHEA 🇷🇴:
=LET(t,
A2:C11,
e,
TAKE(
t,
,
2
),
r,
TAKE(
t,
,
-1
),
g,
TEXTBEFORE(
r,
"-"
)-TEXTAFTER(
r,
"-"
),
s,
--HSTACK(
g>=0,
g<=0
),
u,
UNIQUE(
TOCOL(
e
)
),
FILTER(u,
MAP(u,
LAMBDA( a,
LET(b,
--(e=a),
AND(
IF(
b,
s,
)=b
))))))Excel solution 10 for Teams With No Losses, proposed by Abdallah Ally:
=UNIQUE(
VSTACK(
UNIQUE(
TOCOL(
A2:B11
)
),
UNIQUE(
TOCOL(
MAP(
A2:A11,
B2:B11,
C2:C11,
LAMBDA(
x,
y,
z,
LET(
a,
--TEXTSPLIT(
z,
"-"
),
b,
CHOOSECOLS(
a,
1
),
c,
CHOOSECOLS(
a,
2
),
IFS(
bc,
y
)
)
)
),
3
)
)
),
,
TRUE
) Excel solution 11 for Teams With No Losses, proposed by Pieter de B.:
=LET(a,A2:A11,b,B2:B11,c,C2:C11,d,--TEXTBEFORE(c,"-"),e,--TEXTAFTER(c,"-"),ab,TOCOL(HSTACK(a,b)),UNIQUE(FILTER(ab,ISNA(XMATCH(ab,TOCOL(IFS(de,b),2)))))) Excel solution 12 for Teams With No Losses, proposed by Asheesh Pahwa:
=LET(to,A2:A11,tw,B2:B11,r,C2:C11,
df,a-b,a,--TEXTSPLIT(r,"-"),b,--TEXTAFTER(r, "-"), I won,IFS(df>0,to,df<0,tw,TRUE,""), lost,IFS(df>0,tw,df<0,to,TRUE,""),
UNIQUE(FILTER(won,ISNA(XMATCH(won, lost)))))Excel solution 13 for Teams With No Losses, proposed by Ziad A.:
=LET(
a,
A2:A11,
W,
LAMBDA(
x,
i,
FILTER(
x,
COUNTIFS(
x,
x,
MMULT(
SPLIT(
C2:C11,
"-"
),
{1;-1}
),
i&0
)=COUNTIF(
x,
x
)
)
),
UNIQUE(
FILTER(
W(
a,
">="
),
COUNTIF(
W(
B2:B11,
"<="
),
W(
a,
">="
)
)
)
)
)Excel solution 14 for Teams With No Losses, proposed by Giorgi Goderdzishvili:
=LET(
tm_1,
A2:A11,
tm_2,
B2:B11,
rst,
C2:C11,
fst,
--TEXTBEFORE(
rst,
"-"
),
sc,
--TEXTAFTER(
rst,
"-"
),
chck,
SWITCH(
TRUE,
fst>sc,
"Win",
fstExcel solution 15 for Teams With No Losses, proposed by Abdelrahman Omer, MBA, PMP:
=LET(
team1,
A2:A11,
team2,
B2:B11,
result,
C2:C11,
b,
TEXTBEFORE(
result,
"-"
)*1,
a,
TEXTAFTER(
result,
"-"
)*1,
losers,
IF(
a>b,
team1,
IF(
aExcel solution 16 for Teams With No Losses, proposed by Daniel Garzia:
=LET(
f,
A2:A11,
s,
C2:C11,
r,
TEXTBEFORE(
s,
"-"
)-TEXTAFTER(
s,
"-"
),
t,
UNIQUE(
f
),
FILTER(
t,
ISNA(
XMATCH(
t,
IFS(
r<0,
f,
r>0,
B2:B11
)
)
)
)
)Excel solution 17 for Teams With No Losses, proposed by Anup Kumar:
=LET(
removedraw,
FILTER(
A2:C11,
1*TEXTBEFORE(
C2:C11,
"-"
)<>1*TEXTAFTER(
C2:C11,
"-"
)
),
rslt,
BYROW(
TAKE(
removedraw,
,
-1
),
LAMBDA(
x,
--TEXTBEFORE(
x,
"-"
)>--TEXTAFTER(
x,
"-"
)
)
),
rsltArr,
VSTACK(
HSTACK(
CHOOSECOLS(
removedraw,
1
),
rslt
),
HSTACK(
CHOOSECOLS(
removedraw,
2
),
NOT(
rslt
)
)
),
winners,
FILTER(
TAKE(
rsltArr,
,
1
),
DROP(
rsltArr,
,
1
)
),
loosers,
FILTER(
TAKE(
rsltArr,
,
1
),
NOT(
DROP(
rsltArr,
,
1
)
)
),
UNIQUE(
FILTER(
winners,
ISNA(
XMATCH(
winners,
loosers
)
)
)
)
)Excel solution 18 for Teams With No Losses, proposed by Rayan S.:
=LET(
x,
LEFT(
C2:C11,
FIND(
"-",
C2:C11,
1
) - 1
) + 0,
y,
RIGHT(
C2:C11,
LEN(
C2:C11
) - FIND(
"-",
C2:C11,
1
)
) + 0,
arr,
VSTACK(
HSTACK(
B2:B11,
y >= x
),
HSTACK(
A2:A11,
x >= y
)
),
c,
FILTER(
TAKE(
arr,
,
1
),
TAKE(
arr,
,
-1
) = FALSE
),
s,
IFERROR(
MATCH(
TAKE(
arr,
,
1
),
c,
0
),
TAKE(
arr,
,
1
)
),
UNIQUE(
FILTER(
s,
ISTEXT(
s
)
)
)
)Excel solution 19 for Teams With No Losses, proposed by Rayan S.:
=LET(
x, TEXTBEFORE(C2:C11, "-") + 0,
y, TEXTAFTER(C2:C11, "-") + 0,
arr, VSTACK(HSTACK(B2:B11, y >= x), HSTACK(A2:A11, x >= y)),
t, TAKE(arr, , 1),
s, IFERROR(MATCH(t, FILTER(t, TAKE(arr, , -1) = FALSE), 0), t),
UNIQUE(FILTER(s, ISTEXT(s)))
)Excel solution 20 for Teams With No Losses, proposed by Mungunbayar Bat-Ochir:
=LET(
_team1;
A2:A11;
_team2;
B2:B11;
_scores;
VALUE(
TEXTSPLIT(
TEXTJOIN(
"#";
;
C2:C11
);
"-";
"#"
)
);
winners;
IFS(
CHOOSECOLS(
_scores;
1
)>CHOOSECOLS(
_scores;
2
);
_team1;
CHOOSECOLS(
_scores;
1
)CHOOSECOLS(
_scores;
2
);
_team2
);
result;
UNIQUE(
TOCOL(
FILTER(
winners;
ISERROR(
XMATCH(
winners;
losers
)
)
);
2
)
);
result
) Excel solution 21 for Teams With No Losses, proposed by Andres Rojas Moncada:
=LET(_rest1,
VALOR(
TEXTOANTES(
D4:D13,
"-"
)
),
rest2,
VALOR(
TEXTODESPUES(
D4:D13,
"-"
)
),
_sq0,
NO(
ESNUMERO(
_rest1
)
)*1,
_sq1,
ESNUMERO(
_sq0
)*1,
_arraypg,
SI(
_rest1=_rest2,
APILARH(
B4:B13,
_sq1,
C4:C13,
_sq1
),
SI(
_rest1<_rest2,
APILARH(
B4:B13,
_sq0,
C4:C13,
_sq1
),
APILARH(
B4:B13,
_sq1,
C4:C13,
_sq0
)
)
),
_listapg,
AJUSTARFILAS(
ENCOL(
_arraypg
),
2
),
_teams,
UNICOS(
ELEGIRCOLS(
_listapg,
1
)
),
_bool,
MAP(_teams,
LAMBDA(_ts,
SUMAPRODUCTO((ELEGIRCOLS(
_listapg,
1
)=_ts)*1)))=MAP(_teams,
LAMBDA(_ts,
SUMAPRODUCTO((ELEGIRCOLS(
_listapg,
1
)=_ts)*1,
ELEGIRCOLS(
_listapg,
2
)))),
FILTRAR(
_teams,
_bool
))
_rest1 = Resultado del equipo 1
_rest2 = Resultado del equipo 2
_sq0 = Secuencia de ceros (perdedores)
_sq1 = Secuencia de unos (ganadores)
_arraypg = Array de perdedores (0) y ganadores (1)
_listapg = _arraypg convertido en lista de 2 columnas
_teams = Equipos unicos del conjunto
_bool = Comparacion del conteo de equipos totales vs partidos ganados (si son iguales,
entonces ganaron la misma cantidad de apariciones)Excel solution 22 for Teams With No Losses, proposed by Ricardo Alexis Domínguez Hernández:
=FILTER(UNIQUE(TOCOL(A2:B11)),
BYROW(UNIQUE(TOCOL(A2:B11)),
LAMBDA(x,SUM(IF(1*TEXTBEFORE(CHOOSECOLS(FILTER($A$2:$C$11,$A$2:$A$11=x),3),"-")<1*TEXTAFTER(CHOOSECOLS(FILTER($A$2:$C$11,$A$2:$A$11=x),3),"-"),1,0))))
+
BYROW(UNIQUE(TOCOL(A2:B11)),
LAMBDA(x,SUM(IF(1*TEXTBEFORE(CHOOSECOLS(FILTER($A$2:$C$11,$B$2:$B$11=x),3),"-")>1*TEXTAFTER(CHOOSECOLS(FILTER($A$2:$C$11,$B$2:$B$11=x),3),"-"),1,0))))=0)Excel solution 23 for Teams With No Losses, proposed by Kriddakorn Pongthanisorn:
=LET(_t1, A2:A11, _t2,B2:B11, _sc,C2:C11, _score, ARRAYFORMULA(SPLIT(_sc,"-",1)), _s1, CHOOSECOLS(_score,1), _s2, CHOOSECOLS(_score,2), _winner, MAP(_t1,_t2,_s1,_s2,LAMBDA(_t1,_t2,_s1,_s2, IFERROR(IFS(_s1-_s2>0,_t1,_s1-_s2,_t2),TEXTJOIN(",",1,_t1,_t2)))), _sp_winner,TOCOL(ARRAYFORMULA(SPLIT(_winner,",",1)),1), _team,UNIQUE(_sp_winner), _total_play, ARRAYFORMULA(COUNTIFS(VSTACK(_t1,_t2),_team)), _total_win, ARRAYFORMULA(COUNTIFS(_sp_winner, _team)), _all_win,CHOOSECOLS( FILTER(HSTACK(_team,ARRAYFORMULA(_total_win-_total_play)),ARRAYFORMULA(_total_win-_total_play)=0),1),_all_win)Excel solution 24 for Teams With No Losses, proposed by Matteo Franco:
=LET(m, A2:C11,
lost, UNIQUE(IF(VALUE(TEXTBEFORE(DROP(m, , 2), "-"))0)) Solving the challenge of Teams With No Losses with Python in Excel
Python in Excel solution 1 for Teams With No Losses, proposed by Bo Rydobon 🇹🇭:
Python
df = xl("A1:C11", headers=True)
up = pd.melt(df, id_vars=['Result'])
list(set(up.value)-set(up[pd.eval('('+up.Result+')*(3-2*'+up.variable.str[-1]+')')<0].value))
(1-7)*(3-2*T#) = -6 for T1 and 6 for T2
Python in Excel solution 2 for Teams With No Losses, proposed by John V.:
Hi everyone!
df = xl("A1:C11", headers=True)
df["D"] = [int(x[0]) - int(x[1]) for x in [i.split("-") for i in df["Result"]]]
df["L"] = [r["Team 1"] if r["D"] < 0 else r["Team 2"] if r["D"] > 0 else None for i, r in df.iterrows()]
p = set(df["L"][df["L"].notnull()])
t = set(pd.unique(df[["Team 1", "Team 2"]].values.ravel("K")))
pd.DataFrame(list(p.symmetric_difference(t)))
Blessings!
Python in Excel solution 3 for Teams With No Losses, proposed by Diarmuid Early:
I'm saving all my Python solutions to these challenges here if anyone wants to explore:
bit.ly/PythonLearningFolder
input = xl("A1:C11", headers=True)
allTeams = set(input.iloc[:, :2].values.flatten())
losers = set([t1 if dif < 0 else t2 for t1, t2, res in input.values if (dif:=(goals:=list(map(int,res.split("-"))))[0]-goals[1]) != 0])
list(allTeams-losers)
input = xl("A1:C11", headers=True)
allTeams = pd.unique(input.iloc[:, :2].values.flatten())
losers = [t1 if dif < 0 else t2 for t1, t2, res in input.values if (dif:=(goals:=list(map(int,res.split("-"))))[0]-goals[1]) != 0]
[team for team in allTeams if team not in losers]
Solving the challenge of Teams With No Losses with R
R solution 1 for Teams With No Losses, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Teams Never Lost.xlsx") %>% select(1,2,3)
result_1 = input %>%
select(home_team = 1, away_team = 2, everything()) %>%
separate(Result, into = c("home_score", "away_score"), sep = "-") %>%
mutate(home_score = as.numeric(home_score),
away_score = as.numeric(away_score),
home_result = case_when(home_score > away_score ~ "WIN",
home_score < away_score ~ "LOSE",
home_score == away_score ~ "TIE"),
away_result = case_when(home_score < away_score ~ "WIN",
home_score > away_score ~ "LOSE",
home_score == away_score ~ "TIE"))
home_teams = result_1 %>%
select(team = 1, result = 5)
away_teams = result_1 %>%
select(team = 2, result = 6)
result = bind_rows(home_teams, away_teams) %>%
group_by(team, result) %>%
count() %>%
ungroup() %>%
pivot_wider(names_from = result, values_from = n, values_fill = 0)
result$team[result$LOSE == 0]
# [1] "Juventus" "Real Madrid"
&&