The distance between cities and staff travels are provided in the question distance and travel tables. Calculate the total distance for each travel. For example, in the highlighted cell, the travel starts from city D, goes to city C, and finally returns to city D. Therefore, the total distance is 31 + 31.
📌 Challenge Details and Links
Challenge Number: 81
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Total Distance! with Power Query
Power Query solution 1 for Total Distance!, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
S = Table.AddColumn(
Source("Travel"),
"Distance",
each
let
l = Text.Split([Path], ",")
in
List.Sum(
List.Transform(
{0 .. List.Count(l) - 2},
(m) =>
Table.Column(Table.SelectRows(Source("Distance"), each [#"From-To"] = l{m}), l{m + 1}){
0
}
)
)
)
in
SPower Query solution 2 for Total Distance!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Tbl1, {"From-To"}, "At", "Va"),
Lista = Table.ToRows(Unpivot),
Tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Sol = Table.AddColumn(Tbl2, "Distance", each
let
a = Text.Split([Path], ","),
b = List.Transform({0..List.Count(a)-2}, each {a{_}, a{_+1}}),
c = List.Transform(b, each List.Select(Lista, (x)=> List.FirstN(x,2)=_){0}),
d = List.Sum(List.Transform(c, each _{2}))
in d)
in
SolPower Query solution 3 for Total Distance!, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
Travel = Table.AddIndexColumn(Source, "Travel", 0, 1, Int64.Type),
Split = Table.ExpandListColumn(
Table.TransformColumns(
Table.DuplicateColumn(Travel, "Path", "From"),
{{"From", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)}}
),
"From"
),
Index = Table.AddIndexColumn(Split, "Index", 0, 1, Int64.Type),
To = Table.AddColumn(
Index,
"To",
each try
if [Travel] = Index{[Index] - 1}[Travel] then Index{[Index] - 1}[From] else null
otherwise
null
),
Merge = Table.NestedJoin(
To,
{"From", "To"},
Table.UnpivotOtherColumns(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{"From-To"},
"To",
"Value"
),
{"From-To", "To"},
"Distance",
JoinKind.LeftOuter
),
Expand = Table.ExpandTableColumn(Merge, "Distance", {"Value"}, {"Value"}),
Group = Table.Group(
Expand,
{"Date", "Staff ID", "Path"},
{{"Count", each List.Sum([Value]), type nullable number}}
)
in
GroupPower Query solution 4 for Total Distance!, proposed by Masoud Karami:
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
D1 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
D2 = Table.UnpivotOtherColumns(D1, {"From-To"} , "A_", "V_"),
T1 = Table.SplitColumn( Source , "Path", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Path.1", "Path.2", "Path.3", "Path.4", "Path.5", "Path.6", "Path.7"}),
T2 = Table.UnpivotOtherColumns(T1, { "Date", "Staff ID"}, "A_", "V_"),
T3 = Table.Group(T2, {"Date", "Staff ID"}, {{"All", each
let
inx = Table.AddIndexColumn( _ , "x", 1, 1, Int64.Type) ,
Lv = Table.AddColumn( inx , "xx" , each try inx[V_]{[x]} otherwise null )
in
Lv
}}),
T4 = Table.Combine( T3[All] ),
T5 = Table.NestedJoin(T4, {"V_", "xx"}, D2 , {"From-To", "A_"}, "Faseleh", JoinKind.LeftOuter),
T6 = Table.ExpandTableColumn(T5, "Faseleh", {"V_"}, {"V_.1"}),
T8 = Table.Group(T6, {"Date", "Staff ID"}, {{"Distance", each List.Sum([V_.1]), type nullable number}}),
T9 = Table.NestedJoin(T8, {"Date", "Staff ID"}, Source , {"Date", "Staff ID"}, "Faseleh", JoinKind.LeftOuter),
T10 = Table.ExpandTableColumn(T9, "Faseleh", {"Path"}, {"Path"}),
T11 = Table.ReorderColumns(T10,{"Date", "Staff ID", "Path", "Distance"})
in
T11Power Query solution 5 for Total Distance!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S1 = Excel.CurrentWorkbook(){[Name = "Distance"]}[Content],
A = Table.UnpivotOtherColumns(S1, {"From-To"}, "Attribute", "Value"),
B = Table.CombineColumns(
A,
{"From-To", "Attribute"},
Combiner.CombineTextByDelimiter(",", QuoteStyle.None),
"FT"
),
S = Excel.CurrentWorkbook(){[Name = "Path"]}[Content],
C = Table.AddColumn(S, "S", each Text.Split([Path], ",")),
D = Table.ExpandListColumn(C, "S"),
E = Table.Group(
D,
{"Date", "Staff ID"},
{{"Tb", each _, type table [Date = datetime, Staff ID = text, Path = text, S = text]}}
),
F = (T) =>
let
W = Table.AddIndexColumn(T, "I", 1, 1, Int64.Type),
F2 = Table.AddColumn(W, "FT", each [S] & "," & W[S]{[I]}),
F3 = Table.RemoveRowsWithErrors(F2, {"FT"}),
F4 = Table.SelectColumns(F3, {"FT"})
in
F4,
X = Table.AddColumn(E, "F", each F([Tb])),
G = Table.SelectColumns(X, {"Date", "Staff ID", "F"}),
I = Table.ExpandTableColumn(G, "F", {"FT"}, {"FT"}),
J = Table.NestedJoin(I, {"FT"}, B, {"FT"}, "N"),
K = Table.ExpandTableColumn(J, "N", {"Value"}, {"Value"}),
L = Table.Group(K, {"Date", "Staff ID"}, {{"Distance", each List.Sum([Value]), type number}}),
M = Table.Sort(L, {{"Date", Order.Ascending}, {"Staff ID", Order.Ascending}}),
N = Table.TransformColumnTypes(M, {{"Date", type date}})
in
NSolving the challenge of Total Distance! with Excel
Excel solution 1 for Total Distance!, proposed by Bo Rydobon 🇹🇭:
=MAP(
J3:J14,
LAMBDA(
a,
LET(
n,
XMATCH(
TEXTSPLIT(
a,
,
","
),
B3:B6
),
SUM(
INDEX(
C3:F6,
DROP(
n,
-1
),
DROP(
n,
1
)
)
)
)
)
)Excel solution 2 for Total Distance!, proposed by محمد حلمي:
=MAP(J3:J14,
LAMBDA(a,
LET(i,
TEXTSPLIT(
a,
","
),
SUM(REDUCE(0,
i&DROP(
i,
,
1
),
LAMBDA(a,
v,
a+IFNA((v=B3:B6&C2:F2)*C3:F6,
)))))))Excel solution 3 for Total Distance!, proposed by محمد حلمي:
=LET(
v,
TEXTSPLIT(
CONCAT(
J3:J14&9
),
",",
9,
1
),
MMULT(
IFNA(
XLOOKUP(
v&DROP(
v,
,
1
),
TOCOL(
B3:B6&C2:F2
),
TOCOL(
C3:F6
)
),
),
SEQUENCE(
7
)^0
)
)
7 = COLUMNS(
v
)Excel solution 4 for Total Distance!, proposed by محمد حلمي:
=MAP(
J3:J14,
LAMBDA(
a,
LET(
i,
TEXTSPLIT(
a,
","
),
SUM(
IFNA(
XLOOKUP(
i&DROP(
i,
,
1
),
TOCOL(
B3:B6&C2:F2
),
TOCOL(
C3:F6
)
),
)
)
)
)
)Excel solution 5 for Total Distance!, proposed by محمد حلمي:
=MAP(J3:J14,
LAMBDA(e,
LET(i,
TEXTSPLIT(
e,
,
","
),
SUM(IFNA(MAP(i&DROP(
i,
1
),
LAMBDA(A,
SUM((A=B3:B6&C2:F2)*C3:F6))),
)))))Excel solution 6 for Total Distance!, proposed by Oscar Mendez Roca Farell:
=MAP(
J3:J14,
LAMBDA(
a,
LET(
t,
TEXTSPLIT(
a,
,
","
),
SUM(
XLOOKUP(
IFNA(
t&DROP(
t,
1
),
),
TOCOL(
B3:B6&C2:F2
),
TOCOL(
C3:F6
),
0
)
)
)
)
)Excel solution 7 for Total Distance!, proposed by Julian Poeltl:
=MAP(
J3:J14,
LAMBDA(
P,
LET(
D,
B2:F6,
DD,
DROP(
D,
1,
1
),
SP,
TEXTSPLIT(
P,
","
),
W,
RIGHT(
DROP(
SCAN(
,
SP,
LAMBDA(
A,
B,
A&B
)
),
,
1
),
2
),
C,
DROP(
TAKE(
D,
1
),
,
1
),
SUM(
XLOOKUP(
W,
TOCOL(
C&TRANSPOSE(
C
)
),
TOCOL(
DD
)
)
)
)
)
)Excel solution 8 for Total Distance!, proposed by Kris Jaganah:
=MAP(
J3:J14,
LAMBDA(
z,
LET(
a,
TEXTSPLIT(
z,
,
","
),
SUM(
MAP(
DROP(
a,
-1
),
DROP(
a,
1
),
LAMBDA(
x,
y,
XLOOKUP(
x,
B2:B6,
XLOOKUP(
y,
B2:F2,
B2:F6
)
)
)
)
)
)
)
)Excel solution 9 for Total Distance!, proposed by Imam Hambali:
=LET(
a,
J3:J14,seq,
SEQUENCE(
,
MAX(
LEN(
a
)
)-1
),BYROW(XLOOKUP(LAMBDA(
x,
y,
MID(
x,
y,
2
)
)(SUBSTITUTE(
a,
",",
""
),
seq),
TOCOL(
B3:B6&C2:F2
),
TOCOL(
C3:F6
),
0),
SUM)
)Excel solution 10 for Total Distance!, proposed by Sunny Baggu:
=MAP( J3:J14, LAMBDA(
t, LET(
a,
TEXTSPLIT(
t,
,
","
),
SUM(
XLOOKUP(
DROP(
a,
-1
) & DROP(
a,
1
),
TOCOL(
B3:B6 & C2:F2
),
TOCOL(
C3:F6
)
)
)
) ))Excel solution 11 for Total Distance!, proposed by Bilal Mahmoud kh.:
=MAP(
J3:J14,
LAMBDA(
n,
LET(
a,
TEXTSPLIT(
n,
,
","
),
b,
IFERROR(
MAP(
SEQUENCE(
COUNTA(
FILTER(
a,
NOT(
ISNUMBER(
a
)
)
)
),
,
1
),
a,
LAMBDA(
x,
y,
IFNA(
VLOOKUP(
INDEX(
a,
x,
1
),
A2:E6,
MATCH(
INDEX(
a,
x+1,
1
),
A2:E2,
0
),
0
),
0
)
)
),
0
),
SUM(
b
)
)
)
)Excel solution 12 for Total Distance!, proposed by CA Raghunath Gundi:
=BYROW(
$J$3:$J$14, LAMBDA(
path, SUM(
IFERROR(
INDEX(
$B$2:$F$6,
MATCH(
TEXTSPLIT(
path,
","
),
$B$2:$B$6,
0
),
MATCH(
DROP(
TEXTSPLIT(
path,
","
),
,
1
),
$B$2:$F$2,
0
)
),
0
)
)
)
)Excel solution 13 for Total Distance!, proposed by El Badlis Mohd Marzudin:
=MAP(
SUBSTITUTE(
J3:J14,
",",
""
),
LAMBDA(
w,
SUM(
XLOOKUP(
IF(
SEQUENCE(
LEN(
w
)
)<=LEN(
w
)-1,
MID(
w,
SEQUENCE(
LEN(
w
)
),
2
),
""
),
TOCOL(
B3:B6&C2:F2
),
TOCOL(
C3:F6
),
0
)
)
)
)Excel solution 14 for Total Distance!, proposed by Hamidi Hamid:
=LET(x;
DROP(
REDUCE(
;
J2:J14;
LAMBDA(
a;
b;
IFERROR(
VSTACK(
a;
TEXTSPLIT(
b;
",";
)
);
""
)
)
);
1
);
y;
HSTACK(
DROP(
x;
;
1
);
SEQUENCE(
COUNTA(
J3:J14
)
)*0
);
BYROW(MAP(x;
y;
LAMBDA(a;
b;
SUMPRODUCT((B3:B6=a)*(C2:F2=b);
C3:F6)));
LAMBDA(
a;
SUM(
a
)
)))Excel solution 15 for Total Distance!, proposed by Hussein SATOUR:
=MAP(
J3:J14,
LAMBDA(
x,
SUM(
XLOOKUP(
MID(
x,
SEQUENCE(
LEN(
x
)/2,
,
,
2
),
3
),
TOCOL(
B3:B6&","&C2:F2
),
TOCOL(
C3:F6
)
)
)
)
)Excel solution 16 for Total Distance!, proposed by Mey Tithveasna:
=MAP(J3:J14,
LAMBDA(f,
LET(path,
TEXTSPLIT(
f,
,
","
),
distances,
SUM((INDEX(
C3:F6,
MATCH(
DROP(
path,
-1
),
C2:F2,
0
),
MATCH(
DROP(
path,
1
),
B3:B6,
0
)
))),
distances)))Excel solution 17 for Total Distance!, proposed by Pieter de Bruijn:
=MAP(
J3:J14,
LAMBDA(
m,
LET(
x,
TEXTSPLIT(
m,
,
","
),
a,
DROP(
x,
-1
),
b,
DROP(
x,
1
),
SUM(
INDEX(
C3:F6,
XMATCH(
a,
B3:B6
),
XMATCH(
b,
C2:F2
)
)
)
)
)
)Excel solution 18 for Total Distance!, proposed by Rick Rothstein:
=MAP(
J3:J14,
LAMBDA(
x,
LET(
t,
TEXTSPLIT(
x,
","
),
s,
SUM(
INDEX(
C3:F6,
MATCH(
DROP(
t,
,
1
),
B3:B6,
),
MATCH(
DROP(
t,
,
-1
),
C2:F2,
)
)
),
s
)
)
)Solving the challenge of Total Distance! with Python
Python solution 1 for Total Distance!, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-081 Compaint grouping.xlsx"
input1 = pd.read_excel(path, skiprows=1, usecols="B:F", nrows= 4)
input2 = pd.read_excel(path, skiprows=1, usecols="H:J")
test = pd.read_excel(path, skiprows=1, usecols="K:K")
result1 = input1.melt(id_vars=["From-To"], var_name="To", value_name="Distance")
result2 = input2.assign(Path=input2["Path"].str.split(",")).explode("Path")
result2["to"] = result2.groupby(["Date","Staff ID"])["Path"].shift(-1)
result2 = result2.dropna()
result = result2.merge(result1, left_on=["Path", "to"], right_on=["From-To", "To"])
result = result.groupby(["Date", "Staff ID"]).agg({"Distance": "sum"}).reset_index()
print(result["Distance"].equals(test["Distance"])) # True
Python solution 2 for Total Distance!, proposed by Aman Mashetty:
xlsx"
# Read data from Excel file
distance_matrix = pd.read_excel(path, skiprows=1, usecols="B:F", nrows=5)
travel_data = pd.read_excel(path, skiprows=1, usecols="H:J")
test = pd.read_excel(path, skiprows=1, usecols="K:K")
distance_matrix.set_index('From-To', inplace=True)
travel_data = travel_data.dropna(subset=['Path'])
travel_data['Path'] = travel_data['Path'].astype(str)
# Calculate the total distance for each travel path
result_data = []
# Iterate over the rows of travel_data
for row in travel_data.itertuples(index=False, name=None):
date, staff_id, path = row
cities = path.split(',')
total_distance = 0
for i in range(len(cities) - 1):
from_city = cities[i]
to_city = cities[i + 1]
total_distance += distance_matrix.at[from_city, to_city]
result_data.append([date, staff_id, path, total_distance])
result_df = pd.DataFrame(result_data, columns=['Date', 'Staff ID', 'Path', 'Distance'])
# Print the result
print(result_df)
Solving the challenge of Total Distance! with R
R solution 1 for Total Distance!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-081 Compaint grouping.xlsx"
input1 = read_xlsx(path, range = "B2:F6")
input2 = read_xlsx(path, range = "H2:J14")
test = read_xlsx(path, range = "K2:K14")
result1 = input1 %>%
pivot_longer(cols = -c(`From-To`), names_to = "To", values_to = "Distance")
result2 = input2 %>%
separate_rows(Path, sep = ",") %>%
mutate(to = lead(Path), .by = c(Date, `Staff ID`)) %>%
na.omit()
result = result2 %>%
left_join(result1, by = c("Path" = "From-To", "to" = "To")) %>%
summarise(Distance = sum(Distance, na.rm = TRUE), .by = c(Date, `Staff ID`))
identical(result$Distance, test$Distance)
#> [1] TRUE