Rank regions on the basis of values given for each year and list the regions for maximum occurrences of Rank 1, 2 and 3 in different years. Ex. Region3, Region8, Region14 occurred for rank 1, 3 times which was maximum occurrence of rank 1 in different years Region3 in 2020, 2021 & 2022 Region8 in 2018, 2019 & 2020 Region14 in 2019, 2022 & 2024
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 431
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Rank Regions by Yearly Scores with Power Query
_x000D_Power Query solution 1 for Rank Regions by Yearly Scores, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rank = Table.FromColumns(
{
{1, 2, 3},
List.Transform(
List.Zip(
List.Transform(
List.Zip(
List.Transform(
List.Skip(Table.ToColumns(Source)),
(l) =>
List.Transform(l, each List.PositionOf(List.Distinct(List.Sort(l, - 1)), _) + 1)
)
),
each List.Transform({1, 2, 3}, (n) => List.Count(List.Select(_, each _ = n)))
)
),
(l) =>
Text.Combine(
List.Transform(List.PositionOf(l, List.Max(l), 2), (n) => Source[Region]{n}),
", "
)
)
},
{"Rank", "Region"}
)
in
RankPower Query solution 2 for Rank Regions by Yearly Scores, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
G = Table.Group, L = List.Sort, F = Table.SelectRows,
U = Table.UnpivotOtherColumns(S, {"Region"}, "Y", "V"),
T = Table.TransformColumnTypes(U,{{"Y", Int64.Type}, {"V", Int64.Type}}),
P = hashtag#table({"Rank"}, {{1}, {2}, {3}}),
R = Table.AddColumn(P, "Region", each
let
v = _[Rank] - 1,
H = G(T, "Y", {"A", each
let
a = L(List.Distinct(_[V]), -1){v}
in
F(_, each _[V] = a)[Region]
})[[A]],
E = G(Table.ExpandListColumn(H, "A"), "A", {"C", List.Count}),
a = L(E[C], -1){0}
in
Text.Combine(F(E, each [C] = a)[A], ", ")
)
in
R
Blessings!
Power Query solution 3 for Rank Regions by Yearly Scores, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Type = Table.TransformColumns(Source, {"Region", Text.From}, Number.From),
Ranks = 3,
Region = Type[Region],
Years = List.Skip(Table.ToColumns(Type)),
Generate = List.Generate(
() => [a = - 1],
each [a] < Ranks,
each [
a = [a] + 1,
b = List.Transform(
Years,
(f) =>
[
D = List.Distinct(f),
S = List.Sort(D, 1),
P = List.PositionOf(f, S{a}, Occurrence.All),
R = List.Transform(P, (x) => Region{x})
][R]
),
c = List.Combine(b),
d = List.Sort(List.Modes(c), (x) => List.PositionOf(Region, x))
],
each [Rank = [a] + 1, Regions = Text.Combine([d], ", ")]
),
Return = Table.FromRecords(List.Skip(Generate))
in
ReturnPower Query solution 4 for Rank Regions by Yearly Scores, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Type = Table.TransformColumnTypes(Source, List.Transform(Years, each {_, Int64.Type})),
Years = List.Skip(Table.ColumnNames(Source)),
Combine = Table.Combine(
List.Transform(
Years,
each Table.SelectColumns(
Table.SelectRows(
Table.AddRankColumn(Type, "Rank", {_, Order.Descending}, [RankKind = RankKind.Dense]),
each [Rank] < 4
),
{"Region", "Rank"}
)
)
),
Group = Table.Group(Combine, {"Region", "Rank"}, {{"A", each List.Count([Rank])}}),
Sol = Table.Group(
Group,
{"Rank"},
{
{
"Regions",
each
let
a = List.Max([A]),
b = Text.Combine(Table.SelectRows(_, each [A] = a)[Region], ", ")
in
b
}
}
)
in
SolPower Query solution 5 for Rank Regions by Yearly Scores, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
TG = Table.Group,
UnPivot = Table.UnpivotOtherColumns(Source, {"Region"}, "Year", "Amount"),
GroupBy = TG(UnPivot, {"Year"}, {{"Tab", each _}}),
Transf = Table.TransformColumns(GroupBy, {"Tab", each
let
Rank = Table.AddRankColumn(_, "Rank", {"Amount", Order.Descending},
[RankKind = RankKind.Dense]),
FilterRank = Table.SelectRows(Rank, each [Rank] < 4)
in
FilterRank
})[[Tab]],
Exp = Table.ExpandTableColumn(Transf, "Tab", {"Region", "Rank"}, {"Region", "Rank"}),
GroupByOc = TG(Exp, {"Rank", "Region"}, {{"Ocurrence", each Table.RowCount(_), Int64.Type}}),
GroupFinal = TG(GroupByOc, {"Rank"}, {{"Ocurr", each _}}),
Result = Table.AddColumn(GroupFinal, "Regions", each
let
a = List.Max([Ocurr][Ocurrence]),
b = Table.SelectRows([Ocurr], each [Ocurrence] = a),
c = Table.AddColumn(b, "Reg", each Number.From(Text.Select([Region],{"0".."9"}))),
d = Table.Sort(c, {"Reg", Order.Ascending})
in
Text.Combine(d[Region], ", "))[[Rank], [Regions]]
in
Result
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 6 for Rank Regions by Yearly Scores, proposed by Arnaud Duvernois:
let
Source = Excel.CurrentWorkbook(){[Name = "Tableau1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Region"}, "Attribut", "Valeur"),
GroupBy = Table.Group(
Unpivot,
{"Attribut"},
{
{
"Nombre",
each
let
a = List.FirstN(List.Sort(List.Distinct(_[Valeur]), Order.Descending), 3)
in
Table.AddColumn(_, "Rank", each List.PositionOf(a, [Valeur]) + 1)
}
}
),
SelectRows = Table.SelectRows(Table.Combine(GroupBy[Nombre]), each [Rank] <> 0),
Result = Table.Group(
SelectRows,
{"Rank"},
{
{
"Region",
each
let
a = Table.Group(_, {"Region"}, {{"Number", (s) => Table.RowCount(s)}})
in
Text.Combine(Table.SelectRows(a, each [Number] = List.Max(a[Number]))[Region], ", ")
}
}
),
Sort = Table.Sort(Result, {{"Rank", Order.Ascending}})
in
SortSolving the challenge of Rank Regions by Yearly Scores with Excel
_x000D_Excel solution 1 for Rank Regions by Yearly Scores, proposed by Bo Rydobon 🇹🇭:
=HSTACK(
{1;2;3},
MAP(
{1;2;3},
LAMBDA(
n,
INDEX(
GROUPBY(
BYROW(
N(
-B2:H20=BYCOL(
-B2:H20,
LAMBDA(
x,
SMALL(
UNIQUE(
x
),
n
)
)
)
),
SUM
),
A2:A20,
ARRAYTOTEXT,
,
0,
-1
),
1,
2
)
)
)
)Excel solution 2 for Rank Regions by Yearly Scores, proposed by John V.:
=HSTACK(
{1;2;3},
MAP(
{1;2;3},
LAMBDA(
x,
LET(
v,
--B2:H20,
f,
BYROW(
N(
v=BYCOL(
v,
LAMBDA(
c,
LARGE(
UNIQUE(
c
),
x
)
)
)
),
SUM
),
TEXTJOIN(
", ",
,
REPT(
A2:A20,
MAX(
f
)=f
)
)
)
)
)
)Excel solution 3 for Rank Regions by Yearly Scores, proposed by محمد حلمي:
=MAP(SEQUENCE(
3
),
LAMBDA(v,
LET(j,
DROP(
REDUCE(
0,
SEQUENCE(
7
),
LAMBDA(
a,
d,
LET(
x,
--CHOOSECOLS(
B2:H20,
d
),
VSTACK(
a,
FILTER(
A2:A20,
x=LARGE(
UNIQUE(
x
),
v
)
)
)
)
)
),
1
),
u,
UNIQUE(
j
),
k,
SORTBY(
u,
--MID(
u,
7,
9
)
),
i,
MAP(k,
LAMBDA(a,
SUM(--(a=j)))),
TEXTJOIN(
", ",
,
REPT(
k,
i=MAX(
i
)
)
))))Excel solution 4 for Rank Regions by Yearly Scores, proposed by Kris Jaganah:
=LET(a,
A2:A20,
b,
--B2:H20,
c,
B1:H1,
d,
TOCOL(
b&c&a
),
e,
--MID(
d,
1,
3
),
f,
--MID(
d,
4,
4
),
g,
MID(
d,
8,
8
),
h,
TOROW(
UNIQUE(
f
)
),
i,
DROP(
REDUCE(
"",
h,
LAMBDA(
x,
y,
HSTACK(
x,
TAKE(
SORT(
UNIQUE(
FILTER(
e,
y=f
)
),
,
-1
),
3
)
)
)
),
,
1
),
j,
TOCOL(
h&i
),
k,
INT(
SEQUENCE(
ROWS(
j
),
,
,
1/COUNT(
h
)
)
),
l,
XLOOKUP(
f&e,
j,
k,
0
),
m,
{1;2;3},
HSTACK(m,
MAP(m,
LAMBDA(z,
LET(p,
FILTER(
g,
l=z
),
q,
UNIQUE(
p
),
r,
MAP(q,
LAMBDA(zz,
SUM(--(p=zz)))),
ARRAYTOTEXT(
FILTER(
q,
r=MAX(
r
)
)
))))))Excel solution 5 for Rank Regions by Yearly Scores, proposed by Julian Poeltl:
=LET(T,
A1:H20,
TT,
DROP(
T,
1,
1
)*1,
R,
DROP(
TAKE(
T,
,
1
),
1
),
RN,
LAMBDA(
R,
BYCOL(
TT,
LAMBDA(
A,
CHOOSEROWS(
UNIQUE(
SORT(
A,
,
-1
)
),
R
)
)
)
),
AT,
{1,
2,
3},
Re,
MAP(AT,
LAMBDA(C,
TEXTJOIN(", ",
,
FILTER(R,
LET(B,
BYROW(--(TT=RN(
C
)),
LAMBDA(
A,
SUM(
A
)
)),
B=MAX(
B
)))))),
HSTACK(
VSTACK(
"Rank",
AT
),
VSTACK(
"Regions",
Re
)
))Excel solution 6 for Rank Regions by Yearly Scores, proposed by Timothée BLIOT:
=LET(H,
HSTACK,
R,
REDUCE,
I,
INDEX,
T,
B2:H20,
D,
DROP(R("",
SEQUENCE(
COLUMNS(
T
)
),
LAMBDA(a,
v,
H(a,
MAP(I(
T,
,
v
),
LAMBDA(x,
1+SUM(--(xExcel solution 7 for Rank Regions by Yearly Scores, proposed by Oscar Mendez Roca Farell:
=LET(
r,
{1;2;3},
HSTACK(
r,
MAP(
r,
LAMBDA(
a,
TEXTJOIN(
", ",
,
"Region"&MODE.MULT(
--TEXTSPLIT(
CONCAT(
BYCOL(
B2:H20,
LAMBDA(
c,
TEXTJOIN(
", ",
,
REPT(
MID(
A2:A20,
7,
3
),
c=LARGE(
UNIQUE(
c
),
a
)
)
)
)
)&", "
),
& ,
", ",
1
)
)
)
)
)
)
)Excel solution 8 for Rank Regions by Yearly Scores, proposed by Duy Tùng:
=LET(
a,
{1;2;3},
b,
B2:H20,
HSTACK(
a,
MAP(
a,
LAMBDA(
v,
@DROP(
GROUPBY(
BYROW(
N(
b=BYCOL(
b,
LAMBDA(
x,
LARGE(
UNIQUE(
x
),
v
)
)
)
),
SUM
),
A2:A20,
ARRAYTOTEXT,
,
,
-1
),
,
1
)
)
)
)
)Excel solution 9 for Rank Regions by Yearly Scores, proposed by Sunny Baggu:
=LET(
_v,
DROP(
REDUCE(
"",
SEQUENCE(
COLUMNS(
B1:H1
)
),
LAMBDA(
a,
v,
HSTACK(
a,
XMATCH(
INDEX(
B2:H20,
,
v
),
UNIQUE(
SORT(
INDEX(
B2:H20,
,
v
),
,
-1
)
)
)
)
)
),
,
1
),
_s,
SEQUENCE(
3
),
HSTACK(
_s,
MAP(
_s,
LAMBDA(
x,
ARRAYTOTEXT(
LET(
_r,
BYROW(
N(
_v = x
),
LAMBDA(
a,
SUM(
a
)
)
),
FILTER(
A2:A20,
_r = MAX(
_r
)
)
)
)
)
)
)
)Excel solution 10 for Rank Regions by Yearly Scores, proposed by Asheesh Pahwa:
=LET(n,
B2:H20,
d,
DROP(REDUCE("",
SEQUENCE (COLUMNS(
n
)),
LAMBDA(
a,
v,
HSTACK(
a,
LET(
I,
INDEX(
n,
,
v
),
u,
UNIQUE(
I
),
L,
LARGE(
u,
SEQUENCE(
3
)
),
m,
XMATCH(
I,
L
),
m
)
)
)),
,
1),
f,
IFNA(
d,
""
),
DROP(REDUCE("",
SEQUENCE(
3
),
LAMBDA
(x,
y,
VSTACK(
x,
LET(
a,
IF(
f=y,
1,
0
),
b,
BYROW(
a,
LAMBDA(
x,
SUM(
x
)
)
),
HSTACK(
y,
ARRAYTOTEXT(
FILTER(
A2:A20,
b>2
)
)
)
)
))),
1))Excel solution 11 for Rank Regions by Yearly Scores, proposed by Charles Roldan:
=LET(Years,
B1:H1,
Regions,
A2:A20,
Data,
B2:H20,
Headers,
J2:K2,
REDUCE(Headers,
{1;2;3},
LAMBDA(m,
n,
VSTACK(m,
HSTACK(n,
ARRAYTOTEXT(FILTER(Regions,
LAMBDA(
x,
x = MAX(
x
)
)(BYROW(
Regions = TOROW(
DROP(
REDUCE(
"",
SEQUENCE(
COLUMNS(
Years
)
),
LAMBDA(
a,
b,
VSTACK(
a,
FILTER(
Regions,
LET(
Vals,
CHOOSECOLS(
Data,
b
),
Vals = LARGE(
UNIQUE(
Vals
),
n
)
)
)
)
)
),
1
)
),
LAMBDA(
x,
SUM(
--x
)
)
)))))))))Excel solution 12 for Rank Regions by Yearly Scores, proposed by Ankur Sharma:
=LET(a, A2:A20, b, TEXTSPLIT(ARRAYTOTEXT(BYCOL(B2:H20, LAMBDA(c, ARRAYTOTEXT(FILTER(a, c >= LARGE(UNIQUE(c), 3)))))), , ", "), d, MAP(a, LAMBDA(e, SUM(--(b = e)))), f, UNIQUE(d), g, {1;2;3}, HSTACK(g, MAP(g, LAMBDA(h, ARRAYTOTEXT(FILTER(a, d = LARGE(f, h)))))))Excel solution 13 for Rank Regions by Yearly Scores, proposed by Pieter de Bruijn:
=LET(
x,
LAMBDA(
y,
BYROW(
MAKEARRAY(
19,
7,
LAMBDA(
r,
c,
LET(
a,
--B2:H20,
SUM(
N(
INDEX(
a,
r,
c
)<=UNIQUE(
INDEX(
a,
,
c
)
)
)
)
)
)
),
LAMBDA(
w,
SUM(
N(
w=y
)
)
)
)
),
LET(
z,
LAMBDA(
y,
ARRAYTOTEXT(
FILTER(
A2:A20,
x(
y
)=MAX(
x(
y
)
)
)
)
),
LET(
d,
{1;2;3},
HSTACK(
d,
MAP(
d,
z
)
)
)
)
)Excel solution 14 for Rank Regions by Yearly Scores, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(r,
DROP(
REDUCE(
"",
SEQUENCE(
COLUMNS(
B2:H20
)
),
LAMBDA(
b,
x,
VSTACK(
b,
REDUCE(
"",
{1,
2,
3},
LAMBDA(
a,
y,
HSTACK(
a,
TOCOL(
FILTER(
A2:A20,
--CHOOSECOLS(
B2:H20,
x
)=LARGE(
UNIQUE(
--CHOOSECOLS(
B2:H20,
x
)
),
y
)
)
)
)
)
)
)
)
),
,
1
),
REDUCE({"Rank",
"Regions"},
{1;2;3},
LAMBDA(a,
i,
VSTACK(a,
HSTACK(i,
LET(t,
MAP(A2:A20,
LAMBDA(x,
LET(m,
TOCOL(
CHOOSECOLS(
r,
i
),
3
),
SUM(--(m=x))))),
TEXTJOIN(
", ",
,
FILTER(
A2:A20,
t=MAX(
t
)
)
)))))))Excel solution 15 for Rank Regions by Yearly Scores, proposed by Alexandra Popoff:
= LAMBDA(
In_Region,
In_Year,
In_Data,
[In_MaxRank],
LET(
z_Region,
In_Region,
z_Year,
TRANSPOSE(
In_Year
),
z_Data,
VALUE(
In_Data
),
z_Max_Rank,
if(
ISOMITTED(
In_MaxRank
),
3,
In_MaxRank
),
z_n_Reg,
ROWS(
z_Region
),
z_Rank,
MAKEARRAY(
z_n_Reg,
ROWS(
z_Year
),
LAMBDA(
z_y,
z_x,
XMATCH(
INDEX(
z_Data,
z_y,
z_x
),
SORT(
UNIQUE(
INDEX(
z_Data,
,
z_x
)
),
,
-1
)
)
)
),
z_Top,
MAKEARRAY(
z_n_Reg,
z_Max_Rank,
LAMBDA(
z_y,
z_x,
SUM(
N(
TRANSPOSE(
INDEX(
z_Rank,
z_y
)
) = z_x
)
)
)
),
z_Out,
BYROW(
SEQUENCE(
z_Max_Rank,
1,
1,
1
),
LAMBDA(
z_i,
LET(
z_Col,
INDEX(
z_Top,
,
INDEX(
z_i,
1,
1
)
),
TEXTJOIN(
", ",
TRUE,
FILTER(
z_Region,
z_Col = MAX(
z_Col
)
)
)
)
)
),
z_Out
)
)Solving the challenge of Rank Regions by Yearly Scores with Python
_x000D_Python solution 1 for Rank Regions by Yearly Scores, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel("431 Top 3 Rankings.xlsx", usecols="A:H", nrows=20)
test = pd.read_excel("431 Top 3 Rankings.xlsx", usecols="J:K", nrows=3, skiprows=1)
result = input.melt(id_vars=["Region"], var_name="year", value_name="result")
result["Rank"] = result.groupby("year")["result"].rank("dense", ascending=False).astype('int64')
result = result[result["Rank"] <= 3]
result = result.groupby(["Region", "Rank"]).size().reset_index(name="n")
result = result.groupby("Rank").apply(lambda x: x[x["n"] == x["n"].max()]).reset_index(drop=True)
result["RegionNo"] = result["Region"].str.extract(r"(d+)").astype(int)
result = result.sort_values(["Rank", "RegionNo"])
result = result.groupby("Rank")["Region"].apply(lambda x: ", ".join(x)).reset_index(name="Regions")
print(result.equals(test)) # True
Solving the challenge of Rank Regions by Yearly Scores with Python in Excel
_x000D_Python in Excel solution 1 for Rank Regions by Yearly Scores, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_431 - Top 3 Rankings.xlsx'
df = pd.read_excel(file_path, usecols='A:H')
# Perform data transformation and cleansing
for col in df.columns[1: ]:
df[col] = df[col].rank(ascending=False, method='dense').astype(int)
items = {}
for rank in range(1, 4):
ranks = []
items[rank] = []
for row in df.loc[:, :'2024'].iterrows():
ranks.append(sum([x for x in row[1] if x == rank]))
df[rank] = pd.Series(ranks)
regions = df['Region'][df[rank] == max(df[rank])]
items[rank] = ', '.join(regions)
items = {'Rank': items.keys(), 'Regions': items.values()}
df = pd.DataFrame(items)
print(df')
Solving the challenge of Rank Regions by Yearly Scores with R
_x000D_R solution 1 for Rank Regions by Yearly Scores, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/431 Top 3 Rankings.xlsx", range = "A1:H20")
test = read_excel("Excel/431 Top 3 Rankings.xlsx", range = "J2:K5")
result = input %>%
pivot_longer(cols = -c(1), names_to = "year", values_to = "result") %>%
mutate(Rank = dense_rank(desc(result)) %>% as.numeric(), .by = year) %>%
filter(Rank <= 3) %>%
summarise(n = n_distinct(year), .by = c("Region", "Rank")) %>%
mutate(check = n == max(n), .by = "Rank") %>%
filter(check) %>%
summarise(Regions = paste(Region, collapse = ", "), .by = "Rank"&) %>%
arrange(Rank)