— This week will be FIFA World Cup week. All challenges will be related to FIFA World Cup only for this week. — List teams who have won FIFA world cup consecutively and years of their consecutive winnings. Note – I have changed 1966 winner to Brazil from England and 1998 winner to Brazil from France Your formula need not be different from others as long as you have worked out your formula independently)
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 69
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Consecutive FIFA Wins and Years with Power Query
Power Query solution 1 for Consecutive FIFA Wins and Years, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(
Source,
"Champion",
{"Years", each Text.Combine(List.Transform([Year], Text.From), ", ")},
GroupKind.Local
),
Return = Table.SelectRows(Group, each Text.Contains([Years], ", "))
in
ReturnPower Query solution 2 for Consecutive FIFA Wins and Years, proposed by Luan Rodrigues:
let
Fonte = Data,
Group = Table.SelectRows(
Table.Group(
Fonte,
{"Champion"},
{{"Contagem", each _}, {"Contar", each Table.RowCount(_)}},
GroupKind.Local
),
each [Contar] > 1
),
List = Table.AddColumn(Group, "Times Won", each [Contagem][Year]),
Result = Table.TransformColumns(
List,
{"Times Won", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
)[[Champion], [Times Won]]
in
ResultPower Query solution 3 for Consecutive FIFA Wins and Years, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source, {{"Year", type text}}),
Grouped = Table.Group(
ChangedType,
{"Champion"},
{{"Count", each Table.RowCount(_), Int64.Type}, {"Times Won", each Text.Combine(_[Year], ", ")}},
GroupKind.Local
),
Filtered = Table.SelectRows(Grouped, each [Count] > 1),
Final = Table.RemoveColumns(Filtered, {"Count"})
in
FinalPower Query solution 4 for Consecutive FIFA Wins and Years, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name="ConsecutiveWins"]}[Content],
#"Grouped Rows" = Table.Group(Source,
{"Champion"},
{
{"Count", each
Table.RowCount(_), Int64.Type},
{"Years Won", each [Year]}
},
GroupKind.Local),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each
[Count] > 1)[[Champion],[Years Won]],
#"Extracted Values" = Table.TransformColumns(#"Filtered Rows", {"Years Won", each
Text.Combine(List.Transform(_, Text.From), ", "),
type text})
in
#"Extracted Values"
GroupKind.Local explained for the recent "Consecutive Birds" challenge:
https://www.linkedin.com/posts/matthiasfriedmann_excel-excelchallenge-powerquerychallenge-activity-6998615127243554816-Vi1j
Power Query solution 5 for Consecutive FIFA Wins and Years, proposed by Abdoul Karim N.:
let
Source = Excel.CurrentWorkbook(){[Name = "TWorldCup"]}[Content],
ChangedType = Table.TransformColumnTypes(Source, {{"Year", type text}, {"Champion", type text}}),
Intermediate = [
Grouping = Table.Group(
ChangedType,
{"Champion"},
{
{"Count", each Table.RowCount(_), Int64.Type},
{"Time Wons", each Text.Combine([Year], ", ")}
},
GroupKind.Local
),
ConsecutiveWins = Table.SelectRows(Grouping, each [Count] >= 2)[[Champion], [Time Wons]]
][ConsecutiveWins][[Champion], [Time Wons]]
in
IntermediateSolving the challenge of Consecutive FIFA Wins and Years with Excel
Excel solution 1 for Consecutive FIFA Wins and Years, proposed by John V.:
=LET(a,
B2:B22,
b,
"",
c,
MAP(
a,
LAMBDA(
x,
IF(
OR(
x=T(
OFFSET(
x,
{-1;1},
)
)
),
x,
b
)
)
),
FILTER(HSTACK(
c,
SCAN(
b,
ROW(
a
)-1,
LAMBDA(
i,
x,
IF(
OR(
INDEX(
c,
x-1
)=b,
x=1
),
b,
i&", "
)&INDEX(
A2:A22,
x
)
)
)
),
(VSTACK(
DROP(
c,
1
),
b
)=b)*(c>b)))Excel solution 2 for Consecutive FIFA Wins and Years, proposed by محمد حلمي:
=LET(
a,SCAN("",B2:B22,LAMBDA(a,d,
IF(OR(d=T(OFFSET(d,{1,-1},))),
a&d&" "&OFFSET(d,,-1),""))),
v,FILTER(a,(a<>"")*(VSTACK(DROP(a,1),"")="")),
f,TEXTBEFORE(v," "),
HSTACK(f,TEXTAFTER(SUBSTITUTE(v,f,","),", ")))Excel solution 3 for Consecutive FIFA Wins and Years, proposed by محمد حلمي:
=REDUCE(A1:B1,B2:B22,LAMBDA(a,v,LET(
i,@+v:A22,
IF(v=OFFSET(v,-1,),VSTACK(DROP(a,-1),
HSTACK(v,TAKE(a,-1,-1)&", "&i)),
IF(v=OFFSET(v,1,),VSTACK(a,HSTACK(v,i)),a)))))Excel solution 4 for Consecutive FIFA Wins and Years, proposed by محمد حلمي:
=LET(
s,
SCAN(
0,
B2:B22,
LAMBDA(
a,
d,
IF(
OFFSET(
d,
-1,
)=d,
a,
a+1
)
)
),
REDUCE(
HSTACK(
"Champion",
"Times Won"
),
UNIQUE(
VSTACK(
UNIQUE(
s,
,
1
),
UNIQUE(
s
)
),
,
1
),
LAMBDA(
a,
d,
VSTACK(
a,
LET(
r,
FILTER(
A2:B22,
s=d
),
HSTACK(
INDEX(
r,
1,
2
),
ARRAYTOTEXT(
TAKE(
r,
,
1
)
)
)
)
)
)
)
)Excel solution 5 for Consecutive FIFA Wins and Years, proposed by محمد حلمي:
=REDUCE(
A1:B1,
B2:B22,
LAMBDA(
a,
v,
LET(
i,
@+v:A22,
IF(
v=OFFSET(
v,
-1,
),
VSTACK(
DROP(
a,
-1
),
HSTACK(
v,
TAKE(
a,
-1,
-1
)&", "&i
)
),
IF(
v=OFFSET(
v,
1,
),
VSTACK(
a,
HSTACK(
v,
i
)
),
a
)
)
)
)
)Excel solution 6 for Consecutive FIFA Wins and Years, proposed by 🇰🇷 Taeyong Shin:
=LET(c,B2:B22,n,SCAN(0,c<>DROP(VSTACK(0,c),-1),SUM),DROP(GROUPBY(HSTACK(n,c),A2:A22,ARRAYTOTEXT,,0,,MMULT(XMATCH(n,n,,{1,-1}),{-1;1})),,1))Excel solution 7 for Consecutive FIFA Wins and Years, proposed by Julian Poeltl:
=LET(
Y,
A2:A22,
C,
B2:B22,
D,
IFERROR(
C=DROP(
C,
1
),
0
),
F,
D+VSTACK(
0,
DROP(
D,
-1
)
),
B,
BYROW(
IFNA(
TEXTSPLIT(
TEXTJOIN(
",",
,
IF(
F>0,
Y,
"|"
)
),
",",
"|"
),
""
),
LAMBDA(
A,
TEXTJOIN(
", ",
,
A
)
)
),
T,
FILTER(
B,
B<>""
),
HSTACK(
XLOOKUP(
TEXTBEFORE(
T,
","
)*1,
Y,
C
),
T
)
)Excel solution 8 for Consecutive FIFA Wins and Years, proposed by Timothée BLIOT:
=LET(
Data,
A2:B22,
ConsecutiveWinners,
FILTER(
Data,
BYROW(
SEQUENCE(
ROWS(
Data
)
),
LAMBDA(
a,
--OR(
IF(
a>1,
INDEX(
Data,
a,
2
)=INDEX(
Data,
a-1,
2
),
0
),
IF(
a1,
IF(
INDEX(
ConsecutiveWinners,
a,
1
)=INDEX(
ConsecutiveWinners,
a-1,
1
)+4,
total,
total+1
),
total
)
)
),
Answer,
HSTACK(
MAP(
UNIQUE(
Groups
),
LAMBDA(
a,
INDEX(
ConsecutiveWinners,
MATCH(
a,
Groups
),
2
)
)
),
MAP(
UNIQUE(
Groups
),
LAMBDA(
a,
TEXTJOIN(
", ",
1,
FILTER(
INDEX(
ConsecutiveWinners,
,
1
),
Groups=a,
""
)
)
)
)
),
Answer
) Excel solution 9 for Consecutive FIFA Wins and Years, proposed by Duy Tùng:
=LET(b,B2:B22,a,DROP(GROUPBY(HSTACK(SCAN(0,b<>B1:B21,SUM),b),A2:A22,ARRAYTOTEXT,,0),,1),FILTER(a,LEN(DROP(a,,1))>4))Excel solution 10 for Consecutive FIFA Wins and Years, proposed by Bhavya Gupta:
=LET(
Y,
A2:A22,
C,
B2:B22,
s,
SCAN(
0,
--VSTACK(
FALSE,
DROP(
C,
1
)=DROP(
C,
-1
)
),
LAMBDA(
x,
y,
IF(
y,
x,
x+1
)
)
),
u,
UNIQUE(
s
),
f,
DROP(
FREQUENCY(
s,
u
),
-1
),
i,
FILTER(
u,
f>1
),
m,
MAP(
i,
LAMBDA(
a,
ARRAYTOTEXT(
FILTER(
Y,
s=a
)
)
)
),
HSTACK(
XLOOKUP(
i,
s,
C
),
m
)
)Excel solution 11 for Consecutive FIFA Wins and Years, proposed by Rajesh Sinha:
=Unique(
B2:B20
)
D2 =TEXTJOIN(
", ",
TRUE,
IF(
$B$2:$B$20=C2,
$A$2:$A$20,
""
)
)Solving the challenge of Consecutive FIFA Wins and Years with Python in Excel
Python in Excel solution 1 for Consecutive FIFA Wins and Years, proposed by Alejandro Campos:
df = xl("A1:B22", headers=True)
consecutive_wins = {}
previous_team = None
years_list = []
for index, row in df.iterrows():
year = row['Year']
team = row['Champion']
if team == previous_team:
years_list.append(year)
else:
if len(years_list) > 1:
if previous_team in consecutive_wins:
consecutive_wins[previous_team].append(years_list)
else:
consecutive_wins[previous_team] = [years_list]
years_list = [year]
previous_team = team
if len(years_list) > 1:
if previous_team in consecutive_wins:
consecutive_wins[previous_team].append(years_list)
else:
consecutive_wins[previous_team] = [years_list]
rows = []
for team, years in consecutive_wins.items():
for y in years:
rows.append({'Team': team, 'Years': ', '.join(map(str, y))})
df_consecutive_wins = pd.DataFrame(rows)
df_consecutive_wins
Solving the challenge of Consecutive FIFA Wins and Years with SQL
SQL solution 1 for Consecutive FIFA Wins and Years, proposed by Zoran Milokanović:
SELECT
T.CHAMPION
,LISTAGG(T.YEAR) AS TIMES_WON
FROM
(
SELECT
D.YEAR
,D.CHAMPION
,NVL2(NVL(DP.CHAMPION, DN.CHAMPION), 1, 0) AS GROUP
,SUM(NVL2(NVL(DP.CHAMPION, DN.CHAMPION), 0, 1)) OVER (ORDER BY D.YEAR) AS GROUP_ID
FROM DATA D
LEFT JOIN DATA DP ON DP.CHAMPION = D.CHAMPION
AND DP.YEAR + 4 = D.YEAR
LEFT JOIN DATA DN ON DN.CHAMPION = D.CHAMPION
AND DN.YEAR - 4 = D.YEAR
) T
WHERE
T.GROUP = 1
GROUP BY
T.CHAMPION
,T.GROUP_ID
ORDER BY
2
;