Put the applicants on either shortlist, longlist or disqualify them Use the required technologies for long or shortlist Dynamic array function allowed, but Extra marks for Legacy solutions or PowerQuery Solution
📌 Challenge Details and Links
Challenge Number: 24
Challenge Difficulty: ⭐
📥Link to the solutions on LinkedIn
Solving the challenge of Looking up Common Data with Power Query
Power Query solution 1 for Lookingup Common Data, proposed by Omid Motamedisedeh:
let
SL = Text.Split(
Excel.CurrentWorkbook(){[Name = "Table2"]}[Content][SHORTLIST REQUIREMENT]{0},
", "
),
LL = Text.Split(
Excel.CurrentWorkbook(){[Name = "Table3"]}[Content][LONGLIST REQUIREMENT]{0},
", "
),
Main = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Main,
"Result",
each [
a = Text.Split([QUALIFIED TECHNOLOGIES], " ; "),
b =
if List.ContainsAll(a, SL) then
"Shortlist"
else if List.ContainsAll(a, LL) then
"Long List"
else
"Disqualify"
][b]
)
in
ResultPower Query solution 2 for Lookingup Common Data, proposed by Aditya Kumar Darak 🇮🇳:
let
Data = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
SLR = Text.Split(Excel.CurrentWorkbook(){[Name = "SLR"]}[Content][SHORTLIST REQUIREMENT]{0}, ", "),
LLR = Text.Split(Excel.CurrentWorkbook(){[Name = "LLR"]}[Content][LONGLIST REQUIREMENT]{0}, ", "),
Return = Table.AddColumn(
Data,
"Listing",
each [
S = Text.Split([QUALIFIED TECHNOLOGIES], " ; "),
R =
if List.ContainsAll(S, SLR) then
"Shortlist"
else if List.ContainsAll(S, LLR) then
"Longlist"
else
"Disqualify"
][R]
)
in
ReturnPower Query solution 3 for Lookingup Common Data, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Short = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
"Short",
each Text.Split([SHORTLIST REQUIREMENT], ", ")
)[Short]{0},
Long = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
"Long",
each Text.Split([LONGLIST REQUIREMENT], ", ")
)[Long]{0},
Sol = Table.AddColumn(
Source,
"Listing",
each
let
a = Text.Split([QUALIFIED TECHNOLOGIES], " ; "),
b =
if List.ContainsAll(a, Short) then
"Shortlist"
else if List.ContainsAll(a, Long) then
"Longlist"
else
"Disqualify"
in
b
)
in
SolPower Query solution 4 for Lookingup Common Data, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCat = Table.AddColumn(
Source,
"Listing",
each [
z = [QUALIFIED TECHNOLOGIES],
a = List.Transform(Text.Split(Source[SHORTLIST REQUIREMENT]{0}, ", "), Text.Trim),
b = List.Transform(Text.Split(Source[SHORTLIST REQUIREMENT]{3}, ", "), Text.Trim),
c = List.Transform(Text.Split(z, ";"), Text.Trim),
d = List.Intersect({a, c}),
e = List.Intersect({b, c}),
f =
if List.Count(d) = 3 then
"Shortlist"
else if List.Count(e) = 2 then
"Longlist"
else
"Disqualify"
][f]
),
RemCols = Table.RemoveColumns(AddCat, {"LISTING", "Column1", "SHORTLIST REQUIREMENT"})
in
RemColsSolving the challenge of Lookingup Common Data with Excel
Excel solution 1 for Lookingup Common Data, proposed by Rick Rothstein:
=LET(
f,
LAMBDA(
t,
l,
LET(
s,
TEXTSPLIT(
l,
", "),
COUNT(
FIND(
s,
t))=COUNTA(
s))),
IF(
f(
C3,
F$3),
"Shortlist",
IF(
f(
C3,
F$6),
"Longlist",
"Disqualify")))
Note: Both this formula and my previously posted formula react correctly to changes in cells F3 and F6,
even when those changes make either (or both)Excel solution 2 for Lookingup Common Data, proposed by Rick Rothstein:
=MAP(
C3:C16,
LAMBDA(
r,
LET(
f,
LAMBDA(
t,
l,
LET(
s,
TEXTSPLIT(
l,
", "),
COUNT(
FIND(
s,
t))=COUNTA(
s))),
IF(
f(
r,
F$3),
"Shortlist",
IF(
f(
r,
F$6),
"Longlist",
"Disqualify")))))Excel solution 3 for Lookingup Common Data, proposed by 🇰🇷 Taeyong Shin:
=IF(
COUNT(
SEARCH(
"*" & SUBSTITUTE(
$F$3:$F$6,
",",
"*") & "*",
C3 & C3 & C3)) > 1,
LEFT(
TEXTJOIN(
REPT(
" ",
10),
,
REPT(
{"Shortlist";"";"";"Longlist"},
ISNUMBER(
SEARCH(
"*" & SUBSTITUTE(
$F$3:$F$6,
",",
"*") & "*",
C3 & C3 & C3)) ) ),
15),
"Disqualify"
)Excel solution 4 for Lookingup Common Data, proposed by Julian Poeltl:
=MAP(
C3:C16,
LAMBDA(
T,
LET(
SP,
TEXTSPLIT(
T,
" ; "),
SPS,
SUM(
--ISNUMBER(
SEARCH(
SP,
F3)))=3,
SPL,
SUM(
--ISNUMBER(
SEARCH(
SP,
F6)))=2,
IFS(
SPS,
"Shortlist",
SPL,
"Longlist",
1,
"Disqualify"))))Excel solution 5 for Lookingup Common Data, proposed by Oscar Mendez Roca Farell:
=MAP(
C3:C16,
LAMBDA(
a,
LET(
F,
LAMBDA(
x,
y,
AND(
ISNUMBER(
FIND(
TEXTSPLIT(
x,
", "),
y)))),
IF(
F(
F3,
a),
"Shortlist",
IF(
F(
F6,
a),
"Longlist",
"Disqualify")))))Excel solution 6 for Lookingup Common Data, proposed by Sunny Baggu:
=MAP(
C3:C16,
LAMBDA(
x,
LET(
_ts,
TEXTSPLIT(
x,
,
" ; "),
IFERROR(
IFS(
ROWS(
TOCOL(
SEARCH(
_ts,
F3),
3)) = 3,
"Shortlist",
ROWS(
TOCOL(
SEARCH(
_ts,
F6),
3)) = 2,
"Longlist",
1,
"Disqualify"
),
"Disqualify"
)
)
)
)Excel solution 7 for Lookingup Common Data, proposed by Sunny Baggu:
=MAP(
C3:C16,
LAMBDA(
t,
LET(
_ts,
TEXTSPLIT(
t,
,
" ; "),
IFS(
SUM(
N(
_ts = TEXTSPLIT(
F3,
", "))) = 3,
"Shortlist",
SUM(
N(
_ts = TEXTSPLIT(
F6,
", "))) = 2,
"Longlist",
1,
"Disqualify"
)
)
)
)Excel solution 8 for Lookingup Common Data, proposed by Asheesh Pahwa:
=LET(
sr,
F3,
Ir,
F6,
tsr,
TEXTSPLIT(
sr,
,
", "),
tlr,
TEXTSPLIT(
Ir,
,
", "),
DROP(
REDUCE(
"",
C3:C16,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
t,
TEXTSPLIT(
v,
,
" ; "),
xs,
SUM(
--ISNUMBER(
XMATCH(
t,
tsr))),
IF(
xs=3,
"Shortlist",
IF(
SUM(
--ISNUMBER(
XMATCH(
t,
tlr)))=2,
"Longlist",
"Disqualify")))))),
1))Excel solution 9 for Lookingup Common Data, proposed by Thang Van:
=LET(
_function,
LAMBDA(
str,
list,
LET(
_ts,
TEXTSPLIT(
list,
", "),
COUNT(
SEARCH(
_ts,
str))=COUNTA(
_ts))),
_result,
LAMBDA(
_a,
SWITCH(
TRUE,
_function(
_a,
$F$6),
"Long list",
_function(
_a,
$F$3),
"Short list",
"Disqualify")),
MAP(
C3:C16,
LAMBDA(
_each,
_result(
_each))))Excel solution 10 for Lookingup Common Data, proposed by Ankur Sharma:
=LET(
a,
TEXTSPLIT(
F3,
", "),
b,
TEXTSPLIT(
F6,
", "),
MAP(
C3:C16,
LAMBDA(
z,
IF(
COUNTA(
a) = COUNT(
SEARCH(
a,
z)),
"Shortlist",
IF(
COUNTA(
b) = COUNT(
SEARCH(
b,
z)),
"Longlist",
"Disqualify")))))Excel solution 11 for Lookingup Common Data, proposed by JvdV –:
=SWITCH(
MAX(
ISERR(
FILTERXML(
"<t><s>"&SUBSTITUTE(
IF(
{1,
0},
F$3,
F$6),
", ",
"</s><s>")&"</s></t>",
"//s[not(contains('"&C3&"',.))]"))*{2,
1}),
0,
"Disqualify",
2,
"Shortlist",
"Longlist")Excel solution 12 for Lookingup Common Data, proposed by Milan Shrimali:
=MAP(
C2:C5,
LAMBDA(
x,
LET(
range,
TRIM(
TOCOL(
TEXTSPLIT(
x,
";"))),
short,
TRIM(
TOCOL(
TEXTSPLIT(
$F$2,
","))),
long,
TRIM(
TOCOL(
TEXTSPLIT(
$F$6,
","))),
matchshortlist,
COUNT(
XMATCH(
short,
range,
0,
1)),
matchlonglist,
COUNT(
XMATCH(
long,
range,
0,
1)),
max,
MAX(
matchshortlist,
matchlonglist),
IFS(
max=3,
"Shortlist",
max=2,
"Longlist",
max<2,
"Disqualify"))))Excel solution 13 for Lookingup Common Data, proposed by Peter Bartholomew:
= IFS(
MAP(qualifiedTechnologies,
ListCompareλ(shortlistRequirement)),
"ShortList",
MAP(qualifiedTechnologies,
ListCompareλ(longlistRequirement)),
"LongList",
TRUE,
"Disqualify")
Of course the issue is then the function
ListCompareλ
= LAMBDA(
masterList,
LAMBDA(
list,
LET(
mList,
TRIM(
TEXTSPLIT(
masterList,
,
",")),
qList,
TRIM(
TEXTSPLIT(
list,
";")),
AND(
BYROW(
qList = mList,
OR))
)
)
)Excel solution 14 for Lookingup Common Data, proposed by Amardeep Singh:
=LET(rng,
C3:C16,
_f1,
LAMBDA(
x,
TRIM(
TEXTSPLIT(
x,
,
{",",
";"}))),
sl,
_f1(F3),
ll,
_f1(F6),
_f2,
LAMBDA(
x,
y,
ISNUMBER(
SUM(
SEARCH(
x,
y)))),
BYROW(rng,
LAMBDA(z,
IFS(_f2(sl,
z),
"Shortlist",
_f2(ll,
z),
"Longlist",
TRUE,
"Disqualify"))))Excel solution 15 for Lookingup Common Data, proposed by Burhan Cesur:
=LET(
s,
LAMBDA(
x,
TRIM(
TEXTSPLIT(
x,
{",",
";"}))),
z,
LAMBDA(c,
f,
SUM(BYCOL(--(TOCOL(
s(
c))=s(
f)),
SUM))),
MAP(
C3:C16,
LAMBDA(
b,
IFS(
z(
b,
F3)=3,
"Shortlist",
z(
b,
F6)=2,
"Longlist",
1,
"Disqualify"))))Excel solution 16 for Lookingup Common Data, proposed by Agah Dikici:
=LET(
c,
C3:C16,
s,
TEXTSPLIT(
$F$3,
", "),
l,
TEXTSPLIT(
$F$6,
", "),
a,
BYROW(
FIND(
s,
c),
LAMBDA(
x,
IFERROR(
SUM(
x),
0))),
b,
BYROW(
FIND(
l,
c),
LAMBDA(
x,
IFERROR(
SUM(
x),
0))),
IF(
a,
"Shortlist",
IF(
b,
"Longlist",
"Disqualify")))Solving the challenge of Lookingup Common Data with Python in Excel
Python in Excel solution 1 for Lookingup Common Data, proposed by Owen Price:
Here's a Python in Excel option.
1) Define a function to create a set of requirements for a short/long list