Group and Sum Shop Fruit Sales e.g. Mango Sales for Shop A: 10+12=22 Dynamic array function allowed, but Extra marks for Legacy Solutions or PowerQuery Solutions Note: The solution should be dynamic to include more Shops, Fruit types and Sales
📌 Challenge Details and Links
Challenge Number: 59
Challenge Difficulty: ⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Group and Sum with Power Query
Power Query solution 1 for Group and Sum, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.ToRows(A),
C = List.Combine(
List.TransformMany(
B,
each {_{0}},
(x, y) =>
[
a = List.Skip(x),
b = List.Alternate(a, 1, 1, 1),
c = List.Alternate(a, 1, 1),
d = List.Repeat({y}, List.Count(c)),
e = List.Zip({d, b, c})
][e]
)
),
D = (w) => List.Distinct(List.Zip(B){w}),
E = List.TransformMany(D(0), each D(1), (t, u) => Text.Split(t & "-" & u, "-")),
F = List.Transform(
E,
each {
_{0},
_{1},
try List.Sum(List.Zip(List.Select(C, (r) => (r{0} = _{0}) and (r{1} = _{1}))){2}) otherwise 0
}
),
G = Table.FromRows(F, {"Shop", "Fruit", "Sale"})
in
GPower Query solution 2 for Group and Sum, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grp = Table.Group(
Source,
{"Shop"},
{
{
"A",
each
let
a = _,
b = List.Skip(Table.ToColumns(a)),
c = List.Split(b, 2),
d = Table.Combine(List.Transform(c, each Table.FromColumns(_, {"Fruit", "Col2"}))),
e = Table.Group(d, "Fruit", {"Sale", each List.Sum([Col2])}),
f = Table.FromRows(
List.Transform(
{"Mango", "Apple", "Kiwi", "Noni", "Fig"},
(x) => {x, Table.SelectRows(e, each [Fruit] = x)[Sale]{0}? ?? 0}
),
Table.ColumnNames(e)
)
in
f
}
}
),
Sol = Table.ExpandTableColumn(Grp, "A", Table.ColumnNames(Grp[A]{0}))
in
SolPower Query solution 3 for Group and Sum, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Cols = Table.ToColumns(Source),
ColNames = List.Distinct(
List.Transform(Table.ColumnNames(Source), each Text.Remove(_, {"0" .. "9"}))
),
Tbls = List.TransformMany(
{Cols{0}},
(x) => List.Split(List.Skip(Cols), 2),
(x, y) => Table.FromColumns({x} & y, ColNames)
),
Tbl = Table.Combine(Tbls),
Rows = List.TransformMany(
List.Distinct(Tbl[Shop]),
(x) => List.Distinct(Tbl[Fruit]),
(x, y) => {x, y, List.Sum(Table.SelectRows(Tbl, each [Shop] = x and [Fruit] = y)[Sale]) ?? 0}
),
Res = Table.FromRows(Rows, ColNames)
in
ResPower Query solution 4 for Group and Sum, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Tbl = Table.FromRows(List.Combine(List.Transform(Table.ToRows(Source), (f) => List.Transform(List.Split(List.Skip(f),2), (x) => {List.First(f)} & x))),{"Shop","Fruit","Sale"}),
Result = [Lst1 = List.Distinct(Tbl[Shop]), Lst2 = List.Distinct(Tbl[Fruit]), fin = Table.FromRows(List.TransformMany(Lst1, each Lst2, (x,y) => {x,y}),{"Shop","Fruit"}), res = Table.AddColumn(fin, "Total Sales", (f) => List.Sum(Table.SelectRows(Tbl, (x) => x[Shop] = f[Shop] and x[Fruit] = f[Fruit])[Sale]) ?? 0) ][res]
in
Result
This contains fruits sold in particular shop,
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Tbl = Table.FromRows(List.Combine(List.Transform(Table.ToRows(Source), (f) => List.Transform(List.Split(List.Skip(f),2), (x) => {List.First(f)} & x))),{"Shop","Fruit","Sale"}),
Group = Table.Group(Tbl, {"Shop","Fruit"},{{"Total Sales", each List.Sum(_[Sale])}})
in
GroupPower Query solution 5 for Group and Sum, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Problem"]}[Content],
A = Table.AddColumn(
Table.FromRows(List.Split(List.Skip(Table.ToColumns(Source)), 2), {"Fruit", "Sale"}),
"Shop",
each Source[Shop]
),
B = Table.SelectRows(
Table.Combine(
Table.AddColumn(
A,
"Result",
each Table.FromColumns({[Shop], [Fruit], [Sale]}, {"Shop", "Fruit", "Sale"})
)[Result]
),
each [Fruit] <> null
),
C = Table.ExpandListColumn(
Table.AddColumn(Table.Distinct(B[[Shop]]), "Fruit", each List.Distinct(B[Fruit])),
"Fruit"
),
D = Table.AddColumn(
C,
"Sale",
each List.Sum(Table.SelectRows(B, (b) => (b[Shop] = [Shop]) and (b[Fruit] = [Fruit]))[Sale])
?? 0
)
in
DSolving the challenge of Group and Sum with Excel
Excel solution 1 for Group and Sum, proposed by Rick Rothstein:
=LET(r,
B4:H12,
p,
TAKE(
r,
,
1),
d,
DROP(
r,
,
1),
t,
TOCOL(
d,
,
1),
u,
UNIQUE(
FILTER(
t,
ISTEXT(
t))),
REDUCE({"Shop",
"Fruit",
"Sale"},
UNIQUE(
p),
LAMBDA(c,
n,
VSTACK(c,
DROP(REDUCE("",
u,
LAMBDA(a,
x,
VSTACK(a,
HSTACK(n,
x,
SUM((IF((d=x)*(p=n),
OFFSET(
d,
,
1),
0))))))),
1)))))Excel solution 2 for Group and Sum, proposed by Kris Jaganah:
=LET(a,
B4:B12,
b,
C4:H12,
c,
TOCOL(
--b,
3,
1),
d,
TOCOL(
b,
,
1),
e,
FILTER(
d,
ISTEXT(
d)),
f,
TOCOL(
REPT(
a,
SEQUENCE(
,
COLUMNS(
b)/2,
,
0)),
,
1),
g,
TOCOL(
UNIQUE(
f)&"-"&TOROW(
UNIQUE(
e))),
h,
TEXTSPLIT(
g,
"-"),
i,
TEXTAFTER(
g,
"-"),
VSTACK({"Shop",
"Fruit",
"Sale"},
HSTACK(h,
i,
MAP(h,
i,
LAMBDA(x,
y,
SUM((f=x)*(e=y)*c))))))Excel solution 3 for Group and Sum, proposed by Julian Poeltl:
=LET(
T,
B4:H12,
C,
WRAPROWS(
TOROW(
TAKE(
T,
,
1)&"|"&DROP(
T,
,
1)),
2),
H,
HSTACK(
TEXTBEFORE(
TAKE(
C,
,
1),
"|"),
TEXTAFTER(
C,
"|")),
N,
IFERROR(
--H,
H),
S,
TAKE(
N,
,
1),
F,
CHOOSECOLS(
N,
2),
q,
DROP(
N,
,
2),
u,
UNIQUE(
S)&"|"&TOROW(
UNIQUE(
F)),
SC,
TOCOL(
u),
VSTACK(
HSTACK(
"Shop",
"Fruit",
"Sale"),
HSTACK(
TEXTSPLIT(
SC,
"|"),
TEXTAFTER(
SC,
"|"),
TOCOL(
MAP(
u,
LAMBDA(
A,
IFERROR(
SUM(
FILTER(
q,
S&"|"&F=A)),
)))))))Excel solution 4 for Group and Sum, proposed by Hussein SATOUR:
=LET(
W,
TOCOL,
z,
TAKE,
b,
W(
C4:H12),
e,
WRAPROWS(
b,
2),
GROUPBY(
HSTACK(
FILTER(
TEXTBEFORE(
W(
B4:B12&"/"&C3:H3),
"/"),
ISNUMBER(
b)),
z(
e,
,
1)),
z(
e,
,
-1),
SUM))Excel solution 5 for Group and Sum, proposed by Duy Tùng:
=LET(a,
SORT(
TEXTSPLIT(
TEXTJOIN(
",",
,
TOROW(
UNIQUE(
B4:B12))&"/"&UNIQUE(
TOCOL(
IF(
C4:H12>"",
C4:H12,
o),
3,
1))),
"/",
",")),
HSTACK(a,
MAP(TAKE(
a,
,
1),
TAKE(
a,
,
-1),
LAMBDA(x,
y,
SUM((B4:B12=x)*(C4:G12=y)*N(
+D4:H12))))))Excel solution 6 for Group and Sum, proposed by Sunny Baggu:
=LET(
_sh,
TOCOL(
IF(
C3:H3 = C3,
B4:B12,
1 / x),
3,
1),
_f,
TOCOL(
FILTER(
C4:H12,
C3:H3 = C3),
,
1),
_s,
TOCOL(
FILTER(
C4:H12,
C3:H3 = D3),
,
1),
_uf,
UNIQUE(
_f),
_us,
UNIQUE(
_sh),
_a,
TOCOL(
IF(
SEQUENCE(
,
ROWS(
_uf)),
_us)),
_b,
TOCOL(
IF(
SEQUENCE(
,
ROWS(
_us)),
UNIQUE(
_f)),
,
1),
_c,
MAP(_a,
_b,
LAMBDA(x,
y,
SUM((x & y = _sh & _f) * _s))),
VSTACK(
HSTACK(
B3:D3),
HSTACK(
_a,
_b,
_c)))Excel solution 7 for Group and Sum, proposed by Pieter de B.:
=LET(x,
LAMBDA(
y,
TOCOL(
IFS(
D4:H12^0,
y),
2,
1)),
s,
x(
B4:B12),
f,
x(
C4:G12),
REDUCE(B3:D3,
UNIQUE(
s),
LAMBDA(r,
u,
VSTACK(r,
DROP(REDUCE("",
UNIQUE(
f),
LAMBDA(v,
w,
VSTACK(v,
HSTACK(u,
w,
SUM((s=u)*(f=w)*x(
D4:H12)))))),
1)))))Excel solution 8 for Group and Sum, proposed by Hamidi Hamid:
=LET(
x,
CHOOSECOLS(
WRAPROWS(
TOCOL(
DROP(
TEXTSPLIT(
CONCAT(
"/"&B4:B12&"-"&C4:H12),
"-",
"/"),
1)),
4),
1,
2,
4),
GROUPBY(
TAKE(
x,
,
2),
TAKE(
x,
,
-1)*1,
SUM,
,
0))Excel solution 9 for Group and Sum, proposed by Asheesh Pahwa:
=LET(
f,
C4:H12,
s,
B4:B12,
t,
TOCOL(
s&"-"&IF(
ISTEXT(
f),
f,
x),
2),
n,
TOCOL(
IF(
--(
f),
f,
x),
2),
u,
UNIQUE(
TEXTAFTER(
t,
"-")),
_u,
UNIQUE(
s),
tr,
TOROW(
_u),
c,
TOCOL(
tr&"-"&u,
,
1),
HSTACK(
TEXTSPLIT(
c,
"-"),
TEXTAFTER(
c,
"-"),
MAP(
c,
LAMBDA(
x,
SUM(
FILTER(
n,
t=x,
0))))))Excel solution 10 for Group and Sum, proposed by Ankur Sharma:
=LET(r,
WRAPROWS(
TOCOL(
B4:B12 & "-" & C4:H12),
2),
s,
TAKE(
TEXTBEFORE(
r,
"-"),
,
1),
f,
TAKE(
TEXTAFTER(
r,
"-"),
,
1),
v,
--TAKE(
TEXTAFTER(
r,
"-"),
,
-1),
us,
UNIQUE(
s),
uf,
UNIQUE(
f),
TEXTSPLIT(ARRAYTOTEXT(MAP(us,
LAMBDA(z,
ARRAYTOTEXT(MAP(uf,
LAMBDA(y,
TEXTJOIN(" - ",
,
z,
y,
SUM((s = z) * (f = y) * v)))))))),
" - ",
", "))Excel solution 11 for Group and Sum, proposed by Peter Bartholomew:
= LET(
shop,
CHOOSECOLS(
data,
{1,
2,
3,
1,
4,
5,
1,
6,
7}),
list,
WRAPROWS(
TOCOL(
shop),
3),
GROUPBY(
TAKE(
list,
,
2),
TAKE(
list,
,
-1),
SUM,
,
0)
)Excel solution 12 for Group and Sum, proposed by Ahmed Ariem:
=LET(
D,
VSTACK(
B3:D12,
CHOOSECOLS(
B4:H12,
1,
4,
5),
CHOOSECOLS(
B4:H12,
1,
6,
7)),
GROUPBY(
CHOOSECOLS(
D,
1,
2),
CHOOSECOLS(
D,
3),
SUM))Excel solution 13 for Group and Sum, proposed by Victor Momoh (MVP, MOS, R.Eng):
=LET(rng,
B4:H12,
colcount,
(COLUMNS(
rng)-1)/2,
exprng,
DROP(
REDUCE(
"",
TAKE(
rng,
,
1),
LAMBDA(
a,
b,
VSTACK(
a,
EXPAND(
b,
colcount,
,
b)))),
1),
expcol,
WRAPROWS(
TOCOL(
DROP(
rng,
,
1)),
2),
alldata,
HSTACK(
exprng,
expcol),
GROUPBY(
TAKE(
alldata,
,
2),
DROP(
alldata,
,
2),
SUM,
0,
0))Excel solution 14 for Group and Sum, proposed by Tomasz Jakóbczyk:
=LET(
t,
VSTACK(
Table1[[
hashtag
#Headers],
[Shop]:[Sale]],
HSTACK(
VSTACK(
Table1[Shop],
Table1[Shop],
Table1[Shop]),
VSTACK(
Table1[[Fruit]:[Sale]],
Table1[[Fruit2]:[Sale3]],
Table1[[Fruit4]:[Sale5]]))),
PIVOTBY(
CHOOSECOLS(
t,
1,
2),
,
CHOOSECOLS(
t,
3),
SUM,
3,
0))Excel solution 15 for Group and Sum, proposed by Mohit Rawat:
=VSTACK(
{"Shop",
"Fruit",
"Sale"},
PIVOTBY(
CHOOSECOLS(
VSTACK(
B3:D12,
CHOOSECOLS(
B4:H12,
1,
4,
5),
CHOOSECOLS(
B4:H12,
1,
6,
7)),
1,
2),
,
CHOOSECOLS(
VSTACK(
B3:D12,
CHOOSECOLS(
B4:H12,
1,
4,
5),
CHOOSECOLS(
B4:H12,
1,
6,
7)),
3),
SUM,
1,
0))Solving the challenge of Group and Sum with Python
Python solution 1 for Group and Sum, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "files/Ex-Challenge 03 2025.xlsx"
input = pd.read_excel(path, usecols="B:H", skiprows=2, nrows=9, names=['Shop', 'Fruit.1', 'Sale.1', 'Fruit.2', 'Sale.2', 'Fruit.3', 'Sale.3'])
test = pd.read_excel(path, usecols="J:L", skiprows=2, nrows=15)
result = pd.concat([input.iloc[:, [0, i, i+1]] for i in range(1, 6, 2)]).reset_index(drop=True)
result['Fruit'] = result[['Fruit.1', 'Fruit.2', 'Fruit.3']].bfill(axis=1).iloc[:, 0]
result['Sale'] = result[['Sale.1', 'Sale.2', 'Sale.3']].bfill(axis=1).iloc[:, 0]
result = result[['Shop', 'Fruit', 'Sale']]
summary = result.groupby(['Shop', 'Fruit'], as_index=False)['Sale'].sum()
summary = summary.pivot(index='Shop', columns='Fruit', values='Sale').fillna(0).reset_index()
summary = summary.melt(id_vars='Shop', var_name='Fruit', value_name='Sale')
summary['Sale'] = summary['Sale'].astype(int)
summary = summary.sort_values(['Shop', 'Sale'], ascending=[True, False]).reset_index(drop=True)
test.columns = summary.columns
test = test.sort_values(['Shop', 'Sale'], ascending=[True, False]).reset_index(drop=True)
print(all(summary == test)) # TruePython solution 2 for Group and Sum, proposed by Luan Rodrigues:
PY Solution!
import pandas as pd
file = "Ex-Challenge 03 2025.xlsx"
df = pd.read_excel(file,usecols="B:H",skiprows=2,nrows=9)
df['Order'] = df.index
n = len([col for col in df.columns if col.startswith('Fruit')])
upv = pd.melt(df,id_vars=['Shop','Order'],var_name='Atributo',value_name='Valor')
upv['Atributo'] = upv['Atributo'].str.replace(r'[^a-zA-Z]', '',regex=True)
upv = upv.sort_values(by=['Order','Atributo'])
upv['Sale'] = upv['Valor'].shift(-n)
upv = upv[upv['Atributo'] != 'Sale']
grp = upv.groupby(['Shop','Valor'])['Sale'].sum().reset_index()
print(grp)Solving the challenge of Group and Sum with Python in Excel
Python in Excel solution 1 for Group and Sum, proposed by Aditya Kumar Darak 🇮🇳:
#PythonInExcel
df = xl("B3:H12", True).set_index("Shop")
stc = df.stack().droplevel(1)
shop = stc.index[::2]
fruit = stc.values[::2]
sale = stc.values[1::2]
df = pd.DataFrame({"Shop": shop, "Fruit": fruit, "Sales": sale})
result = df.groupby(["Shop", "Fruit"], sort=False).sum()
result.unstack(fill_value=0).stack().reset_index()Python in Excel solution 2 for Group and Sum, proposed by Seokho MOON:
Python in Excel
import re
df =xl("Table1[
#All]", headers=True)
col_names = pd.Series(df.columns.str.replace(r"d+", "", regex=True)).unique()
subsets = [
df.iloc[:, [0, i, i + 1]].set_axis(col_names, axis=1)
for i in range(1, len(df.columns), 2)
]
df_combined = pd.concat(subsets, ignore_index=True)
df_combined["Fruit"] = pd.Categorical(
df_combined["Fruit"], categories=df_combined["Fruit"].unique(), ordered=True
)
res = (
df_combined.groupby(["Shop", "Fruit"])["Sale"]
.sum()
.unstack(fill_value=0)
.stack()
.reset_index(name="Sale")
)
resSolving the challenge of Group and Sum with R
R solution 1 for Group and Sum, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/Ex-Challenge 03 2025.xlsx"
input = read_excel(path, range = "B3:H12")
test = read_excel(path, range = "J3:L18") %>% arrange(Shop, desc(Sale))
# as I wanted. I'll do it another way :D
result =
bind_rows(
input %>% select(1,2,3),
input %>% select(1,4,5),
input %>% select(1,6,7)
) %>%
mutate(Fruit = as.factor(Fruit)) %>%
summarise(Sale = sum(Sale), .by = c(Shop, Fruit)) %>%
complete(Shop, Fruit, fill = list(Sale = 0)) %>%
mutate(Fruit = as.character(Fruit)) %>%
arrange(Shop, desc(Sale))
all.equal(result, test, check.attributes = FALSE)
# [1] TRUE