Align the soccer WC champions year wise (year in descending order).
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 498
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List WC Champion Years with Power Query
Power Query solution 1 for List WC Champion Years, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Split = Table.ExpandListColumn(
Table.TransformColumns(Source, {"Years", Splitter.SplitTextByDelimiter(", "), type number}),
"Years"
),
Sort = Table.Sort(Split, {"Years", 1}),
Clean = Table.ReorderColumns(Sort, {"Years", "Winners"})
in
CleanPower Query solution 2 for List WC Champion Years, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Generate = List.TransformMany(
Table.ToRows(Source),
(x) => Text.Split(x{1}, ", "),
(x, y) => {Number.From(y), x{0}}
),
Table = Table.FromRows(Generate, type table [Year = number, Country = text]),
Return = Table.Sort(Table, {"Year", Order.Descending})
in
ReturnPower Query solution 3 for List WC Champion Years, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Split = Table.TransformColumns(Source, {"Years", each Text.Split(_, ", ")}),
Expand = Table.ExpandListColumn(Split, "Years"),
Sol = Table.SelectColumns(Table.Sort(Expand, {{"Years", 1}}), {"Years", "Winners"})
in
SolPower Query solution 4 for List WC Champion Years, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
lst = Table.TransformColumns(Fonte, {"Years", each Text.Split(_, ", ")}),
exp = Table.ExpandListColumn(lst, "Years"),
res = Table.Sort(exp, {each Number.From([Years]), 1})
in
resPower Query solution 5 for List WC Champion Years, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.TransformColumnTypes(S, {"Years", type text}),
b = Table.TransformColumns(a, {"Years", each Text.Split(_, ", ")}),
c = Table.Sort(Table.ExpandListColumn(b, "Years"), {"Years", 1}),
d = Table.SelectColumns(c, {"Years", "Winners"}),
Sol = Table.TransformColumnNames(d, each Text.Remove(_, "s"))
in
SolPower Query solution 6 for List WC Champion Years, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Split = Table.ExpandListColumn(
Table.TransformColumns(Source, {{"Years", Splitter.SplitTextByDelimiter(", ")}}),
"Years"
),
ChangeTyped = Table.TransformColumnTypes(Split, {{"Years", Int64.Type}}),
Sorting = Table.Sort(ChangeTyped, {{"Years", 1}})[[Years], [Winners]]
in
SortingPower Query solution 7 for List WC Champion Years, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.ReorderColumns(
Table.Sort(
Table.ExpandListColumn(
Table.TransformColumns(
Source,
{{"Years", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv)}}
),
"Years"
),
{{"Years", Order.Descending}}
),
{"Years", "Winners"}
)
in
ResultPower Query solution 8 for List WC Champion Years, proposed by Abdul Said M K.:
PQ
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Winners", type text}, {"Years", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Years", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Years.1", "Years.2", "Years.3", "Years.4", "Years.5"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Years.1", Int64.Type}, {"Years.2", Int64.Type}, {"Years.3", Int64.Type}, {"Years.4", Int64.Type}, {"Years.5", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Winners"}, "Attribute", "Value"),
#"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Value", Order.Descending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Attribute"})
in
#"Removed Columns"
Solving the challenge of List WC Champion Years with Excel
Excel solution 1 for List WC Champion Years, proposed by Bo Rydobon 🇹🇭:
=index(array,1,1)
@range = implicit intersection that only works with single column or row
+range returns array
@+range =index(range,1,1)Excel solution 2 for List WC Champion Years, proposed by Bo Rydobon 🇹🇭:
=SORT(REDUCE(D2:E2,B3:B10,LAMBDA(a,v,VSTACK(a,IF({0,1},@+A10:v,--TEXTSPLIT(v,,", "))))),,-1)Excel solution 3 for List WC Champion Years, proposed by Rick Rothstein:
=SORT(TEXTSPLIT(TRIM(CONCAT(SUBSTITUTE(B2:B10&", ",",","|"&A2:A10))),"|"," "),,-1)Excel solution 4 for List WC Champion Years, proposed by John V.:
=LET(y,B3:B10,i,-SORT(-TEXTSPLIT(ARRAYTOTEXT(y),,",")),HSTACK(i,XLOOKUP("*"&i&"*",y,A3:A10,,2)))Excel solution 5 for List WC Champion Years, proposed by محمد حلمي:
=LET(i,TEXTSPLIT(CONCAT(B3:B10&"-"),",","-",1),
SORT(HSTACK(--TOCOL(i,2),TOCOL(IF(-i,A3:A10),2)),,-1))Excel solution 6 for List WC Champion Years, proposed by محمد حلمي:
=REDUCE(
D2:E2,
B3:B10,
LAMBDA(
a,
v,
SORT(
VSTACK(
a,
IF(
{1,
0},
--TEXTSPLIT(
v,
,
","
),
@+v:A10
)
),
,
-1
)
)
)Excel solution 7 for List WC Champion Years, proposed by 🇰🇷 Taeyong Shin:
=LET(f,LAMBDA(x,TEXTSPLIT(ARRAYTOTEXT(x),,",")),SORT(HSTACK(--f(B3:B10),f(REGEXREPLACE(B3:B10,"d+",A3:A10))),,-1))Excel solution 8 for List WC Champion Years, proposed by Kris Jaganah:
=LET(a,B3:B10,b,-SORT(-TEXTSPLIT(ARRAYTOTEXT(a),,", ")),HSTACK(b,MAP(b,LAMBDA(x,FILTER(A3:A10,IFERROR(FIND(x,a),))))))Excel solution 9 for List WC Champion Years, proposed by Julian Poeltl:
=LET(
T,
A3:B10,
Y,
SORT(
TEXTSPLIT(
TEXTJOIN(
", ",
,
TAKE(
T,
,
-1
)
),
,
", "
),
,
-1
),
W,
XLOOKUP(
"*"&Y&"*",
TAKE(
T,
,
-1
),
TAKE(
T,
,
1
),
,
2
),
VSTACK(
HSTACK(
"Year",
"Winner"
),
HSTACK(
Y*1,
W
)
)
)Excel solution 10 for List WC Champion Years, proposed by Timothée BLIOT:
=SORT(
DROP(
REDUCE(
"",
ROW(
1:8
),
LAMBDA(
w,
v,
LET(
A,
INDEX(
A3:A10,
v
),
B,
INDEX(
B3:B10,
v
),
Y,
TEXTSPLIT(
B,
,
", "
),
VSTACK(
w,
HSTACK(
Y,
IF(
A=Y,
,
A
)
)
)
)
)
),
1
),
1,
-1
)Excel solution 11 for List WC Champion Years, proposed by Oscar Mendez Roca Farell:
=LET(y, -MID(B3:B10, SEQUENCE(, 5, ,6), 4), VSTACK(D2:E2, SORT(MID(TOCOL(-y&A3:A10, 2), {1, 5}, {4, 9}), , -1)))Excel solution 12 for List WC Champion Years, proposed by Duy Tùng:
=LET(
a,
--TEXTSPLIT(
TEXTJOIN(
"/",
,
B3:B10
),
", ",
"/"
),
SORT(
HSTACK(
TOCOL(
a,
3
),
TOCOL(
IFS(
a,
A3:A10
),
3
)
),
,
-1
)
)Excel solution 13 for List WC Champion Years, proposed by Sunny Baggu:
=LET(
a,
TEXTSPLIT(
CONCAT(
B3:B10 & ";"
),
",",
";",
1
),
b,
IF(
--a,
A3:A10
),
SORT(
HSTACK(
--TOCOL(
a,
3
),
TOCOL(
b,
3
)
),
{1,
2},
{-1,
1}
)
)Excel solution 14 for List WC Champion Years, proposed by LEONARD OCHEA 🇷🇴:
=LET(
t,
","&A3:A10&"|",
SORT(
TEXTSPLIT(
CONCAT(
SUBSTITUTE(
B3:B10,
", ",
t
)&t
),
",",
"|",
1
),
,
-1
)
)Excel solution 15 for List WC Champion Years, proposed by Abdallah Ally:
=SORT(
REDUCE(
{"Year",
"Winner"},
A3:A10,
LAMBDA(
x,
y,
LET(
a,
TEXTSPLIT(
OFFSET(
y,
,
1
),
,
", "
),
VSTACK(
x,
HSTACK(
a,
EXPAND(
y,
COUNTA(
a
),
,
y
)
)
)
)
)
),
,
-1
)Excel solution 16 for List WC Champion Years, proposed by Anshu Bantra:
=LET(
data, A3:B10,
countries, INDEX(data, , 1),
years, SORT(
WRAPROWS(
REGEXEXTRACT(
TEXTJOIN(",", TRUE, B3:B10), "[0-9]+", 1
), 1
), , -1
),
winners, XLOOKUP( "*" & years & "*",
INDEX(data, , 2), INDEX(data, , 1), , 2
),
VSTACK({"Year", "Winner"}, HSTACK(years, winners))
)Excel solution 17 for List WC Champion Years, proposed by Pieter de B.:
=LET(y,--TEXTSPLIT(TEXTAFTER(","&B3:B10,",",{1,2,3}),","),x,LAMBDA(z,TOCOL(IFS(y,z),2)),SORT(HSTACK(x(y),x(A3:A10)),,-1))Excel solution 18 for List WC Champion Years, proposed by Hamidi Hamid:
=LET(
f,
LET(
x,
DROP(
HSTACK(
A2:A10,
IFERROR(
REDUCE(
,
B2:B10,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
",",
)
)
)
),
""
)
),
1
),
LET(
s,
HSTACK(
TOCOL(
IFNA(
TAKE(
x,
,
1
),
DROP(
x,
,
1
)
)
),
TOCOL(
DROP(
x,
,
1
)
)
),
FILTER(
s,
TAKE(
s,
,
-1
)<>"",
""
)
)
),
SORTBY(
f,
TAKE(
f,
,
-1
)*1,
-1
)
)Excel solution 19 for List WC Champion Years, proposed by Asheesh Pahwa:
=SORT(LET(w,A3:A10,yr,B3:B10,DROP(REDUCE("",SEQUENCE(ROWS(w)),LAMBDA(x,y,VSTACK(x&,IFNA(HSTACK(TEXTSPLIT(@INDEX(yr,y,),,", "),INDEX(w,y,)),INDEX(w,y,))))),1)),,-1)Excel solution 20 for List WC Champion Years, proposed by ferhat CK:
=LET(
a,
SORT(
TRIM(
TEXTSPLIT(
TEXTJOIN(
", ",
,
B3:B10
),
,
", "
)
),
1,
-1
),
b,
BYROW(
a,
LAMBDA(
x,
LET(
n,
INDEX(
A3:B10,
MATCH(
SEQUENCE(
30
),
FIND(
x,
B3:B10,
1
),
0
),
1
),
FILTER(
n,
ISTEXT(
n
)
)
)
)
),
HSTACK(
a,
b
)
)Excel solution 21 for List WC Champion Years, proposed by Ankur Sharma:
=LET(
a,
A3:A10,
b,
B3:B10,
SORT(
TEXTSPLIT(
TEXTJOIN(
", ",
,
MAP(
a,
b,
LAMBDA(
y,
z,
TEXTJOIN(
", ",
,
TEXTSPLIT(
z,
,
", "
) & "-" & y
)
)
)
),
"-",
", "
),
1,
-1
)
)Excel solution 22 for List WC Champion Years, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=VSTACK(
HSTACK(
"Year",
"Winner"
),
LET(
y,
SORT(
VALUE(
TEXTSPLIT(
TEXTJOIN(
", ",
,
B3:B10
),
,
", "
)
),
,
-1
),
HSTACK(
y,
MAP(
y,
LAMBDA(
x,
FILTER(
A3:A10,
ISNUMBER(
SEARCH(
x,
B3:B10
)
)
)
)
)
)
)
)Excel solution 23 for List WC Champion Years, proposed by Imam Hambali:
=LET(
d, TEXT(B3:B10,0),
c, A3:A10,
a, TEXTSPLIT(TEXTJOIN(",",TRUE,d),,","),
b, XLOOKUP("*"&a&"*",d,c,,2),
SORTBY(HSTACK(a*1,b),a,-1)
)Excel solution 24 for List WC Champion Years, proposed by Mey Tithveasna:
=LET(a,A3:A10,b,B3:B10,SORT(DROP(HSTACK(TOCOL(TEXTSPLIT(CONCAT(b&",",","),",")),INDEX(a,MATCH("*"&TOCOL(TEXTSPLIT(CONCAT(b&",",","),","))&"*",b,0))),-2),,-1))Excel solution 25 for List WC Champion Years, proposed by Milan Shrimali:
=let(a,tocol(BYCOL(A2:A9,lambda(x,arrayformula(x&"-"& map(B2:B9,lambda(y,split(y,","))))))),b,arrayformula(split(a,"-")),c,filter(b,choosecols(b,2)<>""),sort(arrayformula(if({1,0},choosecols(c,2),choosecols(c,1))),1,0))Excel solution 26 for List WC Champion Years, proposed by Peter Bartholomew:
= LET(
year, SEQUENCE(24,1,2022,-4),
yr, "*" & year & "*",
winner, XLOOKUP(yr, Years&",", Winners, "", 2),
HSTACK(year, winner)
)Excel solution 27 for List WC Champion Years, proposed by Edwin Tisnado:
=LET(
a,
TEXTSPLIT(
ARRAYTOTEXT(
MAP(
A3:A10,
B3:B10,
LAMBDA(
x,
y,
LET(
t,
TEXTSPLIT(
y,
", "
),
TEXTJOIN(
",",
,
t&";"&x
)
)
)
)
),
";",
","
),
SORTBY(
a,
--DROP(
a,
,
-1
),
-1
)
)Excel solution 28 for List WC Champion Years, proposed by El Badlis Mohd Marzudin:
=LET(
a,
TEXTSPLIT(
CONCAT(
B3:B10&", "
),
,
", "
),
SORT(
DROP(
HSTACK(
a+0,
XLOOKUP(
"*"&a&"*",
B3:B10,
A3:A10,
,
2
)
),
-1
),
1,
-1
)
)Excel solution 29 for List WC Champion Years, proposed by Mahmoud Bani Asadi:
=SORT(REDUCE({"Year","Winner"},A3:A10,LAMBDA(a,c,VSTACK(a,IF({1,0},TEXTSPLIT(OFFSET(c,,1),,", "),c)))),,-1)Excel solution 30 for List WC Champion Years, proposed by Amardeep Singh:
=LET(
w,
A3:A10,
y,
B3:B10,
h,
A2:B2,
a,
TEXTSPLIT(
TEXTJOIN(
"#",
,
w&", "&y
),
", ",
"#"
),
b,
w&"-"&DROP(
a,
,
1
),
c,
TOCOL(
b,
2
),
d,
HSTACK(
--TEXTAFTER(
c,
"-"
),
TEXTBEFORE(
c,
"-"
)
),
VSTACK(
SORTBY(
h,
SEQUENCE(
,
2
),
-1
),
SORT(
d,
,
-1
)
)
)Excel solution 31 for List WC Champion Years, proposed by Songglod P.:
=LET(
y,
TRIM(
TEXTSPLIT(
ARRAYTOTEXT(
B3:B10
),
,
","
)
),
SORT(
HSTACK(
y,
XLOOKUP(
"*"&y&"*",
B3:B10,
A3:A10,
,
2
)
),
1,
-1
)
)Excel solution 32 for List WC Champion Years, proposed by Tamer Salem Shabib –:
=LET(y, --MID(B3:B10, SEQUENCE(, 5, ,6), 4), VSTACK(D2:E2, TOCOL (--y&A3:A10, 2), {1, 5}, {4, 9}), , -1)))Excel solution 33 for List WC Champion Years, proposed by Tomasz Jakóbczyk:
=SORT(TRANSPOSE(TRIM(TEXTSPLIT(TEXTJOIN(",",TRUE,B3:B10),",",,TRUE))),1,-1)
In E3:
=XLOOKUP("*"&D3#&"*",B3:B10,A3:A10,,2,1)Excel solution 34 for List WC Champion Years, proposed by Josh Brodrick:
=SORT(HSTACK(TRIM(TEXTSPLIT(CONCAT(B3:B10&","),,",")),TEXTSPLIT(CONCAT(MAP(A3:A10,SUBSTITUTE(B3:B10,", ",""),LAMBDA(x,y,REPT(x&",",LEN(y)/4)))),,",")),,-1)Excel solution 35 for List WC Champion Years, proposed by Bevon Clarke:
=LET(winners,VSTACK(A3:A10,""),a,B3:B10,years,IFERROR(--TEXTSPLIT(TEXTJOIN("|",TRUE,a),", ","|",TRUE,,""),""),filt,FILTER(TOCOL(years,3),TOCOL(years,3)<>""),count,BYROW(years,LAMBDA(x,COUNT(x))),scount,VSTACK(1,SCAN(1,count,LAMBDA(a,b,a+b))),s,SEQUENCE(SUM(count)),WinnerList,XLOOKUP(s,scount,winners,,-1),Answer,SORT(HSTACK(filt,WinnerList),1,-1),Answer)Excel solution 36 for List WC Champion Years, proposed by Tyler Cameron:
=LET(b,B3:B10,a,SORT(TEXTSPLIT(ARRAYTOTEXT(b),,", "),,-1),HSTACK(a,MAP(a,LAMBDA(x,CONCAT(IF(IFERROR(SEARCH(x,b),0)>0,A3:A10,""))))))Excel solution 37 for List WC Champion Years, proposed by Ogunronbi Taiwo Fisayo:
REDUCE(
SORT(
A2:B2,
,
-1,
1
),
A3:A10,
LAMBDA(
x,
y,
VSTACK(
x,
IFERROR(
HSTACK(
TEXTSPLIT(
OFFSET(
y,
,
1
),
,
", "
),
y
),
y
)
)
)
)Excel solution 38 for List WC Champion Years, proposed by Vijay Agarwal:
=LET(a,A3:A10,b,B3:B10,c,MAX(LEN(b)-LEN(SUBSTITUTE(b,",",""))+1),d,SEQUENCE(,c),e,TRIM(IFERROR(TEXTSPLIT(TEXTAFTER(","&b&",",",",d),","),"")),f,TOCOL(IF(d,a)),g,TOCOL(e),h,SORT(FILTER(HSTACK(g,f),g<>""),,-1),h)Excel solution 39 for List WC Champion Years, proposed by Kuzivakwashe Jokomo:
=IFNA(
TRIM(
SORT(
TEXTSPLIT(
CONCAT(
SUBSTITUTE(
CONCATENATE(
B2:B10,
"|"&A2:A10&" "
),
",",
"|"&A2:A10
)
),
"|",
" "
),
,
-1,
)
),
""
)Solving the challenge of List WC Champion Years with Python
Python solution 1 for List WC Champion Years, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "498 Soccer Champions Alignment.xlsx"
input = pd.read_excel(path, usecols= "A:B", skiprows= 1, nrows = 8)
test = pd.read_excel(path, usecols= "D:E", skiprows= 1)
result = input.copy()
result['Years'] = result['Years'].str.split(', ')
result = result.explode('Years').astype({'Years': 'int64'})
.sort_values('Years', ascending=False).reset_index(drop= True)
.rename(columns = {'Years': 'Year', 'Winners': 'Winner'})
.reindex(columns = ['Year', 'Winner'])
print(result.equals(test)) # True
Solving the challenge of List WC Champion Years with Python in Excel
Python in Excel solution 1 for List WC Champion Years, proposed by Abdallah Ally:
df = xl("A2:B10", headers=True)
# Perform data wrangling
df.columns = [col[: -1] for col in df.columns]
df['Year'] = df['Year'].map(lambda x: x.split(', '))
df = df.explode(column='Year')
df = df.sort_values(by='Year', ascending=False, ignore_index=True)
df = df[['Year', 'Winner']]
df
Python in Excel solution 2 for List WC Champion Years, proposed by Anshu Bantra:
Python in Excel
df = xl("A2:B10", headers=True).dropna()
lst = [('Year', 'Winner')]
for _ in range(len(df)):
for yr in df.loc[_,'Years'].split(','):
lst.append( (yr.strip(),df.loc[_,'Winners']) )
lst = sorted(lst, key=lambda x: x[0], reverse=True)
lst
Solving the challenge of List WC Champion Years with R
R solution 1 for List WC Champion Years, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/498 Soccer Champions Alignment.xlsx"
input = read_xlsx(path, range = "A2:B10")
test = read_xlsx(path, range = "D2:E24")
result = input %>%
separate_rows(Years, sep = ",") %>%
mutate(Years = as.numeric(Years)) %>%
arrange(desc(Years)) %>%
select(Year = Years, Winner = Winners)
identical(result, test)
# [1] TRUE
&&