Find the Max and Min for all Zones.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 332
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Min and Max for all zones with Power Query
Power Query solution 1 for Find Min and Max for all zones, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
z = each [Zone],
S = Table.FromRows(
List.TransformMany(
List.Sort(List.Distinct(z(Source))),
(x) =>
let
r = Table.SelectRows(Source, each z(_) = x)[Sales]
in
{{x, List.Max(r), List.Min(r)}},
(x, y) => y
),
{"Zone", "Max", "Min"}
)
in
SPower Query solution 2 for Find Min and Max for all zones, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.Sort(
Table.Group(
Source,
{"Zone"},
{{"Max", each List.Max([Sales])}, {"Min", each List.Min([Sales])}}
),
"Zone"
)
in
SPower Query solution 3 for Find Min and Max for all zones, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(
Source,
"Zone",
{{"Max", each List.Max([Sales])}, {"Min", each List.Min([Sales])}}
),
Return = Table.Sort(Group, "Zone")
in
ReturnPower Query solution 4 for Find Min and Max for all zones, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Zone"},
{{"All", each Table.FromColumns({{List.Max([Sales])}, {List.Min([Sales])}}, {"Max", "Min"})}}
),
Sol = Table.Sort(Table.ExpandTableColumn(Group, "All", Table.ColumnNames(Group[All]{0})), "Zone")
in
SolPower Query solution 5 for Find Min and Max for all zones, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.Sort(
Table.Group(Fonte, {"Zone"}, {{"Max", each List.Max([Sales])}, {"Min", each List.Min([Sales])}}),
{each [Zone], 0}
)
in
resPower Query solution 6 for Find Min and Max for all zones, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Group = Table.Sort( Table.Group(Source, {"Zone"}, {{"Max", each List.Max([Sales]), type number}, {"Min", each List.Min([Sales]), type number}}), {"Zone", Order.Ascending})
in
Group
NOTE: I've been doing the Excel BI challenges since Challenge #5. They are literally **never** this simple and straightforward.
This can only mean one thing - this is the calm before the storm. I predict Vijay is winding up to hit us soon with the most fiendishly difficult challenge yet, loaded with curveballs and booby traps. So far the pattern has held pretty predictably - an unusally simple one is almost always the harbinger of doom. I eagerly anticipate its arrival... 😁
Power Query solution 7 for Find Min and Max for all zones, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = Table.Sort(Table.Group(Origen, {"Zone"}, {{"G", each _}}), {{"Zone", 0}}),
b = Table.AddColumn(a, "Max", each List.Max([G][Sales])),
Sol = Table.AddColumn(b, "Min", each List.Min([G][Sales]))[[Zone], [Max], [Min]]
in
SolPower Query solution 8 for Find Min and Max for all zones, proposed by Owen Price:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Group(
Source,
"Zone",
{{"Max", each List.Max([Sales])}, {"Min", each List.Min([Sales])}}
)
in
ResultPower Query solution 9 for Find Min and Max for all zones, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Group = Table.Group(Source, {"Zone"}, {{"Tbl", each _, type table [Zone=nullable text, Sales=nullable number]}}),
Agg = Table.AggregateTableColumn(Group, "Tbl", {{"Sales", List.Max, "Max"}, {"Sales", List.Min, "Min"} }),
Final = Table.Sort(Agg,{{"Zone", Order.Ascending}})
in
Final
🧙♂️🧙♂️🧙♂️
Power Query solution 10 for Find Min and Max for all zones, proposed by Nicolas Micot:
let
Source = Excel.CurrentWorkbook(){[Name = "_sales"]}[Content],
#"En-têtes promus" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Type modifié" = Table.TransformColumnTypes(
#"En-têtes promus",
{{"Zone", type text}, {"Sales", Int64.Type}}
),
#"Lignes groupées" = Table.Group(
#"Type modifié",
{"Zone"},
{
{"Max", each List.Max([Sales]), type nullable number},
{"Min", each List.Min([Sales]), type nullable number}
}
)
in
#"Lignes groupées"Power Query solution 11 for Find Min and Max for all zones, proposed by Alejandra Horvath CPA, CGA:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grouped = Table.Group(
Source,
{"Zone"},
{{"Max", each List.Max([Sales]), type number}, {"Min", each List.Min([Sales]), type number}}
),
Sorted = Table.Sort(Grouped, {{"Zone", Order.Ascending}})
in
SortedSolving the challenge of Find Min and Max for all zones with Excel
Excel solution 1 for Find Min and Max for all zones, proposed by Rick Rothstein:
=LET(
a,
A2:A20,
b,
B2:B20,
z,
SORT(
UNIQUE(
a,
TRUE
)
),
HSTACK(
UNIQUE(
z
),
UNIQUE(
MAXIFS(
b,
a,
z
)
),
UNIQUE(
MINIFS(
b,
a,
z
)
)
)
)Excel solution 2 for Find Min and Max for all zones, proposed by John V.:
=GROUPBY(
A1:A20,
B1:B20,
HSTACK(
MAX,
MIN
),
,
0
)
With PROPER headers:
✅ =LET(
g,
PROPER(
GROUPBY(
A1:A20,
B1:B20,
HSTACK(
MAX,
MIN
),
,
0
)
),
IFERROR(
--g,
g
)
)Excel solution 3 for Find Min and Max for all zones, proposed by محمد حلمي:
=LET(
a,
A2:A20,
b,
B2:B20,
u,
UNIQUE(
a
),
SORT(
HSTACK(
u,
MAXIFS(
b,
a,
u
),
MINIFS(
b,
a,
u
)
)
)
)Excel solution 4 for Find Min and Max for all zones, proposed by Kris Jaganah:
=LET(
a,
A2:A20,
b,
B2:B20,
VSTACK(
{"Zone",
"Max",
"Min"},
HSTACK(
GROUPBY(
a,
b,
MAX,
,
0
),
DROP(
GROUPBY(
a,
b,
MIN,
,
0
),
,
1
)
)
)
)Excel solution 5 for Find Min and Max for all zones, proposed by Kris Jaganah:
=GROUPBY(
A1:A20,
B1:B20,
HSTACK(
MAX,
MIN
),
3,
0
)Excel solution 6 for Find Min and Max for all zones, proposed by Kris Jaganah:
=PIVOTBY(
A1:A20,
,
B1:B20,
HSTACK(
MAX,
MIN
),
3,
0
)Excel solution 7 for Find Min and Max for all zones, proposed by Julian Poeltl:
=REDUCE(
HSTACK(
"Zone",
"Max",
"Min"
),
UNIQUE(
A2:A20
),
LAMBDA(
A,
B,
VSTACK(
A,
LET(
F,
FILTER(
B2:B20,
A2:A20=B
),
HSTACK(
B,
MAX(
F
),
MIN(
F
)
)
)
)
)
)Excel solution 8 for Find Min and Max for all zones, proposed by Julian Poeltl:
=LET(
Z,
A2:A20,
S,
B2:B20,
U,
SORT(
UNIQUE(
Z
)
),
VSTACK(
HSTACK(
"Zone",
"Max",
"Min"
),
HSTACK(
U,
BYROW(
U,
LAMBDA(
A,
MAXIFS(
S,
Z,
A
)
)
),
BYROW(
U,
LAMBDA(
A,
MINIFS(
S,
Z,
A
)
)
)
)
)
)Excel solution 9 for Find Min and Max for all zones, proposed by Timothée BLIOT:
=LET(
A,
A2:A20,
B,
B2:B20,
REDUCE(
{"Zone",
"Max",
"Min"},
UNIQUE(
A
),
LAMBDA(
w,
v,
VSTACK(
w,
HSTACK(
v,
MAX(
FILTER(
B,
A=v
)
),
MIN(
FILTER(
B,
A=v
)
)
)
)
)
)
)Excel solution 10 for Find Min and Max for all zones, proposed by Nikola Z Grujicic – Nikola Ž Grujičić:
=LET(
a,
SORT(
UNIQUE(
A2:A20
)
),
b,
MAXIFS(
B2:B20,
A2:A20,
a
),
c,
MINIFS(
B2:B20,
A2:A20,
a
),
VSTACK(
{"Zone",
"Max",
"Min"},
HSTACK(
a,
b,
c
)
)
)Excel solution 11 for Find Min and Max for all zones, proposed by Hussein SATOUR:
=TEXTSPLIT(CONCAT(LET(z, A2:A20, s, B2:B20, MAP(SORT(UNIQUE(z)), LAMBDA(x, TEXTJOIN(",",,x, TAKE((SORT(FILTER(s, z=x))), {-1,1}))&"/")))), ",", "/",1)Excel solution 12 for Find Min and Max for all zones, proposed by Sunny Baggu:
=LET(
_tbl,
SORT(
A2:B20,
{1,
2},
{1,
-1}
),
_v,
SORT(
UNIQUE(
A2:A20
)
),
HSTACK(
_v,
XLOOKUP(
_v,
TAKE(
_tbl,
,
1
),
TAKE(
_tbl,
,
-1
),
,
,
{1,
-1}
)
)
)Excel solution 13 for Find Min and Max for all zones, proposed by Sunny Baggu:
=REDUCE(
{"Zone",
"Max",
"Min"},
SORT(
UNIQUE(
A2:A20
)
),
LAMBDA(
a,
v,
VSTACK(
a,
HSTACK(
v,
TAKE(
SORT(
TOCOL(
IF(
A2:A20 = v,
B2:B20,
1 / x
),
3
)
),
{-1,
1}
)
)
)
)
)Excel solution 14 for Find Min and Max for all zones, proposed by LEONARD OCHEA 🇷🇴:
=GROUPBY(
A1:A20,
B1:B20,
HSTACK(
MAX,
MIN
),
,
0
)
=LET(z,
A2:A20,
s,
B2:B20,
REDUCE({"Zone",
"Max",
"Min"},
SORT(
UNIQUE(
z
)
),
LAMBDA(a,
b,
LET(p,
IF(
z=b,
s,
""
),
VSTACK(a,
HSTACK(b,
MAX(
p
),
(MIN(
p
))))))))Excel solution 15 for Find Min and Max for all zones, proposed by Abdallah Ally:
=LET(
a,
A2:A20,
b,
B2:B20,
c,
SORT(
UNIQUE(
a
)
),
VSTACK(
{"Zone",
"Max",
"Min"},
HSTACK(
c,
MAXIFS(
b,
a,
c
),
MINIFS(
b,
a,
c
)
)
)
)Excel solution 16 for Find Min and Max for all zones, proposed by Abdallah Ally:
=LET(a,A2:A20,b,B2:B20,REDUCE({"Zone","Max","Min"},SORT(UNIQUE(a)),LAMBDA(x,y,VSTACK(x,HSTACK(y,MAX(FILTER(b,a=y)),MIN(FILTER(b,a=y)))))))Excel solution 17 for Find Min and Max for all zones, proposed by Md. Zohurul Islam:
=VSTACK(
{"Zone",
"Max",
"Min"},
DROP(
PIVOTBY(
A1:A20,
,
B1:B20,
HSTACK(
MAX,
MIN
),
3,
0
),
2
)
)Excel solution 18 for Find Min and Max for all zones, proposed by Md. Zohurul Islam:
=LET(a,GROUPBY(A2:A20,B2:B20,HSTACK(MAX,MIN),0,0),IF(a="","Zone",a))Excel solution 19 for Find Min and Max for all zones, proposed by Charles Roldan:
=DROP(
REDUCE(
{0,
0},
D3:D6,
LAMBDA(
a,
b,
VSTACK(
a,
TAKE(
SORT(
FILTER(
B2:B20,
b=A2:A20
)
),
{-1,
1}
)
)
)
),
1
)Excel solution 20 for Find Min and Max for all zones, proposed by Owen Price:
=LET(
g,GROUPBY(A1:A20,B1:B20,HSTACK(MAX,MIN),3,0),
VSTACK(PROPER(TAKE(g,1)),DROP(g,1))
)Excel solution 21 for Find Min and Max for all zones, proposed by Mey Tithveasna:
=LET(
a,
A2:A20,
u,
UNIQUE(
a
),
SORT(
HSTACK(
u,
MAXIFS(
B2:B20,
a,
u
),
MINIFS(
B2:B20,
a,
u
)
),
2,
-1
)
)Excel solution 22 for Find Min and Max for all zones, proposed by Pieter de Bruijn:
=GROUPBY(
A1:A20,
B1:B20,
HSTACK(
MAX,
MIN
),
3,
0
)
else:
=LET(
a,
SORT(
A2:B20,
2
),
u,
UNIQUE(
TAKE(
a,
,
1
)
),
x,
LAMBDA(
x,
XLOOKUP(
u,
TAKE(
a,
,
1
),
DROP(
a,
,
1
),
,
,
x
)
),
VSTACK(
{"Zone",
"Max",
"Min"},
SORT(
HSTACK(
u,
x(
-1
),
x(
1
)
),
1
)
)
)Excel solution 23 for Find Min and Max for all zones, proposed by Ziad A.:
=QUERY(
A1:B20,
"SELECT A, MAX(B), MIN(B) GROUP BY A"
)
MAP:
=SORT(
LET(
z,
A2:A20,
s,
B2:B20,
MAP(
UNIQUE(
z
),
LAMBDA(
u,
{u,
MAXIFS(
s,
z,
u
),
MINIFS(
s,
z,
u
)}
)
)
)
)Excel solution 24 for Find Min and Max for all zones, proposed by Giorgi Goderdzishvili:
=LET(
_zn,
A2:A20,
_sl,
B2:B20,
_un,
UNIQUE(
_zn
),
_mx,
MAXIFS(
_sl,
_zn,
_un
),
_mn,
MINIFS(
_sl,
_zn,
_un
),
VSTACK(
HSTACK(
"Zone",
"Max",
"Min"
),
HSTACK(
_un,
_mx,
_mn
)
)
)Excel solution 25 for Find Min and Max for all zones, proposed by Edwin Tisnado:
=LET(
x,
A2:A20,
y,
B2:B20,
u,
SORT(
UNIQUE(
x
)
),
VSTACK(
{"Zone",
"Max",
"Min"},
HSTACK(
u,
MAXIFS(
y,
x,
u
),
MINIFS(
y,
x,
u
)
)
)
)Excel solution 26 for Find Min and Max for all zones, proposed by Abdelrahman Omer, MBA, PMP:
=LET(
a,
A2:A20,
b,
B2:B20,
c,
SORT(
UNIQUE(
a
)
),
VSTACK(
{"Zone",
"Max",
"Min"},
HSTACK(
c,
MAP(
c,
LAMBDA(
x,
MAX(
FILTER(
b,
a=x
)
)
)
),
MAP(
c,
LAMBDA(
x,
MIN(
FILTER(
b,
a=x
)
)
)
)
)
)
)Excel solution 27 for Find Min and Max for all zones, proposed by Hazem Hassan:
=SORT(
LET(
a,
A2:A20,
b,
UNIQUE(
a
),
c,
B2:B20,
HSTACK(
b,
MAXIFS(
c,
a,
b
),
MINIFS(
c,
a,
b
)
)
)
)Excel solution 28 for Find Min and Max for all zones, proposed by Gabriel Raigosa:
=VSTACK(
{"Zone",
"Max",
"Min"},
LET(
z,
A2:A20,
s,
B2:B20,
o,
SORT(
UNIQUE(
z
)
),
HSTACK(
o,
MAXIFS(
s,
z,
o
),
MINIFS(
s,
z,
o
)
)
)
)
▶️ES: =APILARV(
{"Zone",
"Max",
"Min"},
LET(
z,
A2:A20,
s,
B2:B20,
o,
ORDENAR(
UNICOS(
z
)
),
APILARH(
o,
MAX.SI.CONJUNTO(
s,
z,
o
),
MIN.SI.CONJUNTO(
s,
z,
o
)
)
)
)Excel solution 29 for Find Min and Max for all zones, proposed by Enrico Giorgi:
=HSTACK(MAXIFS($B$2:$B$20,$A$2:$A$20,D3:D6),MINIFS($B$2:$B$20,$A$2:$A$20,D3:D6))
🇮🇹Italian version
=STACK.ORIZ(MAX.PIÙ.SE($B$2:$B$20;$A$2:$A$20;D3:D6);MIN.PIÙ.SE($B$2:$B$20;$A$2:$A$20;D3:D6))Excel solution 30 for Find Min and Max for all zones, proposed by Michael Hengst:
="Zone";EINDEUTIG(
A2:A20
);WENN(
O16:Q16="Max";MAXWENNS(
B2:B20;A2:A20;EINDEUTIG(
A2:A20
)
);MINWENNS(
B2:B20;A2:A20;EINDEUTIG(
A2:A20
)
)
)))
ENGLISH (untested):
SORT(
IF(
O16:Q16="Zone",
UNIQUE(
A2:A20
),
IF(
O16:Q16="Max",
MAXIFS(
B2:B20,
A2:A20,
UNIQUE(
A2:A20
)
),
MINIFS(
B2:B20,
A2:A20,
UNIQUE(
A2:A20
)
)
)
)
)Excel solution 31 for Find Min and Max for all zones, proposed by Hammed Toheeb:
=HSTACK(
SORT(
UNIQUE(
A2:A20
)
),
MAXIFS(
B2:B20,
A2:A20,
SORT(
UNIQUE(
A2:A20
)
)
),
MINIFS(
B2:B20,
A2:A20,
SORT(
UNIQUE(
A2:A20
)
)
)
)Excel solution 32 for Find Min and Max for all zones, proposed by Greg Wagland, CFRE:
=SORT(UNIQUE(A2:A20),1,1,FALSE)
=MAXIFS(B:B,A:A,H3)
=MINIFS(B:B,A:A,H3)Excel solution 33 for Find Min and Max for all zones, proposed by Avadhesh Chaurasiya:
=QUERY(
{D6:E13},
"Select Col1,max(Col2),min(Col2) group by Col1 label max(Col2)'',min(Col2)'' "
)Solving the challenge of Find Min and Max for all zones with Python in Excel
Python in Excel solution 1 for Find Min and Max for all zones, proposed by John V.:
Hi everyone!
One [Python] option with Exact headers and shape:
Blessings!
Python in Excel solution 2 for Find Min and Max for all zones, proposed by Owen Price:
Python is one line:
xl("A1:B20", headers=True).groupby('Zone').Sales.agg(['max','min'])Solving the challenge of Find Min and Max for all zones with R
R solution 1 for Find Min and Max for all zones, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(data.table)
input = read_excel("Max Min.xlsx", range = "A1:B20")
test = read_excel("Max Min.xlsx", range = "D2:F6")
result = input %>%
group_by(Zone) %>%
summarise(Max = max(Sales), Min = min(Sales))
identical(test, result)
#> [1] TRUE
input_dt = setDT(input)
result_dt = input_dt[, .(Max = max(Sales), Min = min(Sales)), by = Zone][order(Zone)]
result_dt = as_tibble(result_dt)
identical(test, result_dt)
#> [1] TRUE
&&