Marks for various tests are given in problem table. For passing, at least 40 marks are required. List the students against the subjects who passed in that subject. Sort the subjects and students in ascending order.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 222
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List Subject Pass Students with Power Query
Power Query solution 1 for List Subject Pass Students, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
P = List.TransformMany(
Table.ToRows(Source),
each
let
c = List.RemoveNulls(List.Skip(_))
in
List.Zip(List.Split(c, List.Count(c) / 2)),
(i, _) => {i{0}} & _
),
D = List.Zip(
List.TransformMany(
List.Sort(List.Distinct(List.Zip(P){1})),
each
let
s = List.Select(P, (r) => r{1} = _ and r{2} > 39)
in
{{List.Zip(s){0}, {}}{Byte.From(s = {})}},
(i, _) => {i} & List.Sort(_)
)
),
S = Table.FromColumns(
D,
List.TransformMany(
{1 .. List.Count(D)},
each {"Student" & Text.From(_ - 1)},
(i, _) => {_, "Subjects"}{Byte.From(i = 1)}
)
)
in
SPower Query solution 2 for List Subject Pass Students, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.UnpivotOtherColumns(A, {"Student"}, "A", "V"),
C = Table.SplitColumn(
B,
"A",
Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}),
{"Des", "Tes"}
),
D = Table.Pivot(C, List.Distinct(C[Des]), "Des", "V"),
E = Table.SelectRows(D, each [Marks] > 39),
F = Table.FromColumns({List.Difference(List.Distinct(D[Test]), E[Test])}, {"Test"}),
G = Table.RemoveColumns(E, {"Marks", "Tes"}),
H = Table.Combine(Table.Group(G, {"Test"}, {"All", each Table.AddIndexColumn(_, "Id", 1)})[All]),
I = Table.TransformColumns(H, {"Id", each "Student" & Text.From(_)}),
J = F & Table.Pivot(I, List.Distinct(I[Id]), "Id", "Student"),
K = Table.Sort(J, {"Test", 0}),
L = Table.RenameColumns(K, {"Test", "Subjects"})
in
LPower Query solution 3 for List Subject Pass Students, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Lista = List.Transform(
Table.ToRows(Source),
each
let
a = List.Skip(_),
b = List.Count(a) / 2,
c = List.Zip(List.Split(a, b)),
d = List.Select(c, each _ <> {null, null}),
e = Table.SelectRows(Table.FromRows(d), each [Column2] >= 40)
in
e
),
Tbl = Table.FromColumns({Source[Student], Lista}, {"Col1", "Col2"}),
Expand = Table.ExpandTableColumn(Tbl, "Col2", {"Column1", "Column2"}),
Group = Table.Group(
Expand,
{"Column1"},
{
{
"A",
each
let
a = [Col1],
b = Table.FromRows(
{a},
List.Transform({1 .. List.Count(a)}, each "Student" & Text.From(_))
)
in
b
}
}
),
Subj = Table.FromColumns(
{
List.Sort(
List.RemoveNulls(
List.Distinct(
List.Combine(
Table.ToRows(
Table.SelectColumns(
Source,
List.Select(Table.ColumnNames(Source), (x) => Text.Contains(x, "Test"))
)
)
)
)
)
)
},
{"Subjects"}
),
Join = Table.AddColumn(Subj, "B", (x) => Table.SelectRows(Group, each x[Subjects] = [Column1])[A]),
Expand2 = Table.ExpandListColumn(Join, "B"),
Sol = Table.ExpandTableColumn(
Expand2,
"B",
Table.ColumnNames(Table.Combine(List.RemoveNulls(Expand2[B])))
)
in
SolPower Query solution 4 for List Subject Pass Students, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
rpl = Table.ReplaceValue(
Fonte,
null,
null,
(a, b, c) => try if a < 40 then null else a otherwise null,
List.Select(Table.ColumnNames(Fonte), each Text.StartsWith(_, "Marks"))
),
tab = Table.AddColumn(
rpl,
"tab",
each
let
Std = [Student],
a = List.RemoveFirstN(Record.FieldValues(_), 1),
b = Table.FromColumns(List.Split(a, List.Count(a) / 2)),
c = Table.SelectRows(b, each [Column2] <> null),
d = Table.AddColumn(b, "Student", each Std)
in
d
)[tab],
cmb = Table.Combine(tab),
grp = Table.Group(
cmb,
{"Column1"},
{
{
"col",
(y) =>
let
a = Table.FromRows(
{Table.Sort(Table.SelectRows(y, each [Column2] <> null), {"Column2", 1})[Student]}
),
b = List.Transform(Table.ColumnNames(a), (x) => Text.Replace(x, "Column", "Studant"))
in
Table.AddColumn(
Table.RenameColumns(a, List.Zip({Table.ColumnNames(a), b})),
"Subject",
each y[Column1]{0}
)
}
}
)[col],
cm = Table.Combine(grp),
flt = Table.SelectRows(cm, each ([Subject] <> null)),
res = Table.SelectColumns(
flt,
{"Subject"} & List.RemoveItems(Table.ColumnNames(flt), {"Subject"})
)
in
resPower Query solution 5 for List Subject Pass Students, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData222"]}[Content],
N = (Table.ColumnCount(Source) - 1) / 2,
Transform = Table.TransformRows(
Source,
each
let
_v = Record.FieldValues(_),
_lsm = List.Select(List.Zip(List.Split(List.Skip(_v), N)), each _{0} <> null),
_t = Table.FromRows(_lsm, {"Subject", "Mark"})
in
Table.AddColumn(_t, "Student", each _v{0})
),
Group = Table.Group(
Table.Combine(Transform),
"Subject",
{
"G",
each
let
_v = List.Sort(Table.SelectRows(_, each [Mark] >= 40)[Student]),
_CN = List.Transform({1 .. List.Count(_v)}, each "Studend_" & Text.From(_)),
_Result = Table.FromRows({_v}, _CN)
in
_Result
}
),
Expand = Table.ExpandTableColumn(
Group,
"G",
List.Max(
List.Transform(Group[G], Table.ColumnNames),
{},
(x, y) => Number.From(List.Count(x) > List.Count(y))
)
),
Sort = Table.Sort(Expand, {{"Subject", Order.Ascending}})
in
SortPower Query solution 6 for List Subject Pass Students, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(
Source,
"T",
each Table.SelectRows(
Table.FromColumns(List.Split(List.Skip(Record.ToList(_), 1), 4), {"Subject", "Mark"}),
each [Subject] <> null and [Mark] <> null
)
),
B = Table.SelectColumns(A, {"Student", "T"}),
C = Table.ExpandTableColumn(B, "T", {"Subject", "Mark"}, {"Subject", "Mark"}),
D = Table.Distinct(Table.Sort(Table.SelectColumns(C, {"Subject"}), {"Subject"})),
E = Table.AddColumn(
D,
"T",
each Table.SelectRows(C, (x) => [Subject] = x[Subject] and x[Mark] >= 40)
),
F = Table.ExpandTableColumn(E, "T", {"Student"}, {"Student"}),
G = Table.Group(
F,
{"Subject"},
{{"Tbl", each _, type table [Subject = text, Student = nullable text]}}
),
H = Table.AddColumn(
G,
"Tbl2",
each Table.AddIndexColumn(Table.Sort([Tbl], {"Student"}), "i", 1, 1)
),
I = Table.SelectColumns(H, {"Tbl2"}),
J = Table.ExpandTableColumn(I, "Tbl2", {"Subject", "Student", "i"}, {"Subject", "Student", "i"}),
K = Table.TransformColumns(J, {{"i", each "Student " & Text.From(_, "en-US"), type text}}),
M = Table.Pivot(K, List.Distinct(K[i]), "i", "Student")
in
MPower Query solution 7 for List Subject Pass Students, proposed by Ahmed Ariem:
let
f = (x) =>
Table.AddColumn(
x,
"Subjects",
each List.Transform(
List.Transform(
List.PositionOf(List.Skip(Record.ToList(_), 5), 40, 2, (x) => x >= 40),
(x) => x + 1
),
(x) => Record.ToList(_){x}
)
)[[Student], [Subjects]],
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Expand = Table.ExpandListColumn(f(Source), "Subjects"),
Group = Table.Group(Expand, "Subjects", {"Student", (x) => Text.Combine(x[Student], ",")}),
Split = Table.SplitColumn(
Group,
"Student",
Splitter.SplitTextByDelimiter(","),
{"Student.1", "Student.2", "Student.3"}
)
in
SplitPower Query solution 8 for List Subject Pass Students, proposed by Alexandre Garcia:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
subjects = Table.FromColumns(
{
List.Sort(
List.Distinct(
List.RemoveNulls(
List.Combine(
Table.ToRows(
Table.SelectColumns(
Source,
List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Test"))
)
)
)
)
),
Order.Ascending
)
},
{"Subjects"}
),
sucess = Table.AddColumn(
Source,
"x",
each
let
x = List.Skip(Record.ToList(_)),
y = List.Zip(List.Split(x, List.Count(x) / 2))
in
Table.SelectRows(Table.FromRows(y), each [Column2] <> null and [Column2] >= 40)
),
Expand = Table.ExpandTableColumn(sucess, "x", {"Column1", "Column2"}),
rec =
let
x = Table.Group(
Expand,
{"Column1"},
{{"x", each Table.Sort(_, {{"Column2", 1}, {"Student", 0}})[Student]}}
)
in
Record.FromList(x[x], x[Column1]),
Result = [
a = Table.AddColumn(subjects, "x", each Record.FieldOrDefault(rec, [Subjects])),
b = Table.TransformColumns(
a,
{
"x",
each
if _ <> null then
Table.FromRows(
{_},
List.Accumulate({1 .. List.Count(_)}, {}, (s, c) => s & {"Student" & Text.From(c)})
)
else
_
}
),
c = List.Distinct(List.Combine(List.Transform(List.RemoveNulls(b[x]), Table.ColumnNames))),
d = Table.ExpandTableColumn(b, "x", c)
][d]
in
ResultPower Query solution 9 for List Subject Pass Students, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Student"},
{{"Count", each Table.FromColumns(List.Split(List.Skip(Record.FieldValues(_{0})), 4))}}
),
#"Expanded Count" = Table.ExpandTableColumn(
#"Grouped Rows",
"Count",
{"Column1", "Column2"},
{"Subjects", "Column2"}
),
#"Filtered Rows" = Table.SelectRows(#"Expanded Count", each [Column2] >= 40),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows", {"Column2"}),
#"Grouped Rows1" = Table.Group(
#"Removed Columns",
{"Subjects"},
{{"Count", each Text.Combine(List.Sort(_[Student]), ",")}}
),
Split = Table.SplitColumn(
#"Grouped Rows1",
"Count",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{"Subject1", "Subject2", "Subject3"}
),
Custom1 = Table.SelectRows(
Table.Distinct(#"Expanded Count"[[Subjects]]),
each _[Subjects] <> null
),
#"Sorted Rows" = Table.Sort(Custom1, {{"Subjects", Order.Ascending}}),
#"Merged Queries" = Table.NestedJoin(
#"Sorted Rows",
{"Subjects"},
Split,
{"Subjects"},
"Sorted Rows",
JoinKind.LeftOuter
),
#"Expanded Sorted Rows" = Table.ExpandTableColumn(
#"Merged Queries",
"Sorted Rows",
{"Subject1", "Subject2", "Subject3"},
{"Subject1", "Subject2", "Subject3"}
)
in
#"Expanded Sorted Rows"Power Query solution 10 for List Subject Pass Students, proposed by Francesco Bianchi 🇮🇹:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom = [
LT = List.Transform,
a = Table.ToRows(Source),
b = LT(a, each List.Skip(_)),
c = LT(b, each List.Split(_, 4)),
d = LT(c, each List.Zip({_{0}, _{1}})),
e = LT(d, each Table.FromRows(_, {"Test", "Mark"})),
f = Table.FromColumns({LT(a, each List.First(_)), e}, {"Student", "Tbl"})
][f],
ExpandedColumn = Table.ExpandTableColumn(Custom, "Tbl", {"Test", "Mark"}, {"Subject", "Mark"}),
Filter = Table.SelectRows(ExpandedColumn, each [Subject] <> null),
ReplacedValue = Table.ReplaceValue(
Filter,
each [Student],
each if [Mark] >= 40 then [Student] else null,
Replacer.ReplaceValue,
{"Student"}
),
#"Grouped Rows" = Table.Group(
ReplacedValue,
{"Subject"},
{{"Student", each Text.Combine(_[Student], "|")}}
),
SortedRows = Table.Sort(#"Grouped Rows", {{"Subject", Order.Ascending}}),
SplitCol = Table.SplitColumn(
SortedRows,
"Student",
Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
{"Student.1", "Student.2", "Student.3"}
)
in
SplitColSolving the challenge of List Subject Pass Students with Excel
Excel solution 1 for List Subject Pass Students, proposed by Bo Rydobon 🇹🇭:
=LET(s,
B2:E7,
r,
DROP(REDUCE(0,
SORT(
UNIQUE(
TOCOL(
s,
3
)
)
),
LAMBDA(a,
v,
IFNA(VSTACK(a,
HSTACK(v,
IFERROR(SORT(TOROW(IFS((s=v)*(F2:I7>39),
A2:A7),
3),
,
,
1),
""))),
""))),
1),
VSTACK(
HSTACK(
"Subjects",
"Student"&SEQUENCE(
,
COLUMNS(
r
)-1
)
),
r
))Excel solution 2 for List Subject Pass Students, proposed by 🇰🇷 Taeyong Shin:
=LET(t,
TOCOL(
B2:E7
),
m,
F2:I7,
n,
SEQUENCE(
ROWS(
t
)
),
z,
IF(
TOCOL(
m
)>=40,
t,
n
),
pv,
PIVOTBY(t,
"Student"&MMULT((z=TOROW(
T(
z
)
))*(n>=TOROW(
n
)),
SIGN(
n
)),
TOCOL(
A2:A7&T(
+m
)
),
SINGLE,
,
0,
,
0,
,
t>0),
FILTER(
pv,
TAKE(
pv,
1
)<>"Student0"
))Excel solution 3 for List Subject Pass Students, proposed by Julian Poeltl:
=LET(
A,
B2:E7,
C,
BYROW(
IF(
F2:I7>39,
A,
""
),
CONCAT
),
IFNA(
REDUCE(
HSTACK(
"Subjects",
"Student"&SEQUENCE(
,
3
)
),
SORT(
UNIQUE(
TOCOL(
A,
3
)
)
),
LAMBDA(
A,
B,
VSTACK(
A,
HSTACK(
B,
IFERROR(
TOROW(
SORT(
FILTER(
A2:A7,
ISNUMBER(
SEARCH(
B,
C
)
)
)
)
),
""
)
)
)
)
),
""
)
)Excel solution 4 for List Subject Pass Students, proposed by Hussein SATOUR:
=LET(
v,
VSTACK,
h,
HSTACK,
i,
IFERROR,
c,
TOCOL,
t,
B2:E7,
tm,
c(
IF(
F2:I7>=40,
A2:A7&"/"&t,
1/0
),
2
),
s,
SORT(
UNIQUE(
c(
t,
1
)
)
),
a,
IFNA(
DROP(
REDUCE(
"",
s,
LAMBDA(
x,
y,
v(
x,
i(
TOROW(
SUBSTITUTE(
FILTER(
tm,
i(
FIND(
y,
tm
),
0
)
),
"/"&y,
""
)
),
""
)
)
)
),
1
),
""
),
v(
h(
"Subjects",
"Student"&SEQUENCE(
,
COLUMNS(
a
)
)
),
h(
s,
a
)
)
)Excel solution 5 for List Subject Pass Students, proposed by Oscar Mendez Roca Farell:
=LET(r,
REDUCE( ,
SORT(
UNIQUE(
TOCOL(
B2:E7,
1
)
)
),
LAMBDA(i,
x,
IFNA(VSTACK(i,
HSTACK(x,
IFERROR(SORT(TOROW(IFS((B2:E7=x)*(F2:I7)>39),
A2:A7),
2),
,
,
1),
""))),
""))),
VSTACK(
HSTACK(
"Subjects",
A1&SEQUENCE(
,
COLUMNS(
r
)-1
)
),
r
))Excel solution 6 for List Subject Pass Students, proposed by Duy Tùng:
=LET(
I,
INDEX,
a,
F2:I7,
f,
LAMBDA(
x,
TOCOL(
IFS(
a>=40,
x
),
3
)
),
b,
f(
B2:E7
),
c,
MATCH(
b,
b,
),
d,
HSTACK(
f(
A2:A7
),
b,
f(
a
)
),
REDUCE(
HSTACK(
"Subjects",
"Student"&SEQUENCE(
,
MAX(
FREQUENCY(
c,
c
)
)
)
),
SORT(
UNIQUE(
TOCOL(
B2:E7,
1
)
)
),
LAMBDA(
x,
y,
IFNA(
VSTACK(
x,
HSTACK(
y,
TOROW(
SORT(
FILTER(
I(
d,
,
1
),
I(
d,
,
2
)=y,
""
)
)
)
)
),
""
)
)
)
)Excel solution 7 for List Subject Pass Students, proposed by Sunny Baggu:
=LET(
_s1, SORT(UNIQUE(TOCOL(B2:E7, 3))),
_s2, IFNA(
DROP(
REDUCE(
"🌻",
_s1,
LAMBDA(x, y,
VSTACK(
x,
IFERROR(
TOROW(
SORT(
FILTER(
A2:A7,
BYROW(IF(B2:E7 = y, F2:I7, 0) >= 40, LAMBDA(a, OR(a)))
)
)
),
""
)
)
)
),
1
),
""
),
_s3, "Student" & SEQUENCE(, COLUMNS(_s2)),
VSTACK(HSTACK("Subjects", _s3), HSTACK(_s1, _s2))
)Excel solution 8 for List Subject Pass Students, proposed by LEONARD OCHEA 🇷🇴:
=LET(
s,
A2:A7,
t,
B2:E7,
m,
F2:I7,
F,
LAMBDA(
x,
TOCOL(
IF(
m>=40,
x,
z
),
3
)
),
IFNA(
REDUCE(
HSTACK(
"Subjects",
"Student"&SEQUENCE(
,
MAX(
BYROW(
N(
F(
t
)=TOROW(
F(
t
)
)
),
SUM
)
)
)
),
SORT(
UNIQUE(
TOCOL(
t,
1
)
)
),
LAMBDA(
a,
b,
VSTACK(
a,
HSTACK(
b,
TOROW(
SORT(
FILTER(
F(
s
),
F(
t
)=b,
""
)
)
)
)
)
)
),
""
)
)Excel solution 9 for List Subject Pass Students, proposed by Md. Zohurul Islam:
=LET(
u,
A2:A7,
v,
B2:E7,
w,
F2:I7,
z,
B1:E1,
a,
TOCOL(
IFNA(
u,
z
)
),
b,
TOCOL(
v
),
c,
TOCOL(
w
),
d,
FILTER(
HSTACK(
b,
a
),
c>=40
),
e,
SORT(
UNIQUE(
TOCOL(
v,
3
)
)
),
f,
DROP(
REDUCE(
"",
e,
LAMBDA(
x,
y,
IFNA(
VSTACK(
x,
TOROW(
SORT(
FILTER(
DROP(
d,
,
1
),
DROP(
d,
,
-1
)=y,
""
)
)
)
),
""
)
)
),
1
),
g,
VSTACK(
A1&SEQUENCE(
,
COLUMNS(
f
)
),
f
),
h,
HSTACK(
VSTACK(
"Subjects",
e
),
g
),
h
)Excel solution 10 for List Subject Pass Students, proposed by Hamidi Hamid:
=LET(tr,
SORT(
UNIQUE(
TOCOL(
IF(
B2:E7=0,
1/0,
B2:E7
),
3
)
)
),
m,
TOCOL(
IF(
F2:I7<40,
"-",
A2:A7
)
),
o,
TOCOL(
IF(
F2:I7<1,
"-",
B2:E7
),
2
),
as,
HSTACK(
o,
m
),
x,
IFNA(
DROP(
REDUCE(
,
SORT(
MAP(
UNIQUE(
o
),
LAMBDA(
a,
TEXTJOIN(
"-",
1,
FILTER(
as,
o=a,
0
)
)
)
)
),
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
"-",
,
1
)
)
)
),
1
),
0
),
d,
IF(
(
ISERROR(
VLOOKUP(
x,
tr,
1,
0
)
)
),
x,
0
),
l,
TAKE(IF((NOT(
ISERROR(
VLOOKUP(
x,
tr,
1,
0
)
)
)),
x,
""),
,
1),
z,
CHOOSECOLS(
d,
SEQUENCE(
,
COLUMNS(
d
)/2,
2,
2
)
),
sd,
HSTACK(
l,
d
),
gt,
BYCOL(
sd,
LAMBDA(
a,
SUM(
IF(
a=0,
1,
0
)
)
)
),
uu,
(gt=MAX(
gt
))*1,
DROP(
SORTBY(
VSTACK(
sd,
uu
),
uu,
1
),
-1
))Excel solution 11 for List Subject Pass Students, proposed by Asheesh Pahwa:
=LET(
d,
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
A2:E7
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
I,
INDEX(
A2:E7,
y,
),
d,
DROP(
I,
,
1
),
t,
TOCOL(
d,
1
)&"-"&TAKE(
I,
,
1
),
DROP(
REDUCE(
"",
t,
LAMBDA(
x,
y,
VSTACK(
x,
TEXTSPLIT(
y,
"-"
)
)
)
),
1
)
)
)
)
),
1
),
m,
TOCOL(
F2:I7,
1
),
tk,
TAKE(
d,
,
1
),
_tk,
TAKE(
d,
,
-1
),
u,
UNIQUE(
tk
),
_d,
DROP(
REDUCE(
"",
u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
HSTACK(
_tk,
m
),
tk=y
),
t,
TAKE(
f,
,
-1
)>=40,
ARRAYTOTEXT(
SORT(
FILTER(
TAKE(
f,
,
1
),
t,
""
)
)
)
)
)
)
),
1
),
HSTACK(
SORT(
u
),
SORTBY(
_d,
u,
1
)
)
)Excel solution 12 for List Subject Pass Students, proposed by ferhat CK:
=LET(a,
TOCOL(
B2:E7
),
b,
TOCOL(
F2:I7
),
c,
DROP(
REDUCE(
"",
A2:A7,
LAMBDA(
x,
y,
VSTACK(
x,
SUBSTITUTE(
SEQUENCE(
4
)^0&y,
1,
""
)
)
)
),
1
),
d,
SORT(
UNIQUE(
FILTER(
a,
a<>0
)
)
),
e,
HSTACK(
a,
c,
b
),
HSTACK(d,
IFNA(DROP(REDUCE(0,
d,
LAMBDA(x,
y,
VSTACK(x,
LET(r,
IFERROR(SORT(FILTER(e,
(a=y)*(b>39)),
3,
-1),
{"",
"",
""}),
TOROW(
CHOOSECOLS(
r,
2
)
))))),
1),
"")))Excel solution 13 for List Subject Pass Students, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(i,
SORT(
UNIQUE(
LET(
a,
TOCOL(
B2:E7
),
FILTER(
a,
a<>0
)
)
),
,
1
),
HSTACK(i,
DROP(TEXTSPLIT(TEXTJOIN(,
,
IFERROR(MAP(i,
LAMBDA(y,
TEXTJOIN(",",
,
SORT(LET(x,
TOCOL(IF(((y=B2:E7)*(F2:I7>=40))>0,
A2:A7,
0)),
FILTER(
x,
x<>0
)),
,
1)))),
"")&"/"),
",",
"/"),
-1)))Excel solution 14 for List Subject Pass Students, proposed by Imam Hambali:
=LET(
sbj,
TOCOL(
B2:E7
),
mrks,
TOCOL(
F2:I7
),
std,
TOCOL(
IF(
SEQUENCE(
,
4
),
A2:A7
)
),
data,
SORTBY(
HSTACK(
std,
sbj,
mrks
),
sbj,
1,
std,
1
),
usbj,
UNIQUE(
SORT(
FILTER(
sbj,
sbj>0
),
1,
1
)
),
lst,
IFNA(REDUCE(B11:D11,
usbj,
LAMBDA(x,
y,
VSTACK(x,
TOROW(IFERROR(FILTER(CHOOSECOLS(
data,
1
),
(CHOOSECOLS(
data,
2
)=y)*(CHOOSECOLS(
data,
3
)>=40)),
""))))),
""),
HSTACK(
VSTACK(
"Subjects",
usbj
),
lst
)
)Excel solution 15 for List Subject Pass Students, proposed by Eddy Wijaya:
=LET(
d,
A2:I7,
db,
DROP(
REDUCE(
0,
TAKE(
d,
,
1
),
LAMBDA(
a,
v,
VSTACK(
a,
LET(
t,
OFFSE&T(
v,
,
1,
1,
4
),
m,
OFFSET(
v,
,
5,
1,
4
),
IF(
{1,
0,
0},
v,
HSTACK(
v,
TOCOL(
t
),
TOCOL(
m
)
)
)
)
)
)
),
1
),
adj_db,
SORT(
FILTER(
db,
TAKE(
db,
,
-1
)>=40
),
2
),
test,
DROP(
SORT(
UNIQUE(
CHOOSECOLS(
db,
2
),
)
),
1
),
REDUCE(
A11:D11,
test,
LAMBDA(
a,
v,
IFNA(
VSTACK(
a,
HSTACK(
v,
TRANSPOSE(
FILTER(
TAKE(
adj_db,
,
1
),
CHOOSECOLS(
adj_db,
2
)=v,
""
)
)
)
),
""
)
)
)
)Excel solution 16 for List Subject Pass Students, proposed by RIJESH T.:
=LET(student,
TOCOL(
IFNA(
A2:A7,
B1:E1
)
),
Subject,
TOCOL(
B2:E7
),
Marks,
TOCOL(
F2:I7
),
Sub,
SORT(
UNIQUE(
TOCOL(
B2:E7,
1
)
)
),
stu,
MAP(Sub,
LAMBDA(a,
TEXTJOIN(",",
TRUE,
SORT(FILTER(student,
(Subject=a)*(Marks>=40),
""))))),
Names,
REDUCE(
"",
DROP(
stu,
1
),
LAMBDA(
x,
y,
VSTACK(
x,
TEXTSPLIT(
y,
","
)
)
)
),
IFERROR(
VSTACK(
HSTACK(
"Subjects",
"Student"&SEQUENCE(
,
3
)
),
HSTACK(
Sub,
Names
)
),
""
)
)Solving the challenge of List Subject Pass Students with Python in Excel
Python in Excel solution 1 for List Subject Pass Students, proposed by Alejandro Campos:
df = xl("A1:I7", headers=True)
passed_students = [
(row[f'Test{i}'], row['Student'])
for index, row in df.iterrows()
for i in range(1, 5)
if row[f'Test{i}'] and row[f'Marks{i}'] >= 40
]
passed_df = (
pd.DataFrame(passed_students, columns=['Subject', 'Student'])
.sort_values(by=['Subject', 'Student'])
.reset_index(drop=True)
)
final_output = passed_df.groupby('Subject')['Student'].apply(list).reset_index()
all_subjects = ['Biology', 'Chemistry', 'English', 'French', 'Maths', 'Physics']
final_df = pd.DataFrame(
[
[subject] + final_output.loc[final_output['Subject'] == subject, 'Student'].values[0] + [' ']
* (3 - len(final_output.loc[final_output['Subject'] == subject, 'Student'].values[0]))
if subject in final_output['Subject'].values else [subject] + [' '] * 3
for subject in all_subjects
],
columns=['Subject', 'Student1', 'Student2', 'Student3']
)
final_df
Python in Excel solution 2 for List Subject Pass Students, proposed by Ümit Barış Köse, MSc:
df = xl("A1:I7", headers=True)
result_df = pd.DataFrame(columns=["Subjects", "Student1", "Student2", "Student3"])
subjects = set()
for test_index in range(1, 5):
subjects.update(df[f'Test{test_index}'].dropna().unique())
subject_students = {subject: [] for subject in subjects}
for index, row in df.iterrows():
student = row["Student"]
for test_index in range(1, 5):
subject = row[f'Test{test_index}']
marks = row[f'Marks{test_index}']
if subject and (marks is not None) and (marks >= 40):
subject_students[subject].append(student)
for subject in sorted(subject_students.keys()):
students = subject_students[subject]
student_list = students + [""] * (3 - len(students))
result_df = pd.concat([result_df, pd.DataFrame([[subject] + student_list], columns=result_df.columns)], ignore_index=True)
result_df
Solving the challenge of List Subject Pass Students with R
R solution 1 for List Subject Pass Students, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_222.xlsx"
input = read_excel(path, range = "A1:I7")
test = read_excel(path, range = "A11:D17")
result = input %>%
pivot_longer(cols = -c(1), names_to = c(".value", "Type"), names_pattern = "(\D+)(\d+)") %>%
mutate(rank = rank(desc(Marks), ties.method = "first"), .by = Test) %>%
select(-Type) %>%
unite("TM", Student, Marks, sep = " ") %>%
pivot_wider(names_from = rank, values_from = TM, names_prefix = "Student") %>%
select(Subjects = Test, sort(names(.)[-1])) %>%
filter(!is.na(Subjects)) %>%
mutate(across(2:ncol(.), ~ifelse(as.numeric(str_extract(., "\d+")) >= 40, str_remove(., "\s\d+"), NA_character_))) %>%
select(where(~!all(is.na(.)))) %>%
arrange(Subjects)
identical(result, test)
#> [1] TRUE
&