In the Question Table, values are provided and we want to rank them in ascending order based on custom logic. 1- Set rank = 0. 2- In each iteration, among all non-ranked values smaller than 50, select the largest one and call it a. 3- Also, among all non-ranked values greater than 50, select the smallest one and call it b. 4- Calculate the absolute distance of a and b from the number 50. 5- Rank the value with the smaller distance as rank + 1 and the other value as rank + 2. 6- Set rank = rank + 2 and go to step 2. For example, in the first iteration, the numbers 47 and 51 are selected. Since 51 is closer to 50, it is ranked 1, and 47 is ranked 2. In the second iteration, 52 and 45 are selected. Since 52 is closer to 50, it is ranked 3, and 45 is ranked 4.
📌 Challenge Details and Links
Challenge Number: 85
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Custom Ranking! with Power Query
Power Query solution 1 for Custom Ranking!, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.AddColumn(
Source,
"Rank",
each List.PositionOf(
List.TransformMany(
List.Zip(
{
List.Reverse(List.FirstN(Source[Values], each _ < 50)),
List.LastN(Source[Values], each _ > 50)
}
),
each List.Sort(List.RemoveNulls(_), each Number.Abs(_ - 50)),
(i, _) => _
),
[Values]
)
+ 1
)
in
SPower Query solution 2 for Custom Ranking!, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Index1 = Table.AddIndexColumn(Source, "Index"),
ToRow = Table.ToRows(Index1),
Above = List.Sort(List.Select(ToRow, each _{0} > 50), {each _{0}, each _{1}}),
Below = List.Sort(List.Select(ToRow, each _{0} < 50), {{each _{0}, 1}, each _{1}}),
Zip = List.Zip({Above, Below}),
Sort = List.Transform(Zip, each List.Sort(_, (f) => Number.Abs(f{0}? ?? 0 - 50))),
Combine = List.RemoveNulls(List.Combine(Sort)),
Table = Table.FromRows(Combine, Value.Type(Index1)),
Rank = Table.AddIndexColumn(Table, "Rank", 1),
Return = Table.Sort(Rank, "Index")[[Values], [Rank]]
in
ReturnPower Query solution 3 for Custom Ranking!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Lista = List.Transform(List.Zip({List.Reverse(List.Sort(List.Select(Source[Values],
each _<50))), List.Select(Source[Values], each _>50)}), each List.RemoveNulls(_)),
Order = List.Transform(List.Combine(List.Transform(Lista,
each List.Sort(List.Transform(_, (x)=> {x,Number.Abs(x-50)}), each _{1}))), each _{0}),
Rank = {1..List.Count(Order)},
Sol = Table.Sort(Table.FromRows(List.Zip({Order, Rank}), {"Values", "Rank"}),
each List.PositionOf(Source[Values], [Values]))
in
SolPower Query solution 4 for Custom Ranking!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = List.Select(S[Values], each _ >= 50),
B = List.Sort(List.Select(S[Values], each _ < 50), Order.Descending),
C = List.Zip({A, B}),
D = Table.FromList(C, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
E = Table.RenameColumns(D, {{"Column1", "Values"}}),
F = Table.AddColumn(
E,
"T",
each Table.Sort(
Table.FromColumns(
{
List.RemoveNulls([Values]),
List.Transform(List.RemoveNulls([Values]), each Number.Abs(_ - 50))
},
{"Values", "InR"}
),
{"InR", Order.Ascending}
)
),
G = Table.ExpandTableColumn(F, "T", {"Values"}, {"Values.1"}),
H = Table.AddIndexColumn(G, "Rank", 1, 1, Int64.Type),
I = Table.NestedJoin(S, {"Values"}, H, {"Values.1"}, "T"),
J = Table.ExpandTableColumn(I, "T", {"Rank"}, {"Rank"}),
#"Sorted Rows" = Table.Sort(J, {{"Values", Order.Ascending}})
in
#"Sorted Rows"Solving the challenge of Custom Ranking! with Excel
Excel solution 1 for Custom Ranking!, proposed by Bo Rydobon 🇹🇭:
=LET(
n,
B3:B23,
s,
ABS(
SEQUENCE(
ROWS(
n
)
)-MATCH(
50,
n
)-0.5
)+ABS(
n-50
)%,
MATCH(
s,
SORT(
s
)
)
)Excel solution 2 for Custom Ranking!, proposed by محمد حلمي:
=LET(
b,
B3:B20,
u,
UNIQUE(
b
),
HSTACK(
u,
MAP(
u,
LAMBDA(
a,
INDEX(
FILTER(
C3:C20,
b=a
),
RANDBETWEEN(
1,
COUNTIF(
b,
a
)
)
)
)
)
)
)Excel solution 3 for Custom Ranking!, proposed by Julian Poeltl:
=LET(
V,
B3:B23,
L,
FILTER(
V,
V>50
),
RL,
ROWS(
L
),
S,
SORT(
FILTER(
V,
V<50
),
,
-1
),
RS,
ROWS(
S
),
PL,
IFERROR(
IF(
L-50<50-S,
1,
2
),
IF(
RL>RS,
1,
0
)
),
PS,
IFERROR(
IF(
L-50>50-S,
1,
2
),
IF(
RL0
)+SEQUENCE(
RL,
,
0,
2
),
SS,
FILTER(
PS,
PS>0
)+SEQUENCE(
RS,
,
0,
2
),
H,
HSTACK(
VSTACK(
L,
S
),
VSTACK(
LL,
SS
)
),
SORT(
HSTACK(
TAKE(
SORT(
H,
2
),
,
1
),
SEQUENCE(
RS+RL
)
)
)
) Excel solution 4 for Custom Ranking!, proposed by Kris Jaganah:
=LET(a,B3:B23,b,a-50,c,SCAN(,IF(b<0,1,-1),SUM),d,IF(b>0,c+1,c),e,ABS(b),f,XLOOKUP(d,d,e),g,XLOOKUP(d,d,e,,,-1),h,IF(b<0,g,f),i,IF(e<=h,1,2),j,SORTBY(a,f,1,i,1),XMATCH(a,j))Excel solution 5 for Custom Ranking!, proposed by Imam Hambali:
=LET( a,
B3:B23, fl,
LAMBDA(
x,
SORT(
FILTER(
a,
IF(
x=-1,
a<50,
IF(
x=1,
a>50
)
)
),
1,
x
)
), b,
HSTACK(
fl(
-1
),
fl(
1
)
), c,
IFERROR(
ABS(
b-50
),
100
), cl,
LAMBDA(
x,
DROP(
c,
,
x
)
), d,
LAMBDA(
x,
y,
IF(
xExcel solution 6 for Custom Ranking!, proposed by Bilal Mahmoud kh.:
=LET(a,
FILTER(
B3:B23,
B3:B23>50
),
b,
SORT(
FILTER(
B3:B23,
B3:B23<50
),
,
-1
),
c,
DROP(REDUCE(,
SEQUENCE(
COUNT(
b
)+1,
,
0
),
LAMBDA(x,
y,
IFERROR(IF((INDEX(
b,
y,
1
))-50 > (50-INDEX(
a,
y,
1
)),
VSTACK(
x,
INDEX(
b,
y,
1
),
INDEX(
a,
y,
1
)
),
VSTACK(
x,
INDEX(
a,
y,
1
),
INDEX(
b,
y,
1
)
)),
VSTACK(
x,
INDEX(
b,
y,
1
)
)))),
1),
SORT(
HSTACK(
c,
SEQUENCE(
21
)
),
1
))Solving the challenge of Custom Ranking! with Python
Python solution 1 for Custom Ranking!, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-085 Custome Ranking.xlsx"
input = pd.read_excel(path, usecols="B", skiprows=1)
input["Values"] = input["Values"].apply(lambda x: 56 if x == 51 else x)
test = pd.read_excel(path, usecols="F:G", skiprows=1)
test.columns = test.columns.str.replace(".1", "")
ab50 = input[input["Values"] > 50].sort_values("Values")["Values"].tolist()
be50 = input[input["Values"] < 50].sort_values("Values", ascending=False)["Values"].tolist()
if len(ab50) > len(be50):
be50 += [None] * (len(ab50) - len(be50))
else:
ab50 += [None] * (len(be50) - len(ab50))
df = pd.DataFrame({"ab": ab50, "be": be50})
df["nr"] = df.index + 1
df = df.melt(id_vars="nr", value_vars=["ab", "be"], var_name="type", value_name="Values")
df = df.dropna().reset_index(drop=True)
df["t2"] = df["Values"] - 50 > 0
df = df.sort_values(["nr", "t2"], ascending=[True, False]).reset_index(drop=True)
df["Rank"] = df.index + 1
df = df[["Values", "Rank"]].sort_values("Values")Solving the challenge of Custom Ranking! with R
R solution 1 for Custom Ranking!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-085 Custome Ranking.xlsx"
input = read_excel(path, range = "B2:B23") %>%
mutate(Values = if_else(Values == 51, 56, Values))
test = read_excel(path, range = "F2:G23")
ab50 = input %>% filter(Values > 50) %>% arrange(Values) %>% pull(Values)
be50 = input %>% filter(Values < 50) %>% arrange(desc(Values)) %>% pull(Values)
if (length(ab50) > length(be50)) {
be50 = c(be50, rep(NA, length(ab50) - length(be50)))
} else {
ab50 = c(ab50, rep(NA, length(be50) - length(ab50)))
}
df = data.frame(ab = ab50, be = be50) %>%
mutate(nr = row_number()) %>%
pivot_longer(cols = c(ab, be), names_to = "type", values_to = "Values") %>%
na.omit() %>%
mutate(t2 = Values - 50 > 0) %>%
arrange(nr, desc(t2)) %>%
mutate(Rank = row_number()) %>%
select(Values, Rank) %>%
arrange(Values)
R solution 2 for Custom Ranking!, proposed by Anil Kumar Goyal:
library(dplyr)
df <- data.frame(
Values = c(22L,25L,27L,28L,30L,34L,
40L,42L,43L,44L,45L,47L,51L,52L,60L,63L,64L,67L,
68L,69L,74L)
)
df |>
mutate(Dummy = 50 - Values > 0) |>
mutate(Rank = dense_rank(abs(50 - Values)), .by = Dummy) |>
mutate(precedence = dense_rank(abs(50 - Values)), .by = Rank) |>
mutate(Values,
Rank = dense_rank(pick(Rank, precedence)),
.keep = "none")