A1:C9: List of candidates with their primary and secondary skills. E1:F7: List of approved primary and secondary skills You will need to provide a formula to list the candidates who have at least 2 approved primary skills and at least 3 approved secondary skills.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 43
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List Candidates Matching Skills with Power Query
Power Query solution 1 for List Candidates Matching Skills, proposed by Rick de Groot:
let
Source = Source,
#"Split Text By Delimiter" = Table.TransformColumns(
Source,
{
{"Primary Skills", each try Text.Split(_, ", ") otherwise {}, type text},
{"Secondary Skills", each try Text.Split(_, ", ") otherwise {}, type text}
}
),
#"Filter Relevant Rows" = Table.SelectRows(
#"Split Text By Delimiter",
each List.Count(List.Select([Primary Skills], each List.Contains({"Java", "Python", "SQL"}, _)))
> 1
and List.Count(
List.Select(
[Secondary Skills],
each List.Contains(
{"Logic", "Analysis", "Domain", "Internet", "Windows", "MS Office"},
_
)
)
)
> 2
),
#"Removed Other Columns" = Table.SelectColumns(#"Filter Relevant Rows", {"Candidate"})
in
#"Removed Other Columns"Power Query solution 3 for List Candidates Matching Skills, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "Candidates"]}[Content],
#"Filtered Rows" = Table.SelectRows(
Source,
each try
List.Count(List.Intersect({PrimarySkills, Text.Split([Primary Skills], ", ")}))
> 1 and List.Count(List.Intersect({SecondarySkills, Text.Split([Secondary Skills], ", ")}))
> 2
otherwise
false
)[[Candidate]]
in
#"Filtered Rows"Solving the challenge of List Candidates Matching Skills with Excel
Excel solution 1 for List Candidates Matching Skills, proposed by John V.:
=LET(f,
LAMBDA(
a,
b,
BYROW(
FIND(
TOROW(
a&","
),
b&","
),
LAMBDA(
x,
COUNT(
x
)
)
)
),
FILTER(A2:A9,
(f(
E2:E4,
B2:B9
)>1)*(f(
F2:F7,
C2:C9
)>2)))
=FILTER(A2:A9,
MAP(B2:B9&",",
C2:C9&",",
LAMBDA(a,
b,
(COUNT(
FIND(
E2:E4&",",
a
)
)>1)*(COUNT(
FIND(
F2:F7&",",
b
)
)>2))))Excel solution 2 for List Candidates Matching Skills, proposed by محمد حلمي:
=FILTER(A2:A9,
MAP(B2:B9,
C2:C9,
LAMBDA(b,
c,
(SUM(--(TEXTSPLIT(
b&", ",
", "
)=E2:E4))>1)*(SUM(--(TEXTSPLIT(
c&", ",
", "
)=F2:F7))>2))))Excel solution 3 for List Candidates Matching Skills, proposed by محمد حلمي:
=FILTER(A2:A9,
MAP(B2:B9,
C2:C9,
LAMBDA(b,
c,
(SUM(--(TEXTSPLIT(
b&", ",
{" ,",
", "}
)=E2:E4))>1)*(SUM(--(TEXTSPLIT(
c&", ",
{" ,",
", "}
)=F2:F7))>2))))Excel solution 4 for List Candidates Matching Skills, proposed by 🇰🇷 Taeyong Shin:
= FILTER(A2:A9,
MAP(B2:B9,
C2:C9,
LAMBDA(a,
b,
(COUNT(
SEARCH(
E2:E4,
a
)
)>=2)*(COUNT(
SEARCH(
F2:F7,
b
)
)>=3) )))Excel solution 5 for List Candidates Matching Skills, proposed by Julian Poeltl:
=LET(C,
A2:A9,
P,
B2:B9,
S,
C2:C9,
PS,
E2:E4,
SS,
F2:F7,
FF,
LAMBDA(
D,
E,
MAP(
E,
LAMBDA(
A,
SUM(
--ISNUMBER(
XMATCH(
D,
TEXTSPLIT(
A,
", "
)
)
)
)
)
)
),
FILTER(C,
(FF(
PS,
P
)>1)*(FF(
SS,
S
)>2)))Excel solution 6 for List Candidates Matching Skills, proposed by Julian Poeltl:
=LET(C,
A2:A9,
P,
B2:B9,
S,
C2:C9,
PS,
E2:E4,
SS,
F2:F7,
FP,
MAP(
P,
LAMBDA(
A,
SUM(
--ISNUMBER(
XMATCH(
PS,
TEXTSPLIT(
A,
", "
)
)
)
)
)
),
FS,
MAP(
S,
LAMBDA(
A,
SUM(
--ISNUMBER(
XMATCH(
SS,
TEXTSPLIT(
A,
", "
)
)
)
)
)
),
FILTER(C,
(FP>1)*(FS>2)))Excel solution 7 for List Candidates Matching Skills, proposed by Aditya Kumar Darak 🇮🇳:
= LET(
_person,
A2:A9,
_p_skills,
B2:B9,
_s_skills,
C2:C9,
_rp_skills,
E2:E4,
_rs_skills,
F2:F7,
FILTER(
_person,
MAP(
_p_skills,
_s_skills,
LAMBDA(
a,
b,
(COUNT(
XMATCH(
TEXTSPLIT(
a,
", "
),
_rp_skills
)
) >= 2)
* (COUNT(
XMATCH(
TEXTSPLIT(
b,
", "
),
_rs_skills
)
) >= 3)))))Excel solution 8 for List Candidates Matching Skills, proposed by Timothée BLIOT:
=LET(
ArrayCandidate,
$A$2:$C$9,
ArrayApproved,
$E$2:$F$9,
Candidate,
INDEX(
ArrayCandidate,
,
1
),
Primary,
INDEX(
ArrayCandidate,
,
2
),
Secondary,
INDEX(
ArrayCandidate,
,
3
),
PrimaryApproved,
IF(
INDEX(
ArrayApproved,
,
1
)="",
INDEX(
ArrayApproved,
,
1
)="#",
INDEX(
ArrayApproved,
,
1
)
),
SecondaryApproved,
IF(
INDEX(
ArrayApproved,
,
2
)="",
INDEX(
ArrayApproved,
,
2
)="#",
INDEX(
ArrayApproved,
,
2
)
),
SearchPrimary,
BYROW(
Primary,
LAMBDA(
a,
SUM(
--ISNUMBER(
SEARCH(
PrimaryApproved,
a
)
)
)
)
),
SearchSecondary,
BYROW(
Secondary,
LAMBDA(
a,
SUM(
--ISNUMBER(
SEARCH(
SecondaryApproved,
a
)
)
)
)
),
Answer,
VALUE((SearchPrimary>=2)*(SearchSecondary>=3)),
FILTER(
Candidate,
Answer,
""
))Excel solution 9 for List Candidates Matching Skills, proposed by Bhavya Gupta:
=LET(
a,
BYROW(
ISNUMBER(
SEARCH(
TRANSPOSE(
E2:E4&","
),
B2:B9&","
)
),
LAMBDA(
P,
SUM(
--P
)
)
)>=2,
b,
BYROW(
ISNUMBER(
SEARCH(
TRANSPOSE(
F2:F7&","
),
C2:C9&","
)
),
LAMBDA(
S,
SUM(
--S
)
)
)>=3,
FILTER(
A2:A9,
a*b
)
)Excel solution 10 for List Candidates Matching Skills, proposed by Charles Roldan:
=LET(
f,
LAMBDA(
a,
b,
c,
BYROW(
FIND(
TOROW(
a
),
b
),
COUNT
) >= c
),
FILTER(
A2:A9,
f(
E2:E4,
B2:B9,
2
) * f(
F2:F7,
C2:C9,
3
)
)
)Excel solution 11 for List Candidates Matching Skills, proposed by Jardiel Euflázio:
=FILTER(A2:A9,
MAP(B2:B9,
C2:C9,
LAMBDA(a,
b,
(SUM(0+(ISNUMBER(
MATCH(
TEXTSPLIT(
a,
", "
),
E2:E4,
0
)
)))>=2)*(SUM(0+(ISNUMBER(
MATCH(
TEXTSPLIT(
b,
", "
),
F2:F7,
0
)
)))>=3))))Excel solution 12 for List Candidates Matching Skills, proposed by Jardiel Euflázio:
=FILTER(A2:A9,
MAP(B2:B9,
C2:C9,
LAMBDA(p,
s,
LET(a,
TEXTSPLIT(
p,
", "
),
b,
TEXTSPLIT(
s,
", "
),
(SUM(0+(ISNUMBER(
MATCH(
a,
E2:E4,
0
)
)))>=2)*(SUM(0+(ISNUMBER(
MATCH(
b,
F2:F7,
0
)
)))>=3)))))Excel solution 13 for List Candidates Matching Skills, proposed by Sarun Chimamphant:
=FILTER(A2:A9,
(BYROW(
--ISNUMBER(
SEARCH(
TRANSPOSE(
E2:E4
),
B2:B9
)
),
LAMBDA(
x,
SUM(
x
)
)
)>1)*(BYROW(
--ISNUMBER(
SEARCH(
TRANSPOSE(
F2:F7
),
C2:C9
)
),
LAMBDA(
x,
SUM(
x
)
)
)>2))
Older versions
=IFERROR(INDEX($A$2:$A$9,
SMALL(IF((MMULT(
--ISNUMBER(
SEARCH(
TRANSPOSE(
$E$2:$E$4
),
$B$2:$B$9
)
),
{1;1;1}
)>1)*(MMULT(
--ISNUMBER(
SEARCH(
TRANSPOSE(
$F$2:$F$7
),
$C$2:$C$9
)
),
{1;1;1;1;1;1}
)>2),
ROW(
$A$2:$A$9
)-1,
""),
ROWS(
I$2:I2
))),
"")Solving the challenge of List Candidates Matching Skills with DAX
DAX solution 1 for List Candidates Matching Skills, proposed by Zoran Milokanović:
EVALUATE
SELECTCOLUMNS(
FILTER(Candidates,
COUNTROWS(FILTER(Skills, FIND(Skills[Primary Skills], Candidates[Primary Skills], 1, 0) && LEN(Skills[Primary Skills]) > 0)) > 1
&&
COUNTROWS(FILTER(Skills, FIND(Skills[Secondary Skills], Candidates[Secondary Skills], 1, 0) && LEN(Skills[Secondary Skills]) > 0)) > 2
),
"Answer Expected", Candidates[Candidate]
)