The distance between two cities is given in the grid. Find the top 3 pair of cities with minimum distance and rank them and sort them from 1 to 3 rank, From City and To City. (Exclude 0 distance as that is between same cities)
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 446
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Top 3 Closest City Pairs with Power Query
Power Query solution 1 for Top 3 Closest City Pairs, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
P = List.TransformMany(
Table.ToRows(Source),
each List.Skip(
List.Zip({Table.ColumnNames(Source), _}),
List.PositionOf(Source[Cities], _{0}) + 2
),
(i, _) => {i{0}} & _
),
S = Table.FromRows(
List.TransformMany(
{1, 2, 3},
each List.Select(P, (p) => p{2} = List.Sort(List.Distinct(List.Zip(P){2})){_ - 1}),
(i, _) => {i} & _
),
{"Rank", "From City", "To City", "Distance"}
)
in
SPower Query solution 2 for Top 3 Closest City Pairs, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Cities"}, "To City", "Distance"),
Filter = Table.SelectRows(Unpivot, each [To City] > [Cities]),
Sort = Table.Sort(Filter, {{"Distance", 0}, {"Cities", 0}}),
Xmatch = Table.AddColumn(Sort, "Rank", each List.PositionOf(Sort[Distance], [Distance]) + 1),
Select = Table.SelectRows(Xmatch, each [Rank] <= 3),
Clean = Table.RenameColumns(
Table.ReorderColumns(Select, {"Rank", "Cities", "To City", "Distance"}),
{{"Cities", "From City"}}
)
in
CleanPower Query solution 3 for Top 3 Closest City Pairs, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Cities"}, "To City", "Distance"),
Combine = Table.AddColumn(
Unpivot,
"From-To",
each if [Cities] < [To City] then [Cities] & "-" & [To City] else [To City] & "-" & [Cities]
),
RemoveDup = Table.Distinct(Combine, {"From-To"}),
Remove0 = Table.SelectRows(RemoveDup, each [Distance] > 0),
Rank = Table.AddRankColumn(Remove0, "Rank", {"Distance", 0}, [RankKind = RankKind.Competition]),
Keep = Table.SelectRows(Rank, each [Rank] <= 3),
Select = Table.SelectColumns(Keep, {"Rank", "Cities", "To City", "Distance"}),
Rename = Table.RenameColumns(Select, {{"Cities", "From City"}}),
Sort = Table.Sort(Rename, {{"Rank", 0}, {"From City", 0}, {"To City", 0}})
in
SortPower Query solution 4 for Top 3 Closest City Pairs, proposed by Rick de Groot:
let
Source = Table1,
Unpivot = Table.UnpivotOtherColumns(Source, {"Cities"}, "Attribute", "Value"),
DelNulls = Table.SelectRows(Unpivot, each [Value] <> 0),
ToRows = Table.ToRows(DelNulls),
sortNDistinct = List.Distinct(List.Transform(ToRows, each List.Sort(_))),
ToTable = Table.FromRows(sortNDistinct, {"Distance", "From City", "To City"}),
Filter = Table.SelectRows(
ToTable,
each List.Contains(List.MinN(ToTable[Distance], 3), [Distance])
),
Sort = Table.Sort(Filter, {{"Distance", 0}, {"From City", 0}}),
Rank = Table.AddRankColumn(
Sort,
"Rank",
{"Distance", Order.Ascending},
[RankKind = RankKind.Dense]
)
in
RankPower Query solution 5 for Top 3 Closest City Pairs, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Cities"}, "A", "V"),
SelR = Table.SelectRows(Unpivot, each [V] <> 0),
Col = Table.AddColumn(SelR, "Custom", each Text.Combine(List.Sort({[Cities], [A]}))),
Group = Table.Combine(
Table.Group(
Col,
{"Custom"},
{
{
"A",
each Table.FromRows(
{List.RemoveLastN(Table.ToRows(_){0})},
{"From City", "To City", "Distance"}
)
}
}
)[A]
),
Sort = Table.Sort(
Table.SelectRows(
Table.AddRankColumn(Group, "Rank", {"Distance", Order.Ascending}),
each [Rank] < 4
),
each [Rank]
),
Sol = Table.ReorderColumns(Sort, {"Rank"} & Table.ColumnNames(Group))
in
SolPower Query solution 6 for Top 3 Closest City Pairs, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
nd = Table.UnpivotOtherColumns(Fonte, {"Cities"}, "To City", "Distance"),
add = Table.AddColumn(nd, "Personalizar", each List.Sort({[Cities], [To City]})),
min = List.MinN(List.Distinct(List.Select(add[Distance], each _ <> 0)), 3),
res = Table.AddRankColumn(
Table.Sort(
Table.Distinct(Table.SelectRows(add, each List.Contains(min, [Distance])), {"Personalizar"}),
{"Distance"}
),
"Rank",
{"Distance"}
)[[Rank], [To City], [Cities], [Distance]]
in
resPower Query solution 7 for Top 3 Closest City Pairs, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.RenameColumns(
Table.SelectRows(
Table.UnpivotOtherColumns(Source, {"Cities"}, "To", "Distance"),
each [To] > [Cities]
),
{"Cities", "From"}
),
AddRank = Table.SelectRows(
Table.AddRankColumn(Unpivot, "Rank", {"Distance", Order.Ascending}, [RankKind = RankKind.Dense]),
each [Rank] <= 3
),
Reorder = Table.Sort(
Table.ReorderColumns(AddRank, {"Rank"} & Table.ColumnNames(Unpivot)),
{{"Rank", Order.Ascending}, {"From", Order.Ascending}}
)
in
ReorderPower Query solution 8 for Top 3 Closest City Pairs, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.RenameColumns(
Table.SelectRows(
Table.UnpivotOtherColumns(Source, {"Cities"}, "To", "Distance"),
each [Distance] <> 0
),
{"Cities", "From"}
),
AddSortedList = Table.AddColumn(
Unpivot,
"SortedList",
each List.Sort({[From], [To]}, Order.Ascending)
),
Extract = Table.RemoveColumns(
Table.Distinct(
Table.TransformColumns(
AddSortedList,
{"SortedList", each Text.Combine(List.Transform(_, Text.From), ","), type text}
),
{"SortedList"}
),
"SortedList"
),
AddRank = Table.SelectRows(
Table.AddRankColumn(Extract, "Rank", {"Distance", Order.Ascending}, [RankKind = RankKind.Dense]),
each [Rank] <= 3
),
Reorder = Table.Sort(
Table.ReorderColumns(AddRank, {"Rank"} & Table.ColumnNames(Unpivot)),
{{"Rank", Order.Ascending}, {"From", Order.Ascending}}
)
in
ReorderPower Query solution 9 for Top 3 Closest City Pairs, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.UnpivotOtherColumns(S, {"Cities"}, "A", "V"),
b = List.Distinct(List.Transform(Table.ToRows(a), each List.Sort(_))),
c = Table.FromRows(List.Select(b, each _{0} <> 0)),
d = Table.Group(c, {"Column1"}, {"G", each _}),
e = Table.FirstN(Table.Sort(d, {"Column1", 0}), 3)[[G]],
f = Table.ExpandTableColumn(
Table.AddIndexColumn(e, "Rank", 1),
"G",
{"Column1", "Column2", "Column3"},
{"Column1", "Column2", "Column3"}
),
Sol = Table.RenameColumns(
Table.SelectColumns(f, {"Rank", "Column2", "Column3", "Column1"}),
{{"Column2", "From City"}, {"Column3", "To City"}, {"Column1", "Distance"}}
)
in
SolSolving the challenge of Top 3 Closest City Pairs with Excel
Excel solution 1 for Top 3 Closest City Pairs, proposed by Bo Rydobon 🇹🇭:
=LET(
a,
A2:A8,
b,
B1:H1,
L,
LAMBDA(
x,
TOCOL(
IFS(
aExcel solution 2 for Top 3 Closest City Pairs, proposed by John V.:
=LET(
x,
A2:A8,
y,
B1:H1,
i,
LAMBDA(
a,
TOCOL(
IFS(
xTOROW(
n
)
),
SUM
),
SORT(
FILTER(
HSTACK(
1+b,
i(
x
),
i(
y
),
n
),
b<3
)
)
) Excel solution 3 for Top 3 Closest City Pairs, proposed by محمد حلمي:
=LET(s,
SEQUENCE(
7
),
d,
B2:H8/(TOROW(
s
)>s),
r,
LAMBDA(
x,
TOCOL(
IF(
d,
x
),
2
)
),
i,
r(
d
),
w,
REDUCE(
K2:M2,
SMALL(
i,
{1,
2,
3}
),
LAMBDA(
a,
v,
VSTACK(
a,
FILTER(
HSTACK(
r(
A2:A8
),
r(
B1:H1
),
i
),
i=v
)
)
)
),
e,
TAKE(
w,
,
-1
),
HSTACK(
w,
VSTACK(
J2,
SCAN(
1,
DROP(
e,
1
)>DROP(
e,
-1
),
LAMBDA(
a,
v,
a+v
)
)
)
))Excel solution 4 for Top 3 Closest City Pairs, proposed by 🇰🇷 Taeyong Shin:
=LET(
a,
A2:A8,
b,
B1:H1,
f,
LAMBDA(
y,
TOCOL(
IFS(
aExcel solution 5 for Top 3 Closest City Pairs, proposed by Kris Jaganah:
=LET(
a,
A1:H8,
b,
TAKE(
SORT(
UNIQUE(
TOCOL(
IF(
a=0,
k,
a
),
3
)
)
),
3
),
c,
TEXTSPLIT(
TEXTJOIN(
", ",
,
REDUCE(
"",
b,
LAMBDA(
v,
w,
VSTACK(
v,
BYROW(
DROP(
a,
1
),
LAMBDA(
x,
LET(
a,
FILTER(
TAKE(
a,
1
),
x=w,
""
),
IF(
a<>"",
a&"-"&w&"-"&XMATCH(
w,
b
)&"-"&TAKE(
x,
,
1
),
""
)
)
)
)
)
)
)
),
"-",
", "
),
VSTACK(
{"Rank",
"From City",
"To City",
"Distance"},
UNIQUE(
IF(
TAKE(
c,
,
1
)Excel solution 6 for Top 3 Closest City Pairs, proposed by Kris Jaganah:
=LET(
a,
A1:H8,
b,
DROP(
a,
1,
1
),
c,
DROP(
TAKE(
a,
1
),
,
1
),
d,
DROP(
TAKE(
a,
,
1
),
1
),
e,
ROWS(
b
),
f,
SEQUENCE,
g,
f(
,
e
),
h,
IF(
f(
e
)-g<0,
b,
""
),
i,
SMALL(
TOCOL(
--h,
3
),
{1;2;3}
),
VSTACK(
{"Rank",
"From City",
"To City",
"Distance"},
TEXTSPLIT(
TEXTJOIN(
",",
,
REDUCE(
"",
i,
LAMBDA(
x,
y,
VSTACK(
x,
XMATCH(
y,
i
)&"-"&TOCOL(
IF(
h=y,
d&"-"&c,
1/0
),
3
)&"-"&y
)
)
)
),
"-",
","
)
)
)Excel solution 7 for Top 3 Closest City Pairs, proposed by Julian Poeltl:
=LET(T,
SORT(
L_Flattena2DTableintoColumns(
A1:H8
),
3
),
N,
TAKE(
T,
,
-1
),
F,
FILTER(T,
(N0)),
C,
CHOOSERO&WS(
F,
SEQUENCE(
COUNTA(
F
)/6,
,
,
2
)
),
VSTACK(HSTACK(
"Rank",
"From City",
"To City",
"Distance"
),
HSTACK((XMATCH(
TAKE(
C,
,
-1
),
N
)-6)/2,
C)))
Pre-programmed Lambdas:
L_Flattena2DTableintoColumns:
=LAMBDA(Table,
LET(ROWS,
ROWS(
DROP(
Table,
1,
1
)
),
COLUMNS,
COLUMNS(
DROP(
Table,
1,
1
)
),
HRows,
CHOOSEROWS(TAKE(
Table,
-ROWS,
1
),
(ROUNDDOWN(
SEQUENCE(
ROWS*COLUMNS,
,
0
)/COLUMNS,
0
)+1)),
HColumn,
CHOOSEROWS(
TOROW(
TAKE(
Table,
1,
-COLUMNS
)
),
L_RepeatingNumberSequence(
COLUMNS,
ROWS
)
),
Data,
TOCOL(
DROP(
Table,
1,
1
)
),
HSTACK(
HRows,
HColumn,
Data
)))
L_RepeatingNumberSequence:
=LAMBDA(
Numbers,
Repetitions,
IF(
MOD(
SEQUENCE(
Numbers*Repetitions
),
Numbers
)=0,
Numbers,
MOD(
SEQUENCE(
Repetitions*Numbers
),
Numbers
)
)
) Excel solution 8 for Top 3 Closest City Pairs, proposed by Julian Poeltl:
=LET(T,
A1:H8,
F,
CHOOSEROWS(
TAKE(
T,
-7,
1
),
MOD(
SEQUENCE(
7^2
)-1,
7
)+1
),
S,
TOCOL(
CHOOSECOLS(
TAKE(
T,
1,
-7
),
ROUNDDOWN(
SEQUENCE(
49,
,
0
)/7,
0
)+1
)
),
N,
TOCOL(
DROP(
T,
1,
1
),
,
TRUE
),
So,
SORT(
HSTACK(
F,
S,
N
),
3,
1
),
SU,
SORT(
UNIQUE(
N
)
),
TT,
CHOOSEROWS(
SU,
4
),
CT,
CHOOSECOLS(
So,
3
),
R,
FILTER(So,
(CT<=TT)*(CT>0)),
RE,
CHOOSEROWS(
R,
SEQUENCE(
COUNTA(
R
)/6,
,
,
2
)
),
VSTACK(
HSTACK(
"Rank",
"From City",
"To City",
"Distance"
),
HSTACK(
XMATCH(
TAKE(
RE,
,
-1
),
SU
)-1,
RE
)
))Excel solution 9 for Top 3 Closest City Pairs, proposed by Timothée BLIOT:
=LET(A,
B2:H8,
B,
TOCOL(
A
),
C,
HSTACK(
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
B
)
),
LAMBDA(
y,
x,
VSTACK(
y,
SORT(
INDEX(
HSTACK(
TOCOL(
IFNA(
A2:A8,
A
)
),
TOCOL(
IFNA(
B1:H1,
A
)
)
),
x
),
,
,
1
)
)
)
),
1
),
B
),
D,
MAP(B,
LAMBDA(x,
SUM(--(x>UNIQUE(
B
))))),
VSTACK({"Rank",
"From City",
"To City",
"Distance"},
UNIQUE(SORT(FILTER(HSTACK(
D,
C
),
(D>0)*(D<4))))))Excel solution 10 for Top 3 Closest City Pairs, proposed by Oscar Mendez Roca Farell:
=LET(c,
A2:A8,
b,
B1:H1,
s,
REPT(
" ",
50
),
t,
TRIM(MID(TOCOL(c&s&b&s&B2:H8/(cExcel solution 11 for Top 3 Closest City Pairs, proposed by Sunny Baggu:
=LET(
a,
SEQUENCE(
ROWS(
A2:A8
)
) < SEQUENCE(
,
COLUMNS(
B1:H1
)
),
b,
TOCOL(
IF(
a,
A2:A8,
x
),
3
),
c,
TOCOL(
IF(
a,
B1:H1,
x
),
3
),
d,
TOCOL(
IF(
a,
B2:H8,
x
),
3
),
tbl,
SORT(
HSTACK(
b,
c,
d
),
3
),
_v,
TAKE(
tbl,
,
-1
),
_t3,
SMALL(
_v,
3
),
_ftbl,
FILTER(
tbl,
_v <= _t3
),
HSTACK(
XMATCH(
TAKE(
_ftbl,
,
-1
),
TAKE(
tbl,
,
-1
)
),
_ftbl
)
)Excel solution 12 for Top 3 Closest City Pairs, proposed by LEONARD OCHEA 🇷🇴:
=LET(
v,
A2:A8,
h,
B1:H1,
m,
SORT(
TEXTSPLIT(
TEXTJOIN(
"/",
,
IF(
vExcel solution 13 for Top 3 Closest City Pairs, proposed by 🇵🇪 Ned Navarrete C.:
=LET(
d,
"*",
m,
SORT(
TEXTSPLIT(
TEXTAFTER(
TOCOL(
IF(
A2:A8Excel solution 14 for Top 3 Closest City Pairs, proposed by Andy Heybruch:
=LET(
_from,
A2:A8,
_to,
B1:H1,
_check,
TOCOL(
A2:A8Excel solution 15 for Top 3 Closest City Pairs, proposed by Sandeep Marwal:
=LET(
from,
A2:A8,
to,
B1:H1,
routelist,
TOCOL(
from&"-"&to
),
distance,
MAP(
routelist,
LAMBDA(
a,
XLOOKUP(
TEXTBEFORE(
a,
"-"
),
to,
XLOOKUP(
TEXTAFTER(
a,
"-"
),
from,
$B$2:$H$8
)
)
)
),
routelistwithdistance,
HSTACK(
routelist,
distance
),
minimum,
SMALL(
UNIQUE(
FILTER(
distance,
distance<>0
)
),
SEQUENCE(
3
)
),
ftr,
FILTER(
routelistwithdistance,
ISNUMBER(
MATCH(
CHOOSECOLS(
routelistwithdistance,
2
),
minimum,
0
)
)
),
SORT(
UNIQUE(
HSTACK(
MAP(
CHOOSECOLS(
ftr,
1
),
LAMBDA(
a,
TEXTJOIN(
"-",
,
TRANSPOSE(
SORT(
TRANSPOSE(
TEXTSPLIT(
a,
"-"
)
),
,
,
FALSE
)
)
)
)
),
CHOOSECOLS(
ftr,
2
)
)
),
2,
1
)
)Excel solution 16 for Top 3 Closest City Pairs, proposed by Josh Brodrick:
=VSTACK({"Rank","From City","To City","Distance"},HSTACK(TOCOL({1,2,3,4}),CHOOSEROWS(DROP(SORT(HSTACK(TOCOL(IFNA(EXPAND(A2:A8,,7),A2:A8)),TOCOL(IFNA(EXPAND(A2:A8,,7),A2:A8),,TRUE),TOCOL(B2:H8,,TRUE)),3),7),{1,3,5,7})))Excel solution 17 for Top 3 Closest City Pairs, proposed by Tyler Cameron:
=LET(
a,
B2:H8,
b,
INDEX(
SORT(
DROP(
UNIQUE(
TOCOL(
a
)
),
1
)
),
SEQUENCE(
3
)
),
d,
IFS(
a=INDEX(
b,
1
),
a,
a=INDEX(
b,
2
),
a,
a=INDEX(
b,
3
),
a
),
t,
HSTACK(
TOCOL(
MAKEARRAY(
7,
7,
LAMBDA(
r,
c,
INDEX(
A2:A8,
r
)
)
)
),
INDEX(
A2:A8,
RIGHT(
BASE(
SEQUENCE(
49
)-1,
7,
4
)+1,
1
)
),
TOCOL(
MAKEARRAY(
7,
7,
LAMBDA(
r,
c,
IFNA(
IF(
r0
),
3
)
)
)
) Solving the challenge of Top 3 Closest City Pairs with Python
Python solution 1 for Top 3 Closest City Pairs, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
input = pd.read_excel("446 Top 3 Min Distance.xlsx", usecols = "A:H", nrows=7)
test = pd.read_excel("446 Top 3 Min Distance.xlsx", usecols="J:M", nrows=4, skiprows=1)
result = input.melt(id_vars="Cities", var_name="City 2", value_name="Distance")
result = result[result["Distance"] != 0]
result["Cities"] = result["Cities"] + " - " + result["City 2"]
result.drop(columns=["City 2"], inplace=True)
result["Cities"] = result["Cities"].str.split(" - ")
result["Cities"] = result["Cities"].apply(sorted)
result["Cities"] = result["Cities"].apply(lambda x: " - ".join(x))
result = result.drop_duplicates(subset="Cities")
result["rank"] = result["Distance"].rank(method="dense").astype("int64")
result = result[result["rank"] <= 3]
result = result.sort_values(["rank", "Cities"])
result["Cities"] = result["Cities"].str.split(" - ")
result["From City"] = result["Cities"].apply(lambda x: x[0])
result["To City"] = result["Cities"].apply(lambda x: x[1])
result = result[["rank", "From City", "To City", "Distance"]].reset_index(drop=True)
result.columns = test.columns
print(result.equals(test)) # True
Python solution 2 for Top 3 Closest City Pairs, proposed by Luan Rodrigues:
Solução Python
import pandas as pd
df = pd.read_excel('Excel_Challenge_446 - Top 3 Min Distance/Excel_Challenge_446 - Top 3 Min Distance.xlsx',usecols='A:H',nrows=9)
df_unpivot = df.melt(id_vars=['Cities'],var_name="From City",value_name="Distance")
df_unpivot['sort'] = df_unpivot.apply(lambda row: sorted([row["Cities"], row["From City"]]), axis=1)
df_unpivot = df_unpivot[df_unpivot['Distance']!=0]
min_values = df_unpivot["Distance"].sort_values().unique()[:3]
df_unpivot = df_unpivot[df_unpivot["Distance"].isin(min_values)]
df_unpivot = df_unpivot.drop_duplicates(subset=['sort']).sort_values(by=['Distance'])
df_unpivot['Rank'] = df_unpivot['Distance'].rank(method='min')
df_unpivot = df_unpivot[['Rank','From City','Cities','Distance']]
print(df_unpivot)
Solving the challenge of Top 3 Closest City Pairs with R
R solution 1 for Top 3 Closest City Pairs, proposed by Konrad Gryczan, PhD:
R Solutution - Done
library(tidyverse)
library(readxl)
input = read_excel("Excel/446 Top 3 Min Distance.xlsx", range = "A1:H8")
test = read_excel("Excel/446 Top 3 Min Distance.xlsx", range = "J2:M6")
result = input %>%
pivot_longer(-Cities, names_to = "City 2", values_to = "Distance") %>%
filter(Distance != 0) %>%
unite("Cities", Cities, `City 2`, sep = " - ") %>%
mutate(Cities = str_split(Cities, " - ")) %>%
mutate(Cities = map(Cities, sort)) %>%
distinct() %>%
mutate(rank = dense_rank(Distance) %>% as.numeric()) %>%
filter(rank <= 3) %>%
arrange(rank) %>%
mutate(`From City` = map_chr(Cities, ~ .x[1]),
`To City` = map_chr(Cities, ~ .x[2])) %>%
select(Rank = rank, `From City`, `To City`, Distance)
&