Extract the least performed subject and marks Dynamic array function allowed but Extra marks for Legacy Array Functions or PowerQuery Solution
📌 Challenge Details and Links
Challenge Number: 15
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Advanced Lookup Data Separated by a Carriage Return with Power Query
Power Query solution 1 for Advanced Lookup Data Separated by a Carriage Return, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.AddColumn(
Source,
"Answer",
each Text.Combine(
List.FirstN(
List.Transform(
List.Sort(
List.Transform(
List.FirstN(
List.Sort(Text.Split([#"Subjects & Marks"], "#(lf)"), Order.Descending),
4
),
each Text.End(_, 2) & _
)
),
each Text.End(_, Text.Length(_) - 2)
),
1
)
)
)
in
AnsPower Query solution 2 for Advanced Lookup Data Separated by a Carriage Return, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Origen,
"Worst Perform",
each
let
a = Text.Split(Record.ToList(_){1}, "#(lf)"),
b = List.Transform(a, each {_, Number.From(Text.Select(_, {"0" .. "9"}))}),
c = List.Select(b, each _{1} = List.Min(List.Transform(b, each _{1}))){0}
in
c{0}
)[[Worst Perform]]
in
SolPower Query solution 3 for Advanced Lookup Data Separated by a Carriage Return, proposed by Mahmoud Bani Asadi:
[
a = Text.Split([#"Subjects & Marks"], "#(lf)"),
b = List.Transform(a, each Number.From(Text.AfterDelimiter(_, ": "))),
c = a{List.PositionOf(b, List.Min(b))}
][c]Solving the challenge of Advanced Lookup Data Separated by a Carriage Return with Excel
Excel solution 1 for Advanced Lookup Data Separated by a Carriage Return, proposed by Rick Rothstein:
=LET(
t,
TEXTSPLIT(
C3,
CHAR(
10)),
f,
FILTER(
t,
LEN(
t)),
a,
0+TEXTAFTER(
f,
":"),
FILTER(
f,
a=MIN(
a)))
or this single formula in cell D3 which will spill the entire result...
=MAP(
C3:C5,
LAMBDA(
x,
LET(
t,
TEXTSPLIT(
x,
CHAR(
10)),
f,
FILTER(
t,
LEN(
t)),
a,
0+TEXTAFTER(
f,
":"),
FILTER(
f,
a=MIN(
a)))))Excel solution 2 for Advanced Lookup Data Separated by a Carriage Return, proposed by محمد حلمي:
=INDEX(
e,
SMALL(
IF(
--RIGHT(
e,
2)=
MIN(
--RIGHT(
e,
2)),
ROW(
$1:$4)),
1))
e refer to
=TRIM(
MID(
SUBSTITUTE(
Sheet1!$C3,
CHAR(
10),
REPT(
" ",
99)),
99*ROW(
Sheet1!$1:$4)-98,
99))Excel solution 3 for Advanced Lookup Data Separated by a Carriage Return, proposed by 🇰🇷 Taeyong Shin:
=MAP(
C3:C5,
LAMBDA(
x,
TEXTJOIN(
":",
,
TAKE(
SORT(
TEXTSPLIT(
x,
":",
CHAR(
10),
1),
2),
1))))
=LET(
func,
LAMBDA(
x,
TEXTJOIN(
":",
,
TAKE(
SORT(
MAP(
TEXTSPLIT(
x,
":",
CHAR(
10),
1),
IFNA(
HSTACK(
T,
VALUE),
C3:C5),
LAMBDA(
x,
f,
f(
x))),
2),
1 ))),
MAP(
C3:C5,
func)
)Excel solution 4 for Advanced Lookup Data Separated by a Carriage Return, proposed by 🇰🇷 Taeyong Shin:
=TRIM(
CLEAN(
RIGHT(
LEFT(
C3,
FIND(
MIN(
--CLEAN(
MID(
C3,
FIND(
REPT(
"|",
ROW(
$1:$4)),
SUBSTITUTE(
C3,
":",
REPT(
"|",
ROW(
$1:$4)),
ROW(
$1:$4)))+1,
3))),
C3)+1),
12)))Excel solution 5 for Advanced Lookup Data Separated by a Carriage Return, proposed by JvdV –:
=FILTERXML(
"<t><s>"&SUBSTITUTE(
C3,
CHAR(
10),
"</s><s>")&"</s></t>",
"//s[substring-after(.,':')="&MIN(
IF(
ISERR(
FIND(
":"&{" ",
""}&ROW(
$1:$100)-1&CHAR(
10),
C3&CHAR(
10))),
100,
ROW(
$1:$100)-1))&"]")