Merge both problem tables into one by summing marks for common entries. Subjects and Students should be in sorted order.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 244
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sum and Sort Student Marks with Power Query
Power Query solution 1 for Sum and Sort Student Marks, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
U = Table.UnpivotOtherColumns(Source("Table1") & Source("Table2"), {"Student"}, "A", "V"),
P = Table.Pivot(U, List.Sort(List.Distinct(U[A])), "A", "V", List.Sum)
in
PPower Query solution 2 for Sum and Sort Student Marks, proposed by Kris Jaganah:
let
A = (x) => Excel.CurrentWorkbook(){[Name = x]}[Content],
B = A("Table1") & A("Table2"),
C = Table.UnpivotOtherColumns(B, {"Student"}, "A", "V"),
D = Table.Pivot(C, List.Sort(List.Distinct(C[A])), "A", "V", List.Sum)
in
DPower Query solution 3 for Sum and Sort Student Marks, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(),
Filter = Table.SelectRows(Source, each Text.StartsWith([Name], "data")),
Combine = Table.Combine(Filter[Content]),
Unpivot = Table.UnpivotOtherColumns(Combine, {"Student"}, "S", "M"),
Return = Table.Pivot(Unpivot, List.Sort(List.Distinct(Unpivot[S])), "S", "M", List.Sum)
in
ReturnPower Query solution 4 for Sum and Sort Student Marks, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Tbl1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tbl2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Sol = Table.Combine(
Table.Group(
Tbl1 & Tbl2,
{"Student"},
{
{
"A",
(x) =>
let
k = Table.ColumnNames(x),
a = Table.ToColumns(x),
b = {a{0}{0}} & List.Transform(List.Skip(a), List.Sum),
c = Table.FromRows({b}, k),
d = Table.SelectColumns(c, {k{0}} & List.Sort(List.Skip(k)))
in
d
}
}
)[A]
)
in
SolPower Query solution 5 for Sum and Sort Student Marks, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Tbl1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tbl2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Tbls = Tbl1 & Tbl2,
Unpivot = Table.UnpivotOtherColumns(Tbls, {"Student"}, "A", "V"),
Sol = Table.Pivot(Unpivot, List.Sort(List.Distinct(Unpivot[A])), "A", "V", List.Sum)
in
SolPower Query solution 6 for Sum and Sort Student Marks, proposed by Luan Rodrigues:
let
Fonte = Tabela1 & Tabela2,
grp = Table.Group(Fonte, {"Student"}, {{"tab", each
let
a = hashtag#table(List.Skip(Table.ColumnNames(_)),{List.Transform({0..Table.ColumnCount(_)-2},(x)=> List.Sum(List.Skip(Table.ToColumns(_)){x}))}),
b = Table.SelectColumns(a, List.Sort(Table.ColumnNames(a) )) in b
}}),
res = Table.ExpandTableColumn(grp, "tab", Table.ColumnNames(grp[tab]{0}) )
in
res
Power Query solution 7 for Sum and Sort Student Marks, proposed by Abdallah Ally:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
Combine = Table.Combine({Source("Table1"), Source("Table2")}),
Result = Table.Group(
Combine,
"Student",
List.Transform(
List.Sort(List.Skip(Table.ColumnNames(Combine))),
(x) => {x, each List.Sum(Table.Column(_, x))}
)
)
in
ResultPower Query solution 8 for Sum and Sort Student Marks, proposed by Abdallah Ally:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
Combine = Table.Combine({Source("Table1"), Source("Table2")}),
Unpivot = Table.UnpivotOtherColumns(Combine, {"Student"}, "Attribute", "Value"),
Result = Table.Pivot(
Unpivot,
List.Distinct(List.Sort(Unpivot[Attribute])),
"Attribute",
"Value",
List.Sum
)
in
ResultPower Query solution 9 for Sum and Sort Student Marks, proposed by Ramiro Ayala Chávez:
let
t1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
t2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
a = t1 & t2,
b = {Table.ColumnNames(a){0}} & List.Sort(List.Skip(Table.ColumnNames(a))),
c = Table.ReplaceValue(Table.SelectColumns(a, b), null, 0, Replacer.ReplaceValue, b),
d = Table.Group(
c,
{"Student"},
{
"G",
each Table.PromoteHeaders(
Table.FromColumns(
List.Zip(
{
List.Skip(Table.ColumnNames(c)),
List.Transform(List.Skip(Table.ToColumns(_)), List.Sum)
}
)
)
)
}
),
e = Table.ExpandTableColumn(d, "G", List.Skip(Table.ColumnNames(c))),
Sol = Table.ReplaceValue(e, 0, null, Replacer.ReplaceValue, Table.ColumnNames(c))
in
SolPower Query solution 10 for Sum and Sort Student Marks, proposed by Eric Laforce:
let
fxSource = (n) => Excel.CurrentWorkbook(){[Name = n]}[Content],
Combine = fxSource("tData244_1") & fxSource("tData244_2"),
Group = Table.Group(
Combine,
{"Student"},
List.Transform(
List.Sort(List.Skip(Table.ColumnNames(Combine))),
(c) => {c, each List.Sum(Table.Column(_, c))}
)
)
in
GroupPower Query solution 11 for Sum and Sort Student Marks, proposed by Eric Laforce:
let
fxSource = (n) => Excel.CurrentWorkbook(){[Name = n]}[Content],
Combine = fxSource("tData244_1") & fxSource("tData244_2"),
Unpivot = Table.UnpivotOtherColumns(Combine, {"Student"}, "A", "V"),
Pivot = Table.Pivot(Unpivot, List.Sort(List.Distinct(Unpivot[A])), "A", "V", List.Sum)
in
PivotPower Query solution 12 for Sum and Sort Student Marks, proposed by Seokho MOON:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
tbl = Source("Table1") & Source("Table2"),
Res = Table.Group(
tbl,
{"Student"},
List.Transform(
List.Sort(List.RemoveItems(Table.ColumnNames(tbl), {"Student"})),
(x) => {x, each List.Sum(Record.Field(_, x))}
)
)
in
ResPower Query solution 13 for Sum and Sort Student Marks, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
S2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
A = Table.Combine({S1, S2}),
B = Table.UnpivotOtherColumns(A, {"Student"}, "Attribute", "Value"),
C = Table.Pivot(B, List.Sort(List.Distinct(B[Attribute])), "Attribute", "Value", List.Sum)
in
CPower Query solution 14 for Sum and Sort Student Marks, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Append = Table.Combine({Source, Table2}),
Unpivot = Table.UnpivotOtherColumns(Append, {"Student"}, "Attribute", "Value"),
Pivot = Table.Pivot(Unpivot, List.Distinct(Unpivot[Attribute]), "Attribute", "Value", List.Sum),
Sort = Table.SelectColumns(Pivot, {"Student"} & List.Sort(List.Skip(Table.ColumnNames(Pivot)), 0))
in
SortPower Query solution 15 for Sum and Sort Student Marks, proposed by Alexandre Garcia:
let
A = each Excel.CurrentWorkbook(){[Name=_]}[Content],
B = A("Table1") & A("Table2"),
C = Table.ColumnNames(B),
D = (x) => List.Accumulate(List.Sort(List.Skip(C)), hashtag#table({C{0}}, {{Table.FirstValue(x)}}), (s,c)=> Table.AddColumn(s, c, each List.Sum(Table.Column(x,c)))),
E = Table.Combine(Table.Group(B, C{0}, {"x", D})[x])
in E
Power Query solution 16 for Sum and Sort Student Marks, proposed by Khanh Lam chi:
let
tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
group = Table.Combine({tbl1,tbl2}),
res = Table.Combine(Table.Group(group, {"Student"}, {{"G", (g)=> let colname = Table.ColumnNames(g) in Table.SelectColumns(hashtag#table(colname, {List.Transform(Table.ToColumns(g) , each try List.Sum(_) otherwise _{0})}),{colname{0}}&List.Sort( List.Skip(colname))) }})[G])
in
res
Power Query solution 17 for Sum and Sort Student Marks, proposed by Ramon Barrull:
let
Origen = Excel.CurrentWorkbook(){[Name = "t_1"]}[Content]
& Excel.CurrentWorkbook(){[Name = "t_2"]}[Content],
anulDinamitz = Table.UnpivotOtherColumns(Origen, {"Student"}, "Asignatura", "Valor"),
dinam = Table.Pivot(
anulDinamitz,
List.Distinct(anulDinamitz[Asignatura]),
"Asignatura",
"Valor",
List.Sum
),
result = Table.TransformColumnTypes(
dinam,
List.Transform(List.Skip(Table.ColumnNames(dinam)), each {_, type number})
& {{"Student", type text}}
)
in
resultPower Query solution 18 for Sum and Sort Student Marks, proposed by Fernando Brenha:
let
Origem = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content],
combine = Table.Combine({Origem, Tabela2}),
unpivoted = Table.UnpivotOtherColumns(combine, {"Student"}, "Atributo", "Valor"),
group = Table.Sort(
Table.Group(unpivoted, {"Student", "Atributo"}, {{"Sum", each List.Sum([Valor]), type number}}),
{"Atributo", "Student"}
),
pivot = Table.Pivot(group, List.Distinct(group[Atributo]), "Atributo", "Sum", List.Sum)
in
pivotSolving the challenge of Sum and Sort Student Marks with Excel
Excel solution 1 for Sum and Sort Student Marks, proposed by Bo Rydobon 🇹🇭:
=LET(
u,
LAMBDA(
a,
LET(
d,
DROP(
a,
1,
1
),
L,
LAMBDA(
x,
i,
j,
TOCOL(
IFS(
d,
TAKE(
DROP(
x,
i,
j
),
j,
i
)
),
3
)
),
HSTACK(
L(
a,
1,
),
L(
a,
,
1
),
L(
d,
,
)
)
)
),
v,
VSTACK(
u(
A1:F6
),
u(
A9:F12
)
),
PIVOTBY(
TAKE(
v,
,
1
),
INDEX(
v,
,
2
),
DROP(
v,
,
2
),
SUM,
,
0,
,
0
)
)Excel solution 2 for Sum and Sort Student Marks, proposed by 🇰🇷 Taeyong Shin:
=LET(
d,
B2:F6,
e,
B10:F12,
F,
LAMBDA(
x,
y,
TOCOL(
VSTACK(
IFS(
d,
x
),
IFS(
e,
y
)
),
2
)
),
PIVOTBY(
F(
A2:A6,
A10:A12
),
F(
B1:F1,
B9:F9
),
F(
d,
e
),
SUM,
,
0,
,
0
)
)Excel solution 3 for Sum and Sort Student Marks, proposed by Julian Poeltl:
=LET(
S,
A2:A6,
ST,
A10:A12,
J,
B1:F1,
JT,
B9:F9,
O,
B2:F6,
T,
B10:F12,
HJ,
UNIQUE(
SORT(
HSTACK(
J,
JT
),
,
,
1
),
1
),
HS,
UNIQUE(
SORT(
VSTACK(
S,
ST
)
)
),
R,
VSTACK(
HSTACK(
"Student",
HJ
),
HSTACK(
HS,
MAP(
HJ&HS,
LAMBDA(
A,
SUM(
IFERROR(
FILTER(
TOCOL(
HSTACK(
O,
T
),
2
),
TOCOL(
HSTACK(
J&S,
JT&ST
),
2
)=A
),
0
)
)
)
)
)
),
IF(
R=0,
"",
R
)
)Excel solution 4 for Sum and Sort Student Marks, proposed by Oscar Mendez Roca Farell:
=LET(
V,
VSTACK,
O,
TOCOL,
a,
A1:F6,
b,
A9:F12,
F,
LAMBDA(
i,
j,
k,
O(
IFS(
DROP(
i,
1,
1
),
TAKE(
DROP(
i,
j,
k
),
k,
j
)
),
2
)
),
PIVOTBY(
V(
F(
a,
1,
),
F(
b,
1,
)
),
V(
F(
a,
,
1
),
F&(
b,
,
1
)
),
V(
O(
B2:F6,
1
),
O(
B10:F12,
1
)
),
SUM,
,
0,
,
0
)
)Excel solution 5 for Sum and Sort Student Marks, proposed by Duy Tùng:
=LET(
a,
B2:F6,
b,
B10:F12,
f,
LAMBDA(
x,
y,
TOCOL(
VSTACK(
IFS(
a,
x
),
IFS(
b,
y
)
),
2
)
),
k,
PIVOTBY(
f(
A2:A6,
A10:A12
),
f(
B1:F1,
B9:F9
),
f(
a,
b
),
SUM,
,
0,
,
0
),
IF(
TAKE(
k,
1
)&TAKE(
k,
,
1
)="",
A1,
k
)
)Excel solution 6 for Sum and Sort Student Marks, proposed by Sunny Baggu:
=LET(
_s,
UNIQUE(
VSTACK(
A2:A6,
A10:A12
)
),
_sub,
SORT(
UNIQUE(
HSTACK(
B1:F1,
B9:F9
),
1
),
,
,
1
),
_v,
WRAPROWS(
MAP(
TOCOL(
_s & _sub
),
LAMBDA(a,
SUM((TOCOL(
A2:A6 & B1:F1
) = a) * TOCOL(
B2:F6
)) +
SUM((TOCOL(
A10:A12 & B9:F9
) = a) * TOCOL(
B10:F12
))
)
),
COLUMNS(
_sub
)
),
VSTACK(
HSTACK(
A1,
_sub
),
HSTACK(
_s,
_v
)
)
)Excel solution 7 for Sum and Sort Student Marks, proposed by Md. Zohurul Islam:
=LET(
f,
LAMBDA(
x,
y,
z,
HSTACK(
TOCOL(
IFNA(
x,
y
)
),
TOCOL(
IFNA(
y,
x
)
),
TOCOL(
z
)
)
),
p,
f(
A2:A6,
B1:F1,
B2:F6
),
q,
f(
A10:A12,
B9:F9,
B10:F12
),
s,
VSTACK(
p,
q
),
u,
PIVOTBY(
TAKE(
s,
,
1
),
CHOOSECOLS(
s,
2
),
TAKE(
s,
,
-1
),
SUM,
0,
0,
,
0
),
u
)Excel solution 8 for Sum and Sort Student Marks, proposed by Md. Zohurul Islam:
=LET(
a,
A2:A6,
b,
B1:F1,
d,
B2:F6,
p,
A10:A12,
q,
B9:F9,
s,
B10:F12,
e,
TOCOL(
IFNA(
a,
b
)
),
f,
TOCOL(
IFNA(
b,
a
)
),
g,
TOCOL(
d
),
h,
HSTACK(
e,
f,
g
),
u,
TOCOL(
IFNA(
p,
q
)
),
v,
TOCOL(
IFNA(
q,
p
)
),
w,
TOCOL(
s
),
z,
HSTACK(
u,
v,
w
),
rng,
VSTACK(
h,
z
),
j,
PIVOTBY(
TAKE(
rng,
,
1
),
CHOOSECOLS(
rng,
2
),
TAKE(
rng,
,
-1
),
SUM,
0,
0,
,
0
),
j
)Excel solution 9 for Sum and Sort Student Marks, proposed by Hamidi Hamid:
=LET(
f,
LAMBDA(
a,
b,
c,
DROP(
TEXTSPLIT(
CONCAT(
"/"&a&"-"&b&"-"&c
),
"-",
"/"
),
1
)
),
x,
f(
A2:A6,
B1:F1,
B2:F6
),
y,
f(
A10:A12,
B9:F9,
B10:F12
),
g,
VSTACK(
x,
y
),
IFERROR(
PIVOTBY(
TAKE(
g,
,
1
),
CHOOSECOLS(
g,
2
),
TAKE(
g,
,
-1
)*1,
SUM,
0,
0,
,
0
),
""
)
)Excel solution 10 for Sum and Sort Student Marks, proposed by Asheesh Pahwa:
=LET(
s,
VSTACK(
A2:A6,
A10:A12
),
sub,
HSTACK(
B1:F1,
B9:F9
),
c,
A2:A6&B1:F1&"-"&B2:F6,
_c,
A10:A12&B9:F9&"-"&B10:F12,
t,
TOCOL(
VSTACK(
c,
_c
)
),
ta,
IFERROR(
--TEXTAFTER(
t,
"-"
),
""
),
tb,
TEXTBEFORE(
t,
"-"
),
u,
UNIQUE(
s
),
_u,
SORT(
UNIQUE(
sub,
1
),
,
1,
1
),
cn,
u&_u,
r,
MAP(
cn,
LAMBDA(
x,
SUM(
FILTER(
ta,
tb=x,
0
)
)
)
),
IF(
r,
r,
""
)
)Excel solution 11 for Sum and Sort Student Marks, proposed by ferhat CK:
=LET(
d,
UNIQUE(
HSTACK(
B1:F1,
B9:F9
),
TRUE
),
st,
UNIQUE(
VSTACK(
A2:A6,
A10:A12
)
),
e,
MAKEARRAY(
6,
6,
LAMBDA(
x,
y,
IFNA(
XLOOKUP(
INDEX(
st,
x
),
A2:A6,
XLOOKUP(
INDEX(
d,
y
),
B1:F1,
B2:F6
)
),
0
)+IFNA(
XLOOKUP(
INDEX(
st,
x
),
A10:A12,
XLOOKUP(
INDEX(
d,
y
),
B9:F9,
B10:F12
)
),
0
)
)
),
HSTACK(
VSTACK(
A1,
st
),
SORT(
VSTACK(
d,
e
),
1,
1,
TRUE
)
)
)Excel solution 12 for Sum and Sort Student Marks, proposed by Jaroslaw Kujawa:
=LET(
st_1;
A2:A6;
sub_1;
SORT(
B1:F1
);
m_1;
IF(
B2:F6<>"";
1*B2:F6;
0
);
st_2;
A9:A11;
sub_2;
B8:F8;
m_2;
IF(
""<>B9:F11;
B9:F11;
0
);
st;
UNIQUE(
VSTACK(
st_1;
st_2
)
);
wsio;
TEXTSPLIT(
TEXTJOIN(
"|";
;
VSTACK(
TOCOL(
st_1&";"&sub_1&";"&m_1
);
TOCOL(
st_2&";"&sub_2&";"&m_2
)
)
);
";";
"|"
);
pv;
PIVOTBY(
TAKE(
wsio;
;
1
);
CHOOSECOLS(
wsio;
2
);
1*TAKE(
wsio;
;
-1
);
SUM;
;
0;
;
0
);
HSTACK(
IF(
TAKE(
pv;
;
1
)<>"";
TAKE(
pv;
;
1
);
"Student"
);
TAKE(
IF(
pv<>0;
pv;
""
);
;
-5
)
)
)Excel solution 13 for Sum and Sort Student Marks, proposed by Jaroslaw Kujawa:
=SUM(IF(($H2=$A$2:$A$6)*(I$1=$B$1:$F$1);
$B$2:$F$6))+SUM(IF(($H2=$A$10:$A$12)*(I$11=$B$9:$F$9);
$B$10:$F$12))Excel solution 14 for Sum and Sort Student Marks, proposed by Ankur Sharma:
=LET(TS,
TEXTSPLIT,
TJ,
TEXTJOIN,
CH,
CHOOSECOLS,
r_1,
TS(TJ(", ",
,
A2:A6 & " - " & (B1:F1 & " - " & B2:F6)),
" - ",
", "),
r_2,
TS(TJ(", ",
,
A10:A12 & " - " & (B9:F9 & " - " & B10:F12)),
" - ",
", "),
c,
VSTACK(
r_1,
r_2
),
a,
PIVOTBY(
CH(
c,
1
),
CH(
c,
2
),
--CH(
c,
3
),
SUM,
,
0,
,
0,
1
),
IFERROR(
a,
""
))Excel solution 15 for Sum and Sort Student Marks, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(a,
TOROW(
SORT(
UNIQUE(
VSTACK(
TOCOL(
B1:F1
),
TOCOL(
B9:F9
)
)
)
)
),
b,
SORT(
UNIQUE(
VSTACK(
TOCOL(
A2:A6
),
TOCOL(
A10:A12
)
)
)
),
VSTACK(HSTACK(
"Student",
a
),
HSTACK(b,
TEXTSPLIT(TEXTJOIN(,
FALSE,
BYROW(b,
LAMBDA(y,
TEXTJOIN(",",
,
BYCOL(DROP(
HSTACK(
"Student",
a
),
,
1
),
LAMBDA(x,
SUM(IF(((y=A2:A6)*(x=B1:F1))>0,
B2:F6,
0))+SUM(IF(((y=A10:A12)*(x=B9:F9))>0,
B10:F12,
0)))))&"*"))),
",",
"*",
TRUE))))Excel solution 16 for Sum and Sort Student Marks, proposed by Imam Hambali:
=LET(
u, B2:F6,
d, B10:F12,
l, LAMBDA(x,y, TOCOL(IF(x>0,y,NA()),3)),
v, VSTACK(HSTACK(l(u,A2:A6), l(u,B1:F1), l(u,u)),
HSTACK(l(d,A10:A12), l(d,B9:F9), l(d,d))),
cc, CHOOSECOLS,
p, PIVOTBY(cc(v,1), cc(v,2),cc(v,3),SUM,0,0,,0),
HSTACK(IF(cc(p,1)="","Student",cc(p,1)), DROP(p,,1))
)Excel solution 17 for Sum and Sort Student Marks, proposed by Peter Bartholomew:
= LAMBDA(rowHdr, colHdr, values,
HSTACK(
TOCOL(IF(values, rowHdr, NA()), 3),
TOCOL(IF(values, colHdr, NA()), 3),
TOCOL(values, 3))
)
the worksheet formula becomes
= LET(
normalised1, UNPIVOTλ(students1, subjects1, marks1),
normalised2, UNPIVOTλ(students2, subjects2, marks2),
normalised, VSTACK(normalised1, normalised2),
student, CHOOSECOLS(normalised, 1),
subject, CHOOSECOLS(normalised, 2),
marks, CHOOSECOLS(normalised, 3),
PIVOTBY(student, subject, marks, SUM, , 0, ,0)
)Excel solution 18 for Sum and Sort Student Marks, proposed by Edwin Tisnado:
=LET(
a,
B1:F1,
b,
B9:F9,
u,
A2:A6,
v,
A10:A12,
m,
VSTACK(
u&a&B2:F6,
v&b&B10:F12
),
l,
LAMBDA(
x,
y,
SORT(
UNIQUE(
TOCOL(
VSTACK(
x,
y
)
)
)
)
),
VSTACK(
HSTACK(
A1,
TOROW(
l(
a,
b
)
)
),
REDUCE(
l(
u,
v
),
l(
a,
b
),
LAMBDA(
i,
j,
HSTACK(
i,
MAP(
l(
u,
v
),
LAMBDA(
t,
IFERROR(
SUM(
--TEXTAFTER(
m,
t&j,
,
,
,
0
)
),
""
)
)
)
)
)
)
)
)Excel solution 19 for Sum and Sort Student Marks, proposed by red craven:
=LET(
V,
VSTACK,
a,
A9:F12,
b,
A1:F6,
f,
LAMBDA(
x,
y,
z,
TOCOL(
IFS(
DROP(
x,
1,
1
)>0,
DROP(
x,
y,
z
)
),
3
)
),
PIVOTBY(
V(
f(
b,
1,
-5
),
f(
a,
1,
-5
)
),
V(
f(
b,
-5,
1
),
f(
a,
-3,
1
)
),
V(
f(
b,
1,
1
),
f(
a,
1,
1
)
),
SUM,
,
0,
,
0
)
)Excel solution 20 for Sum and Sort Student Marks, proposed by red craven:
=LET(
a,
B2:F6,
b,
B10:F12,
f,
LAMBDA(
x,
y,
TOCOL(
IFS(
x>0,
y
),
3
)
),
c,
VSTACK(
HSTACK(
f(
a,
A2:A6
),
f(
a,
B1:F1
),
f(
a,
a
)
),
HSTACK(
f(
b,
A10:A12
),
f(
b,
B9:F9
),
f(
b,
b
)
)
),
PIVOTBY(
TAKE(
c,
,
1
),
INDEX(
c,
,
2
),
TAKE(
c,
,
-1
),
SUM,
0,
0,
,
0
)
)Solving the challenge of Sum and Sort Student Marks with Python
Python solution 1 for Sum and Sort Student Marks, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_244.xlsx"
input1 = pd.read_excel(path, usecols="A:F", nrows=6)
inpu&t2 = pd.read_excel(path, usecols="A:F", skiprows=8, nrows=4)
test = pd.read_excel(path, usecols="I:O", nrows=7).rename(columns=lambda x: x.split('.')[0])
melted = pd.concat([input1, input2]).melt(id_vars='Student', var_name="Subject", value_name="Score").dropna(subset=['Score'])
pivoted = melted.pivot_table(index='Student', columns='Subject', values='Score', aggfunc='sum').reset_index()
pivoted.columns.name = None
print(all(pivoted == test)) # True
Python solution 2 for Sum and Sort Student Marks, proposed by Luan Rodrigues:
import pandas as pd
file = "PQ_Challenge_244.xlsx"
df1 = pd.read_excel(file,usecols="A:F", nrows=6)
df2 = pd.read_excel(file,usecols="A:F",skiprows=8, nrows=4)
df = pd.concat([df1,df2])
df = df.melt(id_vars=['Student'],var_name='Atributo',value_name='Valor')
grp = df.groupby(['Student','Atributo'], as_index=False)['Valor'].sum()
grp = grp.pivot_table(index='Student', columns='Atributo',values='Valor').reset_index()
print(grp)
Python solution 3 for Sum and Sort Student Marks, proposed by Abdallah Ally:
import pandas as pd
file_path = 'PQ_Challenge_244.xlsx'
df1 = pd.read_excel(file_path, usecols='A:F', nrows=5)
df2 = pd.read_excel(file_path, usecols='A:F', skiprows=8)
# Perform data manipulation
df = pd.concat([df1, df2])
df = df.groupby('Student').agg(lambda x: x.sum(min_count=1)).reset_index()
df = df[[df.columns[0]] + sorted(df.columns[1:])]
df[df.columns[1:]] = df[df.columns[1:]].map(lambda x: '' if pd.isna(x) else int(x))
df
Solving the challenge of Sum and Sort Student Marks with Python in Excel
Python in Excel solution 1 for Sum and Sort Student Marks, proposed by Bo Rydobon 🇹🇭:
pd.concat([xl("A1:F6", headers=True), xl("A9:F12", headers=True)]).groupby('Student').sum().sort_index(axis=1).reset_index().replace(0,'')Python in Excel solution 2 for Sum and Sort Student Marks, proposed by Alejandro Campos:
df_t1 = xl("A1:F6", headers=True).fillna(np.NaN)
df_t2 = xl("A9:F12", headers=True).fillna(np.NaN)
merged_df = pd.merge(df_t1, df_t2, on='Student', how='outer', suffixes=('_T1', '_T2'))
for column in ['English', 'Science', 'Maths', 'Arts', 'Physics', 'Computers']:
if f'{column}_T1' in merged_df.columns and f'{column}_T2' in merged_df.columns:
merged_df[column] = merged_df[[f'{column}_T1', f'{column}_T2']].sum(axis=1, skipna=True)
merged_df.drop(columns=[f'{column}_T1', f'{column}_T2'], inplace=True)
elif f'{column}_T1' in merged_df.columns:
merged_df[column] = merged_df[f'{column}_T1']
merged_df.drop(columns=[f'{column}_T1'], inplace=True)
elif f'{column}_T2' in merged_df.columns:
merged_df[column] = merged_df[f'{column}_T2']
merged_df.drop(columns=[f'{column}_T2'], inplace=True)
merged_df.replace(0, np.nan, inplace=True)
merged_df.sort_values(by='Student', inplace=True)
merged_df = merged_df[['Student'] + sorted(
[col for col in merged_df.columns if col != 'Student'])].fillna(' ')
merged_df
Python in Excel solution 3 for Sum and Sort Student Marks, proposed by Aditya Kumar Darak 🇮🇳:
data1 = xl("A1:F6", headers=True)
data2 = xl("A9:F12", headers=True)
combine = pd.concat([data1, data2], ignore_index=True)
group = (
combine.groupby("Student", as_index=False)
.agg(lambda x: x.sum(min_count=1))
.fillna("")
)
cols = ["Student"] + sorted(group.columns[1:])
result = group[cols]
result
Solving the challenge of Sum and Sort Student Marks with R
R solution 1 for Sum and Sort Student Marks, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_244.xlsx"
input1 = read_excel(path, range = "A1:F6")
input2 = read_excel(path, range = "A9:F12")
test = read_excel(path, range = "I1:O7")
I3 = bind_rows(input1, input2) %>%
pivot_longer(cols = -c(Student), names_to = "Subject", values_to = "Score") %>%
na.omit() %>%
pivot_wider(names_from = "Subject", values_from = "Score", values_fn = list(Score = sum)) %>%
select(Student, sort(colnames(.), decreasing = FALSE))
all.equal(I3, test)
# [1] TRUE
&