List All names and total cost against each name.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 644
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Names With Total Costs with Power Query
_x000D_Power Query solution 1 for Names With Total Costs, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.Group(
A,
{"Name & Category", "Cost"},
{"Total Cost", each List.Sum([Cost])},
0,
(x, y) => Number.From(y[Cost] = null and y[#"Name & Category"] <> null)
)[[#"Name & Category"], [Total Cost]]
in
BPower Query solution 2 for Names With Total Costs, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Insert = Table.InsertRows(Source, 0, {[#"Name & Category" = null, Cost = null]}),
Group = Table.Group(
Insert,
"Name & Category",
{{"Name", each [#"Name & Category"]{1}}, {"Total Cost", each List.Sum([Cost])}},
0,
(x, y) => Number.From(y is null)
)[[Name], [Total Cost]]
in
GroupPower Query solution 3 for Names With Total Costs, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.Group(
Source,
"Name & Category",
{
{
"A",
each
let
a = _,
b = List.Sum(a[Cost]),
c = Table.Skip(a, each [#"Name & Category"] = null)[#"Name & Category"]{0},
d = Table.FromColumns({{c}, {b}}, {"Name", "Total Cost"})
in
d
}
},
0,
(x, y) => Number.From(y = null)
)[A]
)
in
SolPower Query solution 4 for Names With Total Costs, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
RemBlanks = Table.SelectRows(
Source,
each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
),
FillName = Table.FillDown(
Table.AddColumn(RemBlanks, "Name", each if [Cost] = null then [#"Name & Category"] else null),
{"Name"}
),
GroupSum = Table.Group(FillName, {"Name"}, {{"Total Cost", each List.Sum([Cost])}})
in
GroupSumPower Query solution 5 for Names With Total Costs, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Group(
Source,
{"Name & Category", "Cost"},
{"Total Cost", each List.Sum([Cost])},
0,
(x, y) => Number.From(y[#"Name & Category"] <> null and y[Cost] = null)
)[[#"Name & Category"], [Total Cost]]
in
ResultPower Query solution 6 for Names With Total Costs, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.SelectRows(
Table.AddColumn(S, "A", each if [Cost] = null then 0 else 1),
each [#"Name & Category"] <> null
),
b = Table.FillDown(
Table.AddColumn(a, "B", each if [A] = 0 then [#"Name & Category"] else null),
{"B"}
),
c = Table.Group(b, "A", {{"Name", each [B]{0}}, {"Total Cost", each List.Sum([Cost])}}, 0),
Sol = Table.SelectRows(c, each [A] = 1)[[Name], [Total Cost]]
in
SolPower Query solution 7 for Names With Total Costs, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rows = List.Accumulate(
List.Select(Table.ToRows(Source), each List.NonNullCount(_) > 0),
{},
(a, v) => if v{1} = null then a & {{v{0}}} else List.RemoveLastN(a) & {List.Last(a) & {v{1}}}
),
Res = Table.FromList(Rows, each {_{0}, List.Sum(List.Skip(_))}, {"Name", "Total Cost"})
in
ResPower Query solution 8 for Names With Total Costs, proposed by Ankur Sharma:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"H1", type text}, {"H2", Int64.Type}}),
AddedCustom = Table.AddColumn(ChangedType, "Name", each if [H1] <> null and [H2] = null then [H1] else null),
Names = Table.FillDown(AddedCustom,{"Name"}),
GroupedRows = Table.Group(Names, {"Name"}, {{"Total Cost", each List.Sum([H2]), type nullable number}})
in
GroupedRows
Best Wishes!
Power Query solution 9 for Names With Total Costs, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Group(
Source,
"Name & Category",
{
{"Name", each List.RemoveNulls([#"Name & Category"]){0}},
{"Total Cost", each List.Sum(_[Cost])}
},
0,
(x, y) => Number.From(y is null)
)[[Name], [Total Cost]]
in
ResultPower Query solution 10 for Names With Total Costs, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
GetNames = Table.AddColumn(
Source,
"Name",
each if [Cost] = null then [#"Name & Category"] else null
),
FillNames = Table.FillDown(GetNames, {"Name"}),
GroupRows = Table.Group(
FillNames,
{"Name"},
{{"Total Cost", each List.Sum([Cost]), type nullable number}}
)
in
GroupRowsPower Query solution 11 for Names With Total Costs, proposed by Ernesto Vega Castillo:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddedCol = Table.AddColumn(Source, "Name", each if [Column2] = null then [Column1] else null),
FilledDown = Table.FillDown(AddedCol, {"Name"}),
RemovedCol = Table.RemoveColumns(FilledDown, {"Column1"}),
ReorderedCol = Table.ReorderColumns(RemovedCol, {"Name", "Column2"}),
Filtered = Table.SelectRows(ReorderedCol, each ([Column2] <> null)),
Sol = Table.Group(Filtered, {"Name"}, {{"Total Cost", each List.Sum([Column2]), type number}})
in
SolSolving the challenge of Names With Total Costs with Excel
_x000D_Excel solution 1 for Names With Total Costs, proposed by Bo Rydobon 🇹🇭:
=LET(c,B3:B18,m,SCAN(0,c,LAMBDA(a,v,IF(v,a,@+A18:v))),DROP(GROUPBY(HSTACK(XMATCH(m,m),m),c,SUM,,0,,c),,1))Excel solution 2 for Names With Total Costs, proposed by Rick Rothstein:
=LET(r,A3:B17,n,TAKE(r,,1),c,TAKE(r,,-1),HSTACK(FILTER(DROP(n,-1),DROP(n,1)="Salary"),TOCOL(LET(s,SCAN(0,OFFSET(c,,,ROWS(c)+2),LAMBDA(a,x,SUM(x+a)*(x<>""))),IF((DROP(s,1)=0)*(DROP(s,2)=0),s,z)),3)))Excel solution 3 for Names With Total Costs, proposed by John V.:
=LET(i,B3:B17,g,GROUPBY(SCAN(,i=0,SUM),i,SUM,,0,,i),IF({1,0},INDEX(FILTER(A3:A17,i=0),g),g))Excel solution 4 for Names With Total Costs, proposed by 🇰🇷 Taeyong Shin:
=LET(c,
B3:B17,
r,
ROW(
c
),
l,
LOOKUP(r,
r/(c=0),
A3:A17),
DROP(
GROUPBY(
HSTACK(
XMATCH(
l,
l
),
l
),
c,
SUM,
,
0,
,
c
),
,
1
))Excel solution 5 for Names With Total Costs, proposed by Kris Jaganah:
=LET(
a,
A3:A17,
VSTACK(
{"Name",
"Total Cost"},
DROP(
GROUPBY(
SCAN(
1,
a,
LAMBDA(
x,
y,
IF(
y="",
x+1,
x
)
)
),
HSTACK(
a,
B3:B17
),
HSTACK(
SINGLE,
SUM
),
0,
0,
,
a<>""
),
1,
1
)
)
)Excel solution 6 for Names With Total Costs, proposed by Timothée BLIOT:
=LET(
A,
A3:A17,
B,
B3:B17,
C,
NOT(
ISBLANK(
A
)
)*ISBLANK(
B
),
D,
SCAN(
0,
SEQUENCE(
ROWS(
C
)
),
LAMBDA(
w,
v,
IF(
INDEX(
C,
v
),
INDEX(
A,
v
),
w
)
)
),
E,
GROUPBY(
D,
B,
SUM,
,
0
),
SORTBY(
E,
XMATCH(
TAKE(
E,
,
1
),
D
)
)
)Excel solution 7 for Names With Total Costs, proposed by Oscar Mendez Roca Farell:
=LET(
i,
B3:B17="",
DROP(
GROUPBY(
HSTACK(
SCAN(
,
i,
SUM
),
SCAN(
,
REPT(
A3:A17,
i
),
LAMBDA(
i,
x,
IF(
x>"",
x,
i
)
)
)
),
B3:B17,
SUM,
,
0,
,
1-i
),
,
1
)
)Excel solution 8 for Names With Total Costs, proposed by Duy Tùng:
=LET(b,
B3:B17,
c,
SCAN(
0,
b=0,
SUM
),
a,
LOOKUP(
c,
GROUPBY(
c,
b,
SUM
)
),
FILTER(HSTACK(
A3:A17,
a
),
(b=0)*(a>0)))Excel solution 9 for Names With Total Costs, proposed by Duy Tùng:
=LET(a,SCAN("",B3:B17,LAMBDA(x,y,IF(y="",OFFSET(y,,-1),x))),DROP(GROUPBY(HSTACK(XMATCH(a,a),a),B3:B17,SUM,,0,,a>0),,1))Excel solution 10 for Names With Total Costs, proposed by Sunny Baggu:
=LET(
_s,
SEQUENCE(
ROWS(
A3:A17
)
),
_n,
FILTER(A3:A17,
(B3:B17 = "") * (A3:A17 <> "")),
_a,
VSTACK(
FILTER(_s,
(B3:B17 = "") * (A3:A17 <> "")),
TAKE(
_s,
-1
)
),
_b,
DROP(
_a,
-1
),
_c,
DROP(
_a,
1
),
_d,
MAP(
_b,
_c,
LAMBDA(a,
b,
SUM((_s > a) * (_s <= b) * B3:B17))
),
HSTACK(
_n,
_d
)
)Excel solution 11 for Names With Total Costs, proposed by Sunny Baggu:
=LET(
_a,
VSTACK(
0,
SCAN(
0,
B3:B17,
LAMBDA(
a,
v,
IF(
v = "",
0,
a + v
)
)
)
),
_t,
FILTER(_a,
(A3:A18 = "") * (B3:B18 = "")),
_n,
FILTER(A3:A17,
(B3:B17 = "") * (A3:A17 <> "")),
HSTACK(
_n,
_t
)
)Excel solution 12 for Names With Total Costs, proposed by Sunny Baggu:
=LET(
_n,
FILTER(A3:A17,
(B3:B17 = "") * (A3:A17 <> "")),
_a,
SCAN(
0,
BYROW(
N(
A3:A17 = TOROW(
_n
)
),
LAMBDA(
a,
SUM(
a
)
)
),
LAMBDA(
a,
v,
a + v
)
),
_s,
MAP(UNIQUE(
_a
),
LAMBDA(a,
SUM((_a = a) * B3:B17))),
HSTACK(
_n,
_s
)
)Excel solution 13 for Names With Total Costs, proposed by Md. Zohurul Islam:
=LET(
z,
FILTER(
A3:B17,
A3:A17>""
),
hdr,
HSTACK(
"Name",
"Total Cost"
),
u,
DROP(
z,
,
1
),
v,
TAKE(
z,
,
1
),
a,
SCAN(
"",
IF(
u=0,
v,
0
),
LAMBDA(
x,
y,
IF(
y=0,
x,
y
)
)
),
b,
VSTACK(
hdr,
GROUPBY(
a,
u,
SUM,
0,
0
)
),
b
)Excel solution 14 for Names With Total Costs, proposed by Md. Zohurul Islam:
=VSTACK({"Name","Total Cost"},GROUPBY(SCAN(0,IF(B3:B17="",A3:A17,""), LAMBDA(x,y,IF(OR(y="",y=0),x,y))),B3:B17,SUM,0,0))Excel solution 15 for Names With Total Costs, proposed by Pieter de B.:
=LET(b,B3:B17,GROUPBY(SCAN(0,b,LAMBDA(x,y,IF(-y,x,@+A17:y))),b,SUM,,0,,b))
Sorted:
=LET(b,B3:B17,g,GROUPBY(SCAN(0,b,LAMBDA(x,y,IF(-y,x,@+A17:y))),b,SUM,,0,,b),SORTBY(g,XMATCH(&TAKE(g,,1),A3:A17)))
Or non-groupby:
=LET(x,TOCOL(IFS(A4:A18="Salary",A3:A17),2),HSTACK(x,XLOOKUP(x,SCAN(0,B3:B17,LAMBDA(a,b,IF(-b,a,@+A17:b))),SCAN(0,B3:B17,LAMBDA(a,b,IF(-b,a+b,0))),,,-1)))Excel solution 16 for Names With Total Costs, proposed by ferhat CK:
=LET(
a,
SCAN(
0,
A3:A17,
LAMBDA(
x,
y,
IF(
y="",
x+1,
x
)
)
),
REDUCE(
{"Name",
"Total Cost"},
UNIQUE(
a
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
b,
FILTER(
A3:A17,
a=y
),
c,
IF(
TAKE(
b,
1
)=0,
CHOOSEROWS(
b,
2
),
TAKE(
b,
1
)
),
HSTACK(
c,
SUM(
FILTER(
B3:B17,
a=y
)
)
)
)
)
)
)
)Excel solution 17 for Names With Total Costs, proposed by Jaroslaw Kujawa:
=VSTACK({"Name","Total Cost"};REDUCE("";A3:A17;LAMBDA(a;x;LET(w;VSTACK(a;IF((x<>"")*(OFFSET(x;;1)="");HSTACK(x;SUM(OFFSET(x;1;1;MIN(IF((OFFSET(x;1;1):$B$18="")*(OFFSET(x;1;):$A$18="");ROW(OFFSET(x;1;1):$B$18)))-ROW(x))));""));FILTER(w;LEN(TAKE(w;;1)))))))Excel solution 18 for Names With Total Costs, proposed by Ankur Sharma:
=LET(n, A3:A17, t, B3:B17,
a, IF((n <> "") * (t = ""), n, ""),
b, SCAN("", a, LAMBDA(i, r, IF(r <> "", r, i))),
c, UNIQUE(b),
d, MAP(c, LAMBDA(z, SUM(FILTER(t, b = z)))),
HSTACK(c, d))Excel solution 19 for Names With Total Costs, proposed by Meganathan Elumalai:
=GROUPBY(
SCAN(
"",
IF(
B3:B17=0,
A3:A17,
0
),
LAMBDA(
a,
v,
IF(
v=0,
a,
v
)
)
),
B3:B17,
SUM,
,
0
)Excel solution 20 for Names With Total Costs, proposed by JvdV -:
=LET(
x,
GROUPBY(
IFNA(
MAP(
A3:A17,
LAMBDA(
s,
XMATCH(
,
A3:s,
,
-1
)
)
),
)+1,
B3:B17,
SUM,
,
0
),
IF(
{1,
0},
INDEX(
A3:A17,
x
),
x
)
)
A little shorter:
=LET(s,
(B3:B17=0)*MAP(
B4:B18,
LAMBDA(
s,
SUM(
s:XLOOKUP(
"^$",
s:B18,
s:B18,
,
3
)
)
)
),
FILTER(
HSTACK(
A3:A17,
s
),
s
))Excel solution 21 for Names With Total Costs, proposed by Milan Shrimali:
=LET(COLM1,A3:A17,COLM2,B3:B17,UNQ,FILTER(HSTACK(COLM1,ROW(COLM1)),(COLM2="")*(COLM1<>"")),TBL,HSTACK(COLM1,COLM2,ARRAYFORMULA(ROW(COLM2))),FNL,HSTACK(TBL,BYROW(CHOOSECOLS(TBL,3),LAMBDA(X,XLOOKUP(X,CHOOSECOLS(UNQ,2),CHOOSECOLS(UNQ,1),,-1)))),BYROW(CHOOSECOLS(UNQ,1),LAMBDA(X,HSTACK(X,SUM(FILTER(CHOOSECOLS(FNL,2),CHOOSECOLS(FNL,4)=X))))))Excel solution 22 for Names With Total Costs, proposed by Ahmed Ariem:
=LET(
a,
A3:A17,
b,
B3:B17,
GROUPBY(
SCAN(
"",
IF(
b="",
a,
""
),
LAMBDA(
acc,
x,
IF(
x>"",
x,
acc
)
)
),
b,
SUM
)
)Excel solution 23 for Names With Total Costs, proposed by Nicolas Micot:
=LET(_names;
UNIQUE(FILTRE(A3:A17;
(A3:A17<>"")*(B3:B17="")));
_indexes;
SCAN(0;
(A3:A17<>"")*(B3:B17="");
LAMBDA(
l_valeur;
l_test;
l_valeur+SI(
l_test;
1;
0
)
));
_totalCosts;
MAP(UNIQUE(
_indexes
);
LAMBDA(l_index;
SOMME((B3:B17)*(_indexes=l_index))));
ASSEMB.H(
_names;
_totalCosts
))Excel solution 24 for Names With Total Costs, proposed by Jorge Alvarez:
=LET(
n;
A3:A17;
co;
B3:B17;
val;
ENCOL(
MAP(
n;
co;
LAMBDA(
a;
v;
SI.CONJUNTO(
Y(
a<>"";
v=""
);
a;
Y(
a<>"";
v<>""
);
v
)
)
);
2
);
nombre;
SCAN(
"";
val;
LAMBDA(
a;
v;
SI(
ESNUMERO(
v
);
a;
v
)
)
);
bd;
APILARH(
nombre;
val
);
bdm;
AGRUPARPOR(
ELEGIRCOLS(
bd;
1
);
ELEGIRCOLS(
bd;
2
);
SUMA;
0;
0
);
ORDENARPOR(
bdm;
COINCIDIRX(
ELEGIRCOLS(
bdm;
1
);
UNICOS(
nombre
);
)
)
)Excel solution 25 for Names With Total Costs, proposed by Zbigniew Szyszkowski:
=GROUPBY(
SCAN(
"",
IF(
B3:B17="",
A3:A17,
""
),
LAMBDA(
a,
i,
IF(
i="",
a,
i
)
)
),
B3:B17,
SUM,
,
0,
,
A3:A17<>""
)Solving the challenge of Names With Total Costs with Python
_x000D_Python solution 1 for Names With Total Costs, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "644 Total Cost.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=16)
test = pd.read_excel(path, usecols="D:E", skiprows=1, nrows=4).sort_values('Name').reset_index(drop = True)
input['Name'] = input['Name & Category'].where(input['Cost'].isna()).ffill()
result = input.fillna({'Cost': 0}).groupby('Name')['Cost'].sum().reset_index().rename(columns={'Cost': 'Total Cost'})
result['Total Cost'] = result['Total Cost'].astype(np.int64)
print(result.equals(test)) # True
Python solution 2 for Names With Total Costs, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_644 - Total Cost.xlsx'
df = pd.read_excel(io=file_path, usecols='A:B', skiprows=1)
# Perform data manipulation
df['Name & Category'] = df['Name & Category'].where(cond=pd.isna(df['Cost']), other=pd.NA).ffill()
df = (
df
.groupby('Name & Category', sort=False)['Cost'].sum()
.rename('Total Cost')
.reset_index()
.map(lambda x: x if isinstance(x, str) else int(x))
)
df
Solving the challenge of Names With Total Costs with Python in Excel
_x000D_Python in Excel solution 1 for Names With Total Costs, proposed by Alejandro Campos:
df = xl("A2:B17", headers=True)
names_cost = {}
current_name = None
for _, row in df.iterrows():
category, cost = row["Name & Category"], row["Cost"]
if pd.notna(category) and category not in ["Salary", "Travel", "Admin", "Advertising"]:
current_name = category
names_cost[current_name] = 0
elif current_name and pd.notna(cost):
names_cost[current_name] += cost
result_df = pd.DataFrame(names_cost.items(), columns=["Name", "Total Cost"])
Solving the challenge of Names With Total Costs with R
_x000D_R solution 1 for Names With Total Costs, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/644 Total Cost.xlsx"
input = read_excel(path, range = "A2:B17")
test = read_excel(path, range = "D2:E6")
result = input %>%
mutate(Name = ifelse(is.na(Cost), `Name & Category`, NA)) %>%
fill(Name, .direction = "down") %>%
summarise(`Total Cost` = sum(Cost, na.rm = T), .by = Name)
all.equal(result, test)
#> [1] TRUE