In the question table, direct connections between people are provided. Our task is to map these connections into a symmetric table, as shown in the result table. If two people are directly connected, enter a “1” in the corresponding cell. If there is no direct connection, list the people who form a bridge between the two individuals. For example, in the red cell, there is no direct connection between A and G. However, A is connected to C, C is connected to F, and F is connected to G. Therefore, the cell will display “C-F,” indicating the bridge connecting A and G.
📌 Challenge Details and Links
Challenge Number: 200
Challenge Difficulty: ⭐⭐⭐⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of People Connection! with Power Query
Power Query solution 1 for People Connection!, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.Transform(Table.ToRows(A), each _{0} & _{1}),
C = List.Distinct(List.TransformMany(B, each Text.ToList(_), (u, v) => v)),
F = (s) =>
[
D = List.Accumulate(
{1 .. 5},
{s},
(t, u) =>
List.Distinct(
List.TransformMany(
t,
each List.Select(
B,
(v) =>
Text.End(v, 1)
= Text.Start(Text.End(_, 1), 1) or Text.Start(v, 1)
= Text.End(_, 1)
),
(x, y) => x & Text.Remove(x & y, Text.ToList(x))
)
)
),
E = List.Transform(
C,
each
let
a = List.Select(List.Sort(D, each Text.Length(_)), (u) => Text.End(u, 1) = _){0}? ?? "-",
b = Text.Length(a),
c =
if b = 1 then
a
else if b = 2 then
"1"
else
Text.Combine(List.RemoveLastN(List.Skip(Text.ToList(a))), "-")
in
c
)
][E],
G = Table.FromRows(List.Transform(C, each {_} & F(_)), {""} & C)
in
GPower Query solution 2 for People Connection!, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.TransformMany(Table.ToRows(A), each {_{0} & _{1}} & {_{1} & _{0}}, (_, w) => w),
C = List.Distinct(List.TransformMany(B, each Text.ToList(_), (_, u) => u)),
D = List.Transform(
C,
(m) =>
[
a = List.Sort(
List.Accumulate(
{0 .. 5},
{m},
(x, y) =>
List.Distinct(
List.TransformMany(
x,
each List.Select(B, (v) => Text.End(v, 1) = Text.End(_, 1)),
(_, z) => _ & Text.Remove(z, Text.ToList(_))
)
)
),
(w) => Text.Length(w)
),
b = {m}
& List.Transform(
C,
(s) =>
[
p = Text.Remove(List.Select(a, each Text.End(_, 1) = s){0}? ?? "-", {s, m}),
q = (if p = "" then 1 else Text.Combine(Text.ToList(p), "-"))
][q]
)
][b]
),
E = Table.FromRows(D, {" "} & C)
in
ESolving the challenge of People Connection! with Excel
Excel solution 1 for People Connection!, proposed by Bo Rydobon 🇹🇭:
=LET(
p,
B3:B11,
q,
C3:C11,
pq,
VSTACK(
p,
q
),
qp,
VSTACK(
q,
p
),
u,
SORT(
UNIQUE(
pq
)
),
v,
TOROW(
u
), c,
REDUCE(
v,
v,
LAMBDA(
a,
i,
UNIQUE(
TOROW(
a&IF(
REGEXTEST(
a,
pq&"$"
)*ISERR(
FIND(
qp,
a
)
),
"-"&qp,
""
),
3
),
1
)
)
), MAP(
IFNA(
u,
v
),
IFNA(
v,
u
),
LAMBDA(
i,
j,
IF(
i=j,
"-",
LET(
x,
REGEXEXTRACT(
c,
"^"&i&"-K.*?(?=-?"&j&")"
),
y,
@SORTBY(
x,
LEN(
x
)
),
IF(
y="",
1,
y
)
)
)
)
)
)Excel solution 2 for People Connection!, proposed by Bo Rydobon 🇹🇭:
=LET(p,
B3:B11,
q,
C3:C11,
pq,
VSTACK(
p,
q
),
qp,
VSTACK(
q,
p
),
u,
SORT(
UNIQUE(
pq
)
),
v,
TOROW(
u
),b,
LAMBDA(b,
a,
LET(n,
ROWS(
a
),
IF(n=1,
a&IFERROR("-"&FILTER(pq,
(TEXTAFTER(
"-"&a,
"-",
-1
)=qp)*ISERR(
FIND(
pq,
a
)
)),
""),
VSTACK(
b(
b,
TAKE(
a,
n/2
)
),
b(
b,
DROP(
a,
n/2
)
)
)))),c,
REDUCE(
u,
u,
LAMBDA(
a,
_,
b(
b,
a
)
)
),
MAP(
IFNA(
u,
v
),
IFNA(
v,
u
),
LAMBDA(
i,
j,
LET(
x,
MID(
TEXTBEFORE(
TEXTAFTER(
c,
i
),
"-"&j
),
2,
99
),
y,
@SORTBY(
x,
LEN(
x
)
),
IFNA(
IF(
y="",
1,
y
),
"-"
)
)
)
))Excel solution 3 for People Connection!, proposed by Kris Jaganah:
=LET(a,
B3:B11,
b,
C3:C11,
c,
UNIQUE(
TOCOL(
B3:C11
)
),
d,
{0;1;2;3;4;5},
t,
TOROW(
c
),
HSTACK(VSTACK(
"",
c
),
REDUCE(t,
c,
LAMBDA(k,
l,
VSTACK(k,
LET(e,
UNIQUE(
TOCOL(
REDUCE(
l,
d,
LAMBDA(
x,
y,
HSTACK(
x,
TOROW(
IFS(
a=RIGHT(
x
),
x&b,
b=RIGHT(
x
),
x&a
),
3
)
)
)
)
)
),
f,
MAP(
e,
LAMBDA(
v,
CONCAT(
UNIQUE(
REGEXEXTRACT(
v,
"[A-Z+]",
1
),
1
)
)
)
),
g,
LEN(
f
),
h,
REDUCE(zz,
d,
LAMBDA(v,
w,
IFERROR(v,
MAP(t,
LAMBDA(v,
TAKE(FILTER(f,
(v=RIGHT(
f
))*(g=w+1)),
1)))))),
i,
LEN(
h
),
j,
IFS(
i=1,
"-",
i=2,
1,
1,
MID(
h,
2,
i-2
)
),
MAP(
j,
LAMBDA(
s,
TEXTJOIN(
"-",
,
REGEXEXTRACT(
s,
"[A-Z-+1]",
1
)
)
)
)))))))Excel solution 4 for People Connection!, proposed by Diarmuid Early:
=LET(from,
B3:B11,
to,
C3:C11,
people,
SORT(
UNIQUE(
VSTACK(
from,
to
)
)
), num,
ROWS(
people
), oneway,
COUNTIFS(
from,
people,
to,
TOROW(
people
)
), matrix,
oneway+TRANSPOSE(
oneway
), pathOneStep,
IF(
matrix,
people&"-"&TOROW(
people
),
0
),
fullPath,
REDUCE(pathOneStep,
SEQUENCE(
num
),
LAMBDA(a,
v,
LET(
numMat,
--(a<>0),
nxtCon,
(--(MMULT(
matrix,
numMat
)>0)>numMat)-MUNIT(
num
), nxtPath,
MAKEARRAY(num,
num,
LAMBDA(r,
c,
IF(NOT(
INDEX(
nxtCon,
r,
c
)
), INDEX(
a,
r,
c
), LET(rw,
TOCOL(
INDEX(
a,
r,
0
)
), cl,
INDEX(
a,
0,
c
), hit,
XMATCH(1,
IF((rw<>0)*(cl<>0),
LEN(
rw
)+LEN(
cl
),
""),
1), INDEX(
rw,
hit
)&MID(
INDEX(
cl,
hit
),
2,
99
))))), nxtPath))),
out,
IF(
fullPath=0,
"-",
IF(
LEN(
fullPath
)=3,
1,
SUBSTITUTE(
SUBSTITUTE(
fullPath,
people&"-",
""
),
"-"&TOROW(
people
),
""
)
)
), out)Excel solution 5 for People Connection!, proposed by Jazen Cosby:
=0,
"-",
IF(
LEN(
fullPath
)=3,
1,
SUBSTITUTE(
SUBSTITUTE(
fullPath,
p&"-",
""
),
"-"&TOROW(
p
),
""
)
)),out)Excel solution 6 for People Connection!, proposed by Pieter de B.:
=LET(
a,
B3:C11,
b,
VSTACK(
a,
SORTBY(
a,
{2,
1}
)
),
c,
TAKE,
u,
UNIQUE(
TOCOL(
a
)
),
r,
ROWS(
u
),
MAKEARRAY(
r,
r,
LAMBDA(
m,
n,
LET(
p,
INDEX(
u,
n,
1
),
q,
,
h,
TEXTAFTER(
REDUCE(
INDEX(
u,
m,
1
),
ROW(
a
),
LAMBDA(
x,
_,
IFNA(
XLOOKUP(
"*-"&p,
x,
x,
,
2
),
REDUCE(
"",
x,
LAMBDA(
v,
w,
VSTACK(
v,
w&FILTER(
"-"&c(
b,
,
-1
),
c(
b,
,
1
)=TEXTAFTER(
"-"&w,
"-",
-1
),
""
)
)
)
)
)
)
),
"-"
),
IFS(
m=n,
"-",
h=p,
1,
1,
TEXTBEFORE(
h,
"-",
-1
)
)
)
)
)
)Excel solution 7 for People Connection!, proposed by Talia Cao, CPA:
=LET(
s,
B3:B11,
e,
C3:C11, t,
HSTACK(
VSTACK(
s,
e
),
VSTACK(
e,
s
)
), l,
SORT(
UNIQUE(
TAKE(
t,
,
1
)
)
), i,
ROWS(
l
),
fxMinPath,
LAMBDA(
StartNode,
EndNode, LET(
f,
LAMBDA(
ME,
Path,
LET(
NextNodes,
FILTER(
TAKE(
t,
,
-1
),
TAKE(
t,
,
1
)=TEXTAFTER(
Path,
"-",
-1,
,
1
),
""
),
UNIQUE(
DROP(
REDUCE(
"",
NextNodes,
LAMBDA(
a,
n,
VSTACK(
a,
IF(
ISNUMBER(
FIND(
n,
Path
)
),
Path,
IF(
n=EndNode,
Path&"-"&n,
ME(
ME,
Path&"-"&n
)
)
)
)
)
),
1
)
)
)
),
FromStart,
f(
f,
StartNode
),
ToEnd,
FILTER(
FromStart,
TEXTAFTER(
FromStart,
"-",
-1
)=EndNode,
""
),
TEXTBEFORE(
TEXTAFTER(
@SORTBY(
ToEnd,
LEN(
ToEnd
)-LEN(
SUBSTITUTE(
ToEnd,
"-",
""
)
)
),
"-"
),
"-",
-1,
,
,
1
)
)
),
IF(
MUNIT(
i
),
"-",
MAKEARRAY(
i,
i,
LAMBDA(
r,
c,
IFNA(
fxMinPath(
INDEX(
l,
r,
1
),
INDEX(
l,
c,
1
)
),
""
)
)
)
)
)Solving the challenge of People Connection! with Python
Python solution 1 for People Connection!, proposed by Konrad Gryczan, PhD:
import pandas as pd
import networkx as nx
path = "CH-200People Connection.xlsx"
input = pd.read_excel(path, usecols="B:C", skiprows=1, nrows=9)
test = pd.read_excel(path, usecols="E:M", skiprows=1, nrows=9)
G = nx.from_pandas_edgelist(input, source='Person 1', target='Person 2', create_using=nx.Graph())
all_people = sorted(set(input['Person 1']).union(set(input['Person 2'])))
all_com = pd.DataFrame([(a1, a2) for a1 in all_people for a2 in all_people], columns=['a1', 'a2'])
def shortest_path(a1, a2, G):
if a1 == a2:
return "-"
try:
path = nx.shortest_path(G, source=a1, target=a2)
if len(path) <= 2:
return "1"
nodes_between = path[1:-1]
return "-".join(nodes_between)
except nx.NetworkXNoPath:
return "1"
all_com['path'] = all_com.apply(lambda row: shortest_path(row['a1'], row['a2'], G), axis=1)
result = all_com.pivot(index='a2', columns='a1', values='path')
print(result)
print(test)
# Discrepancies because of incosistent node sorting, but the paths are correctSolving the challenge of People Connection! with Python in Excel
Python in Excel solution 1 for People Connection!, proposed by Alejandro Campos:
df = xl("B2:C11", headers=True)
persons = sorted(set(df["Person 1"]) | set(df["Person 2"]))
symmetric_table = pd.DataFrame("-", index=persons, columns=persons)
graph = {p: [] for p in persons}
for p1, p2 in zip(df["Person 1"], df["Person 2"]):
symmetric_table.loc[p1, p2] = symmetric_table.loc[p2, p1] = "1"
graph[p1].append(p2)
graph[p2].append(p1)
def find_paths(g, s, e, p=[]):
p = p + [s]
return [p] if s == e else sum((find_paths(g, n, e, p) for n in g[s] if n not in p), [])
for i, p1 in enumerate(persons):
for p2 in persons[i+1:]:
if symmetric_table.loc[p1, p2] != "1":
paths = find_paths(graph, p1, p2)
if paths:
bridges = min(paths, key=len)[1:-1]
if bridges:
symmetric_table.loc[p1, p2] = symmetric_table.loc[p2, p1] = "-".join(bridges)
symmetric_tableSolving the challenge of People Connection! with R
R solution 1 for People Connection!, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(igraph)
path = "files/CH-200People Connection.xlsx"
input = read_excel(path, range = "B2:C11")
test = read_excel(path, range = "E2:M10")
g = graph_from_data_frame(input, directed = FALSE)
all_people = sort(unique(c(input$`Person 1`, input$`Person 2`)))
all_com = expand.grid(a1 = all_people, a2 = all_people, stringsAsFactors = F)
shortest_path = function(a1, a2, g){
if (a1 == a2) {
return("-")
}
path = all_shortest_paths(g, from = a1, to = a2)
if (length(path$res) == 0 || length(path$res[[1]]$name) <= 2) {
return("1")
}
nodes_between = path$res[[1]]$name[-c(1, length(path$res[[1]]$name))]
return(paste(nodes_between, collapse = "-"))
}
result = all_com %>%
rowwise() %>%
mutate(path = shortest_path(a1, a2, g)) %>%
pivot_wider(names_from = a1, values_from = path)
print(result)
print(test)