Pivot the given data by summing up the values. Insert a Total line also.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 700
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Pivot with Total Line with Power Query
Power Query solution 1 for Pivot with Total Line, proposed by Kris Jaganah:
let
A = Text.Combine(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Data], ", "),
B = Text.ToList(Text.Select(A, {"A" .. "Z"})),
C = List.Transform(Text.Split(Text.Remove(A, {"A" .. "Z", "-", " "}), ","), Number.From),
D = List.Transform(
List.Sort(List.Distinct(B)),
each {_, List.Sum(List.Zip(List.Select(List.Zip({B, C}), (v) => v{0} = _)){1})}
),
E = Table.FromRows(D & {{"TOTAL", List.Sum(C)}}, {"Alphabet", "Value"})
in
EPower Query solution 2 for Pivot with Total Line, proposed by Kris Jaganah:
let
A = {"Alphabet","Value"},
B = Table.FromRows(List.Split( List.Select( Text.SplitAny (Text.Combine(
Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Data],", "),"-, "),
(w)=> w <> ""),2) ,A),
C = Table.TransformColumnTypes(B,{A{1}, Int64.Type}),
D = Table.Group(C, A{0}, {A{1}, each List.Sum([Value])}),
E = Table.Sort(D,A{0}),
F = E & hashtag#table( A , {{"TOTAL",List.Sum(C[Value])}})
in
F
Power Query solution 3 for Pivot with Total Line, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Comb = List.Combine(Table.AddColumn(Origen, "A", each Text.Split([Data], ", "))[A]),
Sel = List.Select(
List.Transform({"A" .. "Z"}, each List.Select(Comb, (x) => Text.StartsWith(x, _))),
each not List.IsEmpty(_)
),
Sol = Table.FromRows(
let
m = List.Transform(
Sel,
each {
Text.Start(_{0}, 1),
List.Sum(
List.Transform(
_,
(x) =>
let
a = Text.Split(x, "-"),
b = Number.From(a{1})
in
b
)
)
}
),
n = m & {{"TOTAL", List.Sum(List.Transform(m, each _{1}))}}
in
n,
{"Alphabets", "Value"}
)
in
SolPower Query solution 4 for Pivot with Total Line, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Split = Text.Split(Text.Combine(Source[Data], ", "), ", "),
FromList = Table.FromList(Split, each Text.Split(_, "-"), {"Alphabet", "Value"}),
Group = Table.Group(
FromList,
"Alphabet",
{"Value", each List.Sum(List.Transform([Value], Number.From))},
1,
(x, y) => Value.Compare(x, y)
),
Result = Table.InsertRows(
Group,
Table.RowCount(Group),
{[Alphabet = "TOTAL", Value = List.Sum(Group[Value])]}
)
in
ResultPower Query solution 5 for Pivot with Total Line, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.ExpandListColumn(Table.TransformColumns(S, {"Data", each Text.Split(_, ", ")}), "Data"),
b = Table.TransformColumnTypes(
Table.SplitColumn(a, "Data", Splitter.SplitTextByDelimiter("-"), {"Alphabet", "V"}),
{"V", Int64.Type}
),
c = Table.Sort(Table.Group(b, "Alphabet", {"Value", each List.Sum([V])}), {"Alphabet", 0}),
Sol = Table.InsertRows(c, Table.RowCount(c), {[Alphabet = "TOTAL", Value = List.Sum(c[Value])]})
in
SolPower Query solution 6 for Pivot with Total Line, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rows = List.TransformMany(Source[Data], each Text.Split(_, ", "), (x, y) => Text.Split(y, "-")),
Tbl = Table.FromList(Rows, each {_{0}, Number.From(_{1})}, {"Alphabet", "Value"}),
Group = Table.Sort(Table.Group(Tbl, "Alphabet", {"Value", each List.Sum([Value])}), "Alphabet"),
Res = Group & Table.FromRows({{"Total", List.Sum(Group[Value])}}, Value.Type(Group))
in
ResPower Query solution 7 for Pivot with Total Line, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Sort(
Table.Group(
Table.FromRows(
List.Transform(
List.Combine(List.Transform(Source[Data], (f) => Text.Split(f, ", "))),
(x) => Text.Split(x, "-")
),
{"Alphabets", "N"}
),
"Alphabets",
{"Value", each List.Sum(List.Transform([N], Number.From))}
),
"Alphabets"
),
Result = Group & Table.FromRecords({[Alphabets = "Total", Value = List.Sum(Group[Value])]})
in
ResultPower Query solution 8 for Pivot with Total Line, proposed by Antriksh Sharma:
let
Source = Table,
Split = List.TransformMany(
Source[Data],
(x) => List.Transform(Text.Split(x, ", "), each Text.Split(_, "-")),
(x, y) => y
),
Combine = Table.TransformColumnTypes(
Table.FromRows(Split, type table [Alphabet = text, Value = number]),
{"Value", type number}
),
Group = Table.Group(Combine, "Alphabet", {"Value", each List.Sum([Value]), type number}),
TotalRow = Table.FromRecords(
{[Alphabet = "Total", Value = List.Sum(Group[Value])]},
type table [Alphabet = text, Value = number]
),
Append = Group & TotalRow
in
AppendPower Query solution 9 for Pivot with Total Line, proposed by Antriksh Sharma:
let
Source = Table,
Split = Table.FromList(
Source[Data],
(x) => List.Transform(Text.Split(x, ", "), (y) => Text.Split(y, "-"))
),
Transform = Table.TransformColumns(
Split,
{},
each Table.FromRows({{_{0}, Number.From(_{1})}}, type table [Alphabet = text, Value = number])
),
CombineCols = Table.CombineColumns(
Transform,
Table.ColumnNames(Transform),
each Table.Combine(_),
"Combine"
),
CombineTables = Table.Combine(CombineCols[Combine]),
Group = Table.Group(CombineTables, "Alphabet", {"Value", each List.Sum([Value]), type number}),
TotalRow = Table.FromRecords(
{[Alphabet = "Total", Value = List.Sum(Group[Value])]},
type table [Alphabet = text, Value = number]
),
Append = Group & TotalRow
in
AppendPower Query solution 10 for Pivot with Total Line, proposed by Mihai Radu O:
let
Source = [
a = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Data],
b = Text.Combine(a, ", "),
fct = (txt as text, chars as list, del as text) =>
let
f1 = Text.Split(Text.Select(txt, chars & {del}), del)
in
List.Transform(f1, (x) => try Number.From(x) otherwise x),
alf = fct(b, {"A" .. "Z"}, ","),
nr = fct(b, {"0" .. "9"}, ","),
total = List.Sum(nr),
alf1 = List.Sort(List.Distinct(alf)),
alfabet = alf1 & {"Total"},
nr1 = List.Transform(
alf1,
(x) => List.Sum(List.Transform(List.Zip({alf, nr}), (z) => if z{0} = x then z{1} else null))
),
valoare = nr1 & {total},
tbl = Table.FromColumns({alfabet, valoare}, {"Alphabet", "Value"})
][tbl]
in
SourcePower Query solution 11 for Pivot with Total Line, proposed by Maciej Kopczyński:
let
A = Excel.CurrentWorkbook(){[Name="tblStart"]}[Content],
B = List.Combine(Table.TransformColumns(A, {{"Data", each Text.Split(_, ", ")}})[Data]),
C = Table.FromList(B, Splitter.SplitTextByDelimiter("-"), {"Alphabet", "Value"}),
total = List.Sum(List.Transform(C[Value], each Number.From(_))),
D = Table.Group(C, {"Alphabet"}, {{"Value", each List.Sum(List.Transform([Value], each Number.From(_)))}}) & hashtag#table({"Alphabet", "Value"}, {{"Total", total}})
in
D
Power Query solution 12 for Pivot with Total Line, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
T = Table.FromRows(List.TransformMany(Source[Data],
each Text.Split(_, ", "),
(x, y) => let p = Text.Split(y, "-") in {p{0}, Number.From(p{1})}
), {"Alphabets","Value"}),
Grouped = Table.Group(T, {"Alphabets"},
{{"Value", each List.Sum([Value]), Int64.Type}})
in
Grouped & hashtag#table({"Alphabets", "Value"}, {{"TOTAL", List.Sum( Grouped[Value])}})
Power Query solution 13 for Pivot with Total Line, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tbl = Table.SplitColumn(
Table.FromColumns({List.Combine(List.Transform(Source[Data], each Text.Split(_, ", ")))}, {"A"}),
"A",
Splitter.SplitTextByDelimiter("-"),
{"Alphabet", "V"}
),
Grp = Table.Sort(
Table.Group(
Tbl,
"Alphabet",
{{"Value", each List.Sum(List.Transform(_[V], (x) => Number.From(x)))}}
),
"Alphabet"
),
Res = Table.InsertRows(
Grp,
Table.RowCount(Grp),
{[Alphabet = "TOTAL", Value = List.Sum(Grp[Value])]}
)
in
ResPower Query solution 14 for Pivot with Total Line, proposed by Anjan Kumar Bose:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Data", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(
#"Changed Type",
"Data",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
{"Data.1", "Data.2", "Data.3"}
),
#"Changed Type1" = Table.TransformColumnTypes(
#"Split Column by Delimiter",
{{"Data.1", type text}, {"Data.2", type text}, {"Data.3", type text}}
),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1", {"Data.2", "Data.3"}),
#"Split Column by Delimiter1" = Table.SplitColumn(
#"Removed Columns",
"Data.1",
Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),
{"Data.1.1", "Data.1.2"}
),
#"Changed Type2" = Table.TransformColumnTypes(
#"Split Column by Delimiter1",
{{"Data.1.1", type text}, {"Data.1.2", Int64.Type}}
),
#"Renamed Columns" = Table.RenameColumns(
#"Changed Type2",
{{"Data.1.1", "Data"}, {"Data.1.2", "Value"}}
)
in
#"Renamed Columns"Power Query solution 15 for Pivot with Total Line, proposed by Anjan Kumar Bose:
let
Source = Table.Combine({Table1, Table2, Table3}),
#"Grouped Rows" = Table.Group(Source, {"Data"}, {{"Value", each List.Sum([Value]), type nullable number}}),
TotalRow = hashtag#table({"Data", "Value"}, {{"total", List.Sum(#"Grouped Rows"[Value])}}),
FinalTable = Table.Combine({#"Grouped Rows", TotalRow})
in
FinalTable
Power Query solution 16 for Pivot with Total Line, proposed by Ezel K.:
let
Kaynak = Excel.CurrentWorkbook(){[Name="Tablo1"]}[Content],
#"Değiştirilen Tür" = Table.TransformColumnTypes(Kaynak,{{"Data", type text}}),
Sütunböl1 = Table.SplitColumn(#"Değiştirilen Tür", "Data", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Data.1", "Data.2", "Data.3"}),
#"Değiştirilen Tür1" = Table.TransformColumnTypes(Sütunböl1,{{"Data.1", type text}, {"Data.2", type text}, {"Data.3", type text}}),
#"Özet Sütunlar Çözüldü" = Table.UnpivotOtherColumns(#"Değiştirilen Tür1", {}, "Öznitelik", "Değer"),
#"Kaldırılan Sütunlar" = Table.RemoveColumns(#"Özet Sütunlar Çözüldü",{"Öznitelik"}),
Sütunböl = Table.SplitColumn(#"Kaldırılan Sütunlar", "Değer", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Değer.1", "Değer.2"}),
#"Değiştirilen Tür2" = Table.TransformColumnTypes(Sütunböl,{{"Değer.1", type text}, {"Değer.2", Int64.Type}}),
Kırp = Table.TransformColumns(#"Değiştirilen Tür2",{{"Değer.1", Text.Trim, type text}}),
İsimlendir = Table.RenameColumns(Kırp,{{"Değer.1", "Harfler"}, {"Değer.2", "Değer"}}),
#"Gruplanan Satırlar" = Table.Group(İsimlendir, {"Harfler"}, {{"Sayı", each List.Sum([Değer]), type nullable number}})
in
#"Gruplanan Satırlar"
Show translation
Show translation of this commentPower Query solution 17 for Pivot with Total Line, proposed by Oleksandr Mynka:
let
// FUNCTIONS
f = (lst, x)=> [
a = List.Alternate(lst,1,1,x),
b = if (x = 1) then a else List.Transform(a,Number.From)
][b] ,
// TRANSFORMATION STEPS
src = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content],
spl = Table.TransformColumns(src,{"Data", Splitter.SplitTextByAnyDelimiter({", ","-"})}),
cmb = List.Combine(spl[Data]),
nms = {"Alphabet", "Value"},
tbl = Table.FromColumns({f(cmb,1),f(cmb,0)},nms),
gr = Table.Group(tbl,nms{0}, {nms{1}, each List.Sum(_[Value])}),
sort = Table.Sort(gr,{nms{0},Order.Ascending}),
total = hashtag#table(nms, {{"TOTAL", List.Sum(gr[Value])}}),
res = sort & total
in
res
Solving the challenge of Pivot with Total Line with Excel
Excel solution 1 for Pivot with Total Line, proposed by Bo Rydobon 🇹🇭:
=LET(
x,
TEXTSPLIT(
ARRAYTOTEXT(
A3:A6
),
"-",
", "
),
GROUPBY(
TAKE(
x,
,
1
),
--DROP(
x,
,
1
),
SUM
)
)Excel solution 2 for Pivot with Total Line, proposed by Rick Rothstein:
=LET(
t,
SORT(
TEXTSPLIT(
TEXTJOIN(
", ",
,
A3:A6
),
"-",
", "
)
),
k,
TAKE(
t,
,
1
),
u,
UNIQUE(
k
),
s,
MAP(
u,
LAMBDA(
x,
SUM(
FILTER(
0+TAKE(
t,
,
-1
),
k=x
)
)
)
),
VSTACK(
HSTACK(
u,
s
),
HSTACK(
"Total",
SUM(
s
)
)
)
)Excel solution 3 for Pivot with Total Line, proposed by Kris Jaganah:
=LET(
a,
TEXTSPLIT(
CONCAT(
A3:A6&", "
),
"-",
", ",
1
),
b,
GROUPBY(
TAKE(
a,
,
1
),
--TAKE(
a,
,
-1
),
SUM
),
VSTACK(
{"Alphabet",
"Value"},
IF(
ISTEXT(
b
),
UPPER(
& b
),
b
)
)
)Excel solution 4 for Pivot with Total Line, proposed by Julian Poeltl:
=LET(
T,
TEXTSPLIT(
TEXTJOIN(
",",
,
A3:A6
),
,
","
),
A,
TRIM(
TEXTBEFORE(
T,
"-"
)
),
V,
--TEXTAFTER(
T,
"-"
),
VSTACK(
HSTACK(
"Alphabet",
"Value"
),
GROUPBY(
A,
V,
SUM
)
)
)Excel solution 5 for Pivot with Total Line, proposed by Alejandro Campos:
=LET(
dv, DROP(TEXTSPLIT(CONCAT(A3:A6&", "), "-", ", "),-1),
ta, TAKE(dv,,1),
o, SORT(UNIQUE(ta)),
v, MAP(o, LAMBDA(x, SUM(N(ta=x)*TAKE(dv,,-1)))),
VSTACK({"Alphabet","Value"}, HSTACK(o, v), HSTACK("TOTAL", SUM(v))))Excel solution 6 for Pivot with Total Line, proposed by Timothée BLIOT:
=LET(
A,
WRAPROWS(
REGEXEXTRACT(
ARRAYTOTEXT(
A3:A6
),
"[A-Z]|d+",
1
),
2
),
GROUPBY(
TAKE(
A,
,
1
),
--TAKE(
A,
,
-1
),
SUM
)
)Excel solution 7 for Pivot with Total Line, proposed by Hussein SATOUR:
=LET(a,TEXTSPLIT(ARRAYTOTEXT(A3:A6),"-",", "),GROUPBY(TAKE(a,,1),--TAKE(a,,-1),SUM))Excel solution 8 for Pivot with Total Line, proposed by Oscar Mendez Roca Farell:
=LET(
F,
LAMBDA(
i,
TOCOL(
REGEXEXTRACT(
CONCAT(
A3:A6
),
i,
1
)
)
),
GROUPBY(
F(
"[A-Z]"
),
--F(
"d+"
),
SUM
)
)Excel solution 9 for Pivot with Total Line, proposed by Duy Tùng:
=LET(
a,
TEXTSPLIT(
ARRAYTOTEXT(
A3:A6
),
"-",
", "
),
b,
GROUPBY(
TAKE(
a,
,
1
),
--DROP(
a,
,
1
),
SUM
),
IF(
b>"",
UPPER(
b
),
b
)
)Excel solution 10 for Pivot with Total Line, proposed by Sunny Baggu:
=LET(
_t,
SORT(
TEXTSPLIT(
ARRAYTOTEXT(
A3:A6
),
"-",
", "
)
),
_a,
TAKE(
_t,
,
1
),
_b,
TAKE(
_t,
,
-1
) + 0,
_u,
UNIQUE(
_a
),
_s,
MAP(_u,
LAMBDA(a,
SUM((_a = a) * _b))),
VSTACK(
HSTACK(
_u,
_s
),
HSTACK(
"TOTAL",
SUM(
_b
)
)
)
)Excel solution 11 for Pivot with Total Line, proposed by LEONARD OCHEA 🇷🇴:
=LET(d,TEXTSPLIT(CONCAT(A3:A6&", "),"-",", ",1),I,INDEX,GROUPBY(I(d,,1),--I(d,,2),SUM))Excel solution 12 for Pivot with Total Line, proposed by Abdallah Ally:
=LET(
a,
TEXTSPLIT(
TEXTJOIN(
", ",
,
A3:A6
),
"-",
", "
),
b,
TAKE,
VSTACK(
{"Alphabet",
"Value"},
GROUPBY(
b(
a,
,
1
),
--b(
a,
,
-1
),
SUM
)
)
)Excel solution 13 for Pivot with Total Line, proposed by Anshu Bantra:
=LET(
data_,
TEXTSPLIT(
TEXTJOIN(
", ",
,
A3:A6
),
,
", "
),
VSTACK(
{"Alphabet",
"Value"},
GROUPBY(
TEXTBEFORE(
data_,
"-"
),
--TEXTAFTER(
data_,
"-"
),
SUM,
0,
1
)
)
)Excel solution 14 for Pivot with Total Line, proposed by Md. Zohurul Islam:
=LET(z,A3:A6,hdr,HSTACK("Alphabet","Value"),
a,TEXTSPLIT(ARRAYTOTEXT(z),"-",", "),
b,TAKE(a,,1),c,--DROP(a,,1),d,SORT(UNIQUE(b)),
e,MAP(d,LAMBDA(x,SUM(IF(b=x,c,0)))),
f,VSTACK(hdr,HSTACK(d,e),HSTACK("Total",SUM(e))),
f)Excel solution 15 for Pivot with Total Line, proposed by Md. Zohurul Islam:
=LET(z,A3:A6,
a,TEXTSPLIT(ARRAYTOTEXT(z),"-",", "),
b,HSTACK("Alphabet","Value"),
c,VSTACK(b,GROUPBY(TAKE(a,,1),--DROP(a,,1),SUM,0,1)),
c)Excel solution 16 for Pivot with Total Line, proposed by Pieter de B.:
=LET(a,TEXTSPLIT(ARRAYTOTEXT(A3:A6),"-",", "), GROUPBY(TAKE(a,,1),--DROP(a,,1),SUM))Excel solution 17 for Pivot with Total Line, proposed by Hamidi Hamid:
=LET(
w,
DROP(
TEXTSPLIT(
CONCAT(
"/"&A3:A6
),
", ",
"/"
),
1
),
x,
TOCOL(
MAP(
w,
LAMBDA(
a,
TEXTBEFORE(
a,
"-"
)
)
)
),
y,
TOCOL(
MAP(
w,
LAMBDA(
a,
TEXTAFTER(
a,
"-"
)
)
)
)*1,
GROUPBY(
x,
y,
SUM,
,
1
)
)Excel solution 18 for Pivot with Total Line, proposed by Asheesh Pahwa:
=LET(t,TEXTJOIN("/",,A3:A6),_t,TEXTSPLIT(t,,{", ","-","/"}),
w,WRAPROWS(_t,2),a,TAKE(w,,1),u,SORT(UNIQUE(a)),m,MAP(u,LAMBDA(x,
SUM((a=x)*--(TAKE(w,,-1))))),
VSTACK(HSTACK(u,m),HSTACK("Total",SUM(m))))Excel solution 19 for Pivot with Total Line, proposed by ferhat CK:
=LET(a,TEXTSPLIT(TEXTJOIN(", ",,A3:A6),"-",", "),GROUPBY(TAKE(a,,1),--TAKE(a,,-1),SUM))Excel solution 20 for Pivot with Total Line, proposed by Jaroslaw Kujawa:
=LET(x;TEXTSPLIT(ARRAYTOTEXT(A3:A6);"-";{", ","; "});xx;GROUPBY(TAKE(x;;1);1*TAKE(x;;-1);SUM);HSTACK(UPPER(TAKE(xx;;1));TAKE(xx;;-1)))Excel solution 21 for Pivot with Total Line, proposed by Ankur Sharma:
=LET(
a,
TEXTJOIN(
", ",
,
A3:A6
),
b,
TEXTSPLIT(
a,
"-",
", "
),
GROUPBY(
TAKE(
b,
,
1
),
--TAKE(
b,
,
-1
),
SUM
)
)Excel solution 22 for Pivot with Total Line, proposed by Meganathan Elumalai:
=LET(a,TEXTSPLIT(CONCAT(A3:A6&", "),"-",", ",1),GROUPBY(TAKE(a,,1),--DROP(a,,1),SUM,0,1))Excel solution 23 for Pivot with Total Line, proposed by Antriksh Sharma:
=LET(
a, DROP(REDUCE("", A3:A6, LAMBDA(s, c, VSTACK(s, TEXTSPLIT(c, "-", ", ")))), 1),
b, GROUPBY(TAKE(a, , 1), --TAKE(a, , -1), SUM, 0, 1),
VSTACK({"Alphabet", "Value"}, b)
)Excel solution 24 for Pivot with Total Line, proposed by Imam Hambali:
=LET(
d, TEXTSPLIT(TEXTJOIN(", ",1,A3:A6),"-",", "),
VSTACK({"Alphabet","Value"}, GROUPBY(TAKE(d,,1),TAKE(d,,-1)*1,SUM))
)Excel solution 25 for Pivot with Total Line, proposed by Eddy Wijaya:
=LET(
d,TOCOL(REDUCE(0,A3:A6,LAMBDA(a,v,VSTACK(a,TRIM(TEXTSPLIT(v,","))))),3),
x,DROP(GROUPBY(LEFT(d),--MID(d,3,5),SUM,,0),1),
VSTACK(C2:D2,x,HSTACK(C10,BYCOL(TAKE(x,,-1),SUM))))Excel solution 26 for Pivot with Total Line, proposed by Mey Tithveasna:
=LET(
join, TEXTJOIN(",",,A3:A6),
split, TEXTSPLIT(join, ",", "-"),
t, TOCOL(split, 2),
pairs, WRAPROWS(t, 2),
l,TRIM( INDEX(pairs,,1)),
v, --(INDEX(pairs,,2)),
u, UNIQUE(l),
tot, MAP(u, LAMBDA(x, SUM(FILTER(v, l=x)))),
HSTACK(u, tot)
)Excel solution 27 for Pivot with Total Line, proposed by Milan Shrimali:
=LET(
A,
ARRAYFORMULA(
TRIM(
SPLIT(
TOCOL(
BYROW(
A3:A6,
LAMBDA(
X,
SPLIT(
X,
","
)
)
)
),
"-"
)
)
),
VSTACK(
BYROW(
UNIQUE(
CHOOSECOLS(
A,
1
)
),
LAMBDA(
X,
HSTACK(
X,
SUM(
FILTER(
--CHOOSECOLS(
A,
2
),
CHOOSECOLS(
A,
1
)=X
)
)
)
)
),
HSTACK(
"TOTAL",
ARRAYFORMULA(
SUM(
--CHOOSECOLS(
A,
2
)
)
)
)
)
)Excel solution 28 for Pivot with Total Line, proposed by Nicolas Micot:
=LET(_joinedText;JOINDRE.TEXTE(", ";;A3:A6);
_split;FRACTIONNER.TEXTE(_joinedText;"-";", ");
_alphabet;CHOISIRCOLS(_split;1);
_value;CHOISIRCOLS(_split;2)+0;
GROUPER.PAR(_alphabet;_value;SOMME))Excel solution 29 for Pivot with Total Line, proposed by Guillermo Arroyo:
=LET(
m,
TEXTSPLIT(
TEXTJOIN(
", ",
,
A3:A6
),
"-",
", "
),
PIVOTBY(
INDEX(
m,
,
1
),
,
--INDEX(
m,
,
2
),
SUM
)
)Excel solution 30 for Pivot with Total Line, proposed by Daniel Garzia:
=LET(
i,
TEXTSPLIT(
TEXTJOIN(
", ",
,
A3:A6
),
,
", "
),
PIVOTBY(
LEFT(
i
),
,
0+RIGHT(
i,
LEN(
i
)-2
),
SUM
)
)Excel solution 31 for Pivot with Total Line, proposed by Mahmoud Bani Asadi:
=LET(
a,
TEXTSPLIT(
TEXTJOIN(
", ",
,
A3:A6
),
,
", "
),
GROUPBY(
TEXTBEFORE(
a,
"-"
),
--TEXTAFTER(
a,
"-"
),
SUM
)
)Excel solution 32 for Pivot with Total Line, proposed by Maciej Kopczyński:
=LET(
arr, tblStart[Data],
a, TEXTSPLIT(TEXTJOIN(", ", TRUE, arr), "-", ", "),
first, CHOOSECOLS(a, 1),
second, CHOOSECOLS(a, 2) + 0,
group, VSTACK({"Alphabet","Value"}, GROUPBY(first, second, SUM, 0, 1)),
group
)Excel solution 33 for Pivot with Total Line, proposed by Erdit Qendro:
=LET(a,SORT(UNIQUE(TOCOL(REGEXEXTRACT(CONCAT(A3:A6),"[A-Z]",1)))),
b,MAP(a,LAMBDA(r,SUM(REGEXEXTRACT(CONCAT(A3:A6),"(?<="&r&"-)"&"d+",1)+0))),
VSTACK(HSTACK(a,b),HSTACK("TOTAL",SUM(b))))Excel solution 34 for Pivot with Total Line, proposed by Fredson Alves Pinho:
=UPPER(
GROUPBY(
TOCOL(
REGEXEXTRACT(
CONCAT(
A3:A6
),
"[A-Z]",
1
)
),
--TOCOL(
REGEXEXTRACT(
CONCAT(
A3:A6
),
"d+",
1
)
),
SUM
)
)Excel solution 35 for Pivot with Total Line, proposed by Ernesto Vega Castillo:
=LET(
a,
WRAPROWS(
REGEXEXTRACT(
CONCAT(
A3:A6
),
"[A-Z]+|[0-9]+",
1,
1
),
2
),
g,
GROUPBY(
TAKE(
a,
,
1
),
0+TAKE(
a,
,
-1
),
SUM,
0,
1
),
VSTACK(
{"Alphabet",
"Value"},
g
)
)Excel solution 36 for Pivot with Total Line, proposed by Craig Runciman:
=LET(a,TEXTSPLIT(SUBSTITUTE(TEXTJOIN(",",,A3:A6)," ",""),"-",","),h,{"Alphabet","Value"},VSTACK(h,GROUPBY(INDEX(a,,1),--INDEX(a,,2),SUM,0,1)))Excel solution 37 for Pivot with Total Line, proposed by Hussain Ali Nasser:
=LET(d,TEXTSPLIT(TEXTJOIN(", ",,A3:A6),"-",", "),l,INDEX(d,,1),n,--INDEX(d,,2),PIVOTBY(l,,n,SUM))Excel solution 38 for Pivot with Total Line, proposed by red craven:
=LET(s,TEXTSPLIT(CONCAT(A3:A6&", "),"-",", ",1),g,UPPER(GROUPBY(TAKE(s,,1),--DROP(s,,1),SUM)),IFERROR(--g,g))Excel solution 39 for Pivot with Total Line, proposed by Ricardo Romero Garcia:
=LET(
a,
TEXTSPLIT(
TEXTJOIN(
", ",
,
A3:A6
),
"-",
", "
),
GROUPBY(
TAKE(
a,
,
1
),
--DROP(
a,
,
1
),
SUM
)
)Excel solution 40 for Pivot with Total Line, proposed by CA Mohit Saxena:
=LET(
r,
TEXTSPLIT(
ARRAYTOTEXT(
A3:A6
),
"-",
", "
),
_r1,
TAKE(
r,
,
1
),
u,
UNIQUE(
_r1
),
_r2,
--TAKE(
r,
,
-1
),
s,
MAP(
u,
LAMBDA(
a,
SUM(
FILTER(
_r2,
_r1=a
)
)
)
),
VSTACK(
HSTACK(
u,
s
),
HSTACK(
"Total",
SUM(
s
)
)
)
)
=LET(
r,
DROP(
REDUCE(
"",
A3:A6,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
,
", "
)
)
)
),
1
),
LET(
_r1,
TEXTBEFORE(
r,
"-"
),
_r2,
--TEXTAFTER(
r,
"-"
),
u,
UNIQUE(
_r1
),
s,
MAP(
u,
LAMBDA(
x,
SUM(
FILTER(
_r2,
_r1=x
)
)
)
),
VSTACK(
HSTACK(
u,
s
),
HSTACK(
"Total",
SUM(
s
)
)
)
)
)Excel solution 41 for Pivot with Total Line, proposed by Cuong Pham:
=LET(
a,
TEXTJOIN(
",",
,
A3:A6
),
b,
TRIM(
TEXTSPLIT(
a,
"-",
","
)
),
c,
TAKE(
b,
,
1
),
d,
VALUE(
TAKE(
b,
,
-1
)
),
e,
UNIQUE(
c
),
f,
MAP(
e,
LAMBDA(
x,
SUM(
FILTER(
d,
x=c
)
)
)
),
VSTACK(
HSTACK(
e,
f
),
HSTACK(
"TOTAL",
SUM(
d
)
)
)
)Excel solution 42 for Pivot with Total Line, proposed by Aurélio Zafindaza:
=LET(
val,
ARRAYTOTEXT(
A3:A6
),
splittxt,
IFERROR(
VALUE(
TEXTSPLIT(
val,
"-",
", "
)
),
TEXTSPLIT(
val,
"-",
", "
)
),
GROUPBY(
CHOOSECOLS(
splittxt,
1
),
CHOOSECOLS(
splittxt,
-1
),
SUM,
0,
,
1
)
)Excel solution 43 for Pivot with Total Line, proposed by Jay Agboighale:
=LET(
n,
A3:A6,
t,
DROP(
REDUCE(
0,
n,
LAMBDA(
x,
y,
LET(
v,
TEXTSPLIT(
y,
"-",
","
),
IF(
y<>"",
VSTACK(
x,
v
),
y
)
)
)
),
1
),
a,
TRIM(
CHOOSECOLS(
t,
1
)
),
b,
CHOOSECOLS(
--t,
2
),
GROUPBY(
a,
b,
SUM
)
)Excel solution 44 for Pivot with Total Line, proposed by Mohammad Abou Daher:
=VSTACK(
{"Alphabet",
"Value"},
GROUPBY(
UPPER(
FILTER(
IFERROR(
REGEXEXTRACT(
TOCOL(
TRIM(
TEXTSPLIT(
ARRAYTOTEXT(
A3:A12
),
{"-",
","},
,
)
)
),
"[A-Z]",
1,
1
),
""
),
IFERROR(
REGEXEXTRACT(
TOCOL(
TRIM(
TEXTSPLIT(
ARRAYTOTEXT(
A3:A12
),
{"-",
","},
,
)
)
),
"[A-Z]",
1,
1
),
""
)<>""
)
),
FILTER(
IFERROR(
--TOCOL(
TRIM(
TEXTSPLIT(
ARRAYTOTEXT(
A3:A12
),
{"-",
","},
,
)
)
),
0
),
IFERROR(
--TOCOL(
TRIM(
TEXTSPLIT(
ARRAYTOTEXT(
A3:A12
),
{"-",
","},
,
)
)
),
0
)<>0
),
SUM
)
)Excel solution 45 for Pivot with Total Line, proposed by Muhammed Ödemiş:
=LET(
x,
LET(
a,
A3:A6,
b,
TEXTJOIN(
", ",
TRUE,
a
),
c,
TEXTSPLIT(
b,
{"- ",
", "}
),
WRAPROWS(
c,
2
)
),
GROUPBY(
CHOOSECOLS(
x,
1
),
--CHOOSECOLS(
x,
2
),
SUM,
,
1
)
)Excel solution 46 for Pivot with Total Line, proposed by 🍀 Nacho Cardenal:
=LET(_d;DIVIDIRTEXTO(UNIRCADENAS(", ";VERDADERO;A3:A6);"-";", ");_e;ELEGIRCOLS;AGRUPARPOR(_e(_d;1);_e(_d;2)*1;SUMA))Excel solution 47 for Pivot with Total Line, proposed by Wanderlei Huttel:
= LET(
Text,
TEXTSPLIT(
TEXTJOIN(
", ",
0,
A3:A6
),
"-",
", ",
0
),
GROUPBY(
CHOOSECOLS(
Text,
1
),
--CHOOSECOLS(
Text,
2
),
SUM,
0,
1
)
)Solving the challenge of Pivot with Total Line with Python
_x000D_Python solution 1 for Pivot with Total Line, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "700 Pivot Data.xlsx"
input = pd.read_excel(path, usecols="A", skiprows=1, nrows=5)
test = pd.read_excel(path, usecols="C:D", skiprows=1, nrows=9)
result = (
input["Data"]
.str.extractall(r"(w+)-(d+)")
.rename(columns={0: "Alphabet", 1: "Value"})
.astype({"Value": int})
.groupby("Alphabet", as_index=False)["Value"]
.sum()
)
r2 = pd.concat([result, pd.DataFrame([{"Alphabet": "TOTAL", "Value": result["Value"].sum()}])], ignore_index=True)
print(r2.equals(test)) # True
Python solution 2 for Pivot with Total Line, proposed by Luan Rodrigues:
import pandas as pd
file = r"Excel_Challenge_700 - Pivot Data.xlsx"
df = pd.read_excel(file, usecols='A',skiprows=1).dropna()
df['Data'] = df['Data'].str.split(', ')
df = df.explode('Data')
df[['ALphabet','Value']] = df['Data'].str.split('-',expand=True)
df['Value'] = df['Value'].astype('int')
grp = df.groupby('ALphabet')['Value'].sum().reset_index()
ttl = pd.concat([grp,pd.DataFrame([['TOTAL', grp['Value'].sum()]], columns=['ALphabet', 'Value'])])
print(ttl)
Python solution 3 for Pivot with Total Line, proposed by Anshu Bantra:
import pandas as pd
df = pd.DataFrame(
{
'Data': ['A-234', 'E-256', 'D-3673', 'Q-897', 'R-1', 'S-87', 'T-90', 'A-308', 'R-45', 'S-782', 'D-672', 'Q-29']
}
)
df[['Alphabet', 'Value']] = df['Data'].str.split('-', expand=True)
df['Value'] = df['Value'].astype(int)
df.groupby(by='Alphabet')['Value'].sum()
Python solution 4 for Pivot with Total Line, proposed by Ernesto Vega Castillo:
import pandas as pd
ruta = r"C:excelbichallenge700.xlsx"
df = pd.read_excel(ruta, header=None)
df_rango = df.loc[1:5, 0] # Ajusta índices según el tamaño de tus datos
data = df_rango.str.split(', ').explode().str.split('-')
df_parsed = pd.DataFrame(data.tolist(), columns=["Alphabet", "Value"])
df_parsed["Value"] = pd.to_numeric(df_parsed["Value"], errors="coerce")
df_parsed = df_parsed.dropna() # Eliminar filas con valores NaN
df_parsed = df_parsed[df_parsed["Value"] != 0] # Eliminar valores que sean 0
# Asegurar claves alfabéticas
df_parsed = df_parsed[df_parsed["Alphabet"].str.isalpha()]
result = df_parsed.groupby("Alphabet", as_index=False)["Value"].sum()
result.loc[len(result)] = ["Total", result["Value"].sum()]
print(result)
Python solution 5 for Pivot with Total Line, proposed by Claudiu B.:
import pandas as pd
df = pd.read_excel(r"data.xlsx")
df['data_split'] = df['Data'].str.split(",")
lst = list(df['data_split'])
lst_split = [s.strip().split("-") for l in lst for s in l]
letters = [l[0] for l in lst_split]
numbers = [int(l[1]) for l in lst_split]
letters.append('Total')
numbers.append(sum(numbers))
new_df = pd.DataFrame({'letter':letters, 'number':numbers})
res = (new_df
.groupby(['letter'])
.agg(value = ('number', 'sum'))
.reset_index()
)
res
Python solution 6 for Pivot with Total Line, proposed by Claudiu B.:
a slightly different version:
import pandas as pd
df = pd.read_excel(r"data.xlsx")
df['data_split'] = df['Data'].str.split(",")
df['letter'] = df['data_split'].apply(lambda x: [i.strip().split('-')[0] for i in x])
df['number'] = df['data_split'].apply(lambda x: [int(i.strip().split('-')[1]) for i in x])
temp = df.copy().loc[:, ['letter', 'number']]
df2 = temp.explode(['letter', 'number']).reset_index(drop=True)
res = (df2
.groupby(['letter'])
.agg(value = ('number', 'sum'))
.reset_index()
)
total_row = pd.DataFrame({'letter': 'Total', 'value' : res['value'].sum()}, index=[0])
output = pd.concat([res, total_row], ignore_index=True)
output
Solving the challenge of Pivot with Total Line with Python in Excel
_x000D_Python in Excel solution 1 for Pivot with Total Line, proposed by Alejandro Campos:
from collections import defaultdict
d = defaultdict(int)
for x in sum(xl("A2:A6", headers=True).values.tolist(), []):
for pair in x.split(', '):
parts = pair.split('-')
if len(parts) == 2:
k, v = parts
d[k] += int(v)
d["Total"] = sum(d.values())
pivot_df = pd.DataFrame(d.items(), columns=["Alphabet", "Value"]).sort_values(by='Alphabet').reset_index(drop=True)
Python in Excel solution 2 for Pivot with Total Line, proposed by Anshu Bantra:
df = xl("A2:A6)
df = df.reset_index()
df['Data'] = df['Data'].str.split(", ")
df = df.explode('Data')
df[['Alphabet', 'Value']] = df['Data'].str.split('-', expand=True)
df['Value'] = df['Value'].astype(int)
df.groupby(by='Alphabet')['Value'].sum()
Python in Excel solution 3 for Pivot with Total Line, proposed by Antriksh Sharma:
df = xl("A2:A6", headers = True)
# Split
a = sum(df.values.tolist(),[])
a = sum([x.split(', ') for x in a],[])
a = [x.split('-') for x in a]
df = pd.DataFrame(a, columns= ['Alphabets', 'Value'])
df = df.astype({'Value':float})
df = df.groupby(['Alphabets']).agg(Value = ('Value', 'sum')).reset_index()
total = pd.DataFrame([{'Alphabets':'Total', 'Value':df['Value'].sum()}])
result = pd.concat([df, total], ignore_index = True)
result
Solving the challenge of &Pivot with Total Line with R
_x000D_R solution 1 for Pivot with Total Line, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/700 Pivot Data.xlsx"
input = read_excel(path, range = "A2:A6")
test = read_excel(path, range = "C2:D10")
result = input %>%
separate_rows(Data, sep = ", ") %>%
separate(Data, into = c("Alphabet", "Value"), sep = "-", convert = TRUE) %>%
summarise(Value = sum(Value), .by = Alphabet) %>%
arrange(Alphabet)
r2 = result %>%
add_row(Alphabet = "TOTAL", Value = sum(result$Value))
all.equal(r2, test)
#> [1] TRUE