This challenge was posed by Sunny Baggu List the rows from problem table as per column H.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 468
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sort Rows by Reference Column with Power Query
Power Query solution 1 for Sort Rows by Reference Column, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Cols = List.Select(Table.ColumnNames(Source), each Text.Select(_, {"A" .. "Z"}) = "C"),
Records = List.TransformMany(
Cols,
(x) => {Table.Min(Source, x), Table.Max(Source, x)},
(x, y) => y
),
Return = Table.FromRecords(Records)
in
ReturnPower Query solution 2 for Sort Rows by Reference Column, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
MinMax = List.Combine(
List.Transform(
{0 .. Table.ColumnCount(Source) - 3},
each {
List.Min(List.Skip(Table.ToColumns(Source), 2){_}),
List.Max(List.Skip(Table.ToColumns(Source), 2){_})
}
)
),
Sel = Table.AddColumn(
Source,
"N",
each List.PositionOf(
List.Transform(MinMax, (y) => List.Contains(List.Skip(Record.ToList(_), 2), y)),
true,
2
)
),
Sort = Table.Sort(
Table.SelectRows(Table.ExpandListColumn(Sel, "N"), each ([N] <> null)),
{{"N", Order.Ascending}}
),
Sol = Table.RemoveColumns(Sort, {"N"})
in
SolPower Query solution 3 for Sort Rows by Reference Column, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Columns = {"C1", "C2", "C3", "C4"},
NewTable = Table.FromRecords({}),
ResultTable = List.Accumulate(
Columns,
NewTable,
(x, y) =>
let
a = Table.First(Table.Sort(Source, {{y, Order.Ascending}})),
b = Table.First(Table.Sort(Source, {{y, Order.Descending}}))
in
Table.Combine({x, Table.FromRecords({a, b})})
)
in
ResultTablePower Query solution 4 for Sort Rows by Reference Column, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
C = Table.ColumnCount(S) - 2,
R = Table.RowCount(S),
a = List.LastN(Table.ColumnNames(S), C),
b = List.FirstN(
List.Generate(
() => [i = 0, j = 0],
each [i] < C,
each if [j] = R - 1 then [i = [i] + 1, j = 0] else [i = [i], j = [j] + 1],
each Table.MinN(S, a{[j]}, 1)
),
C
),
c = List.FirstN(
List.Generate(
() => [i = 0, j = 0],
each [i] < C,
each if [j] = R - 1 then [i = [i] + 1, j = 0] else [i = [i], j = [j] + 1],
each Table.MaxN(S, a{[j]}, 1)
),
C
),
Sol = Table.Combine(
List.Generate(() => [i = 0], each [i] < C, each [i = [i] + 1], each b{[i]} & c{[i]})
)
in
SolPower Query solution 5 for Sort Rows by Reference Column, proposed by Venkata Rajesh:
let
Source = Data,
Output = [
unpivot = Table.UnpivotOtherColumns(Source, {"Data1", "Data2"}, "Columns", "Value"),
columns = List.Distinct(unpivot[Columns]),
Result = List.Accumulate(
columns,
Table.SelectRows(Source, each 1 = 2),
(state, current) =>
[
value = Table.SelectRows(unpivot, each [Columns] = current)[Value],
fx = (x) => Table.SelectRows(Source, each Record.Field(_, current) = x),
combine = Table.Combine({state, fx(List.Min(value)), fx(List.Max(value))})
][combine]
)
][Result]
in
OutputSolving the challenge of Sort Rows by Reference Column with Excel
Excel solution 1 for Sort Rows by Reference Column, proposed by Bo Rydobon 🇹🇭:
=LET(
L,
LAMBDA(
i,
BYCOL(
C3:F20,
LAMBDA(
x,
XMATCH(
-i*9^9,
x,
i
)
)
)
),
CHOOSEROWS(
A3:F20,
TOCOL(
VSTACK(
L(
1
),
L(
-1
)
),
,
1
)
)
)Excel solution 2 for Sort Rows by Reference Column, proposed by Bo Rydobon 🇹🇭:
=BMAPλ(SEQUENCE(
4
),
LAMBDA(
n,
CHOOSEROWS(
SORT(
A3:F20,
n+2
),
1,
-1
)
))Excel solution 3 for Sort Rows by Reference Column, proposed by Bo Rydobon 🇹🇭:
=REDUCE(
A2:F2,
SEQUENCE(
4
),
LAMBDA(
a,
n,
VSTACK(
a,
CHOOSEROWS(
SORT(
A3:F20,
n+2
),
1,
-1
)
)
)
)Excel solution 4 for Sort Rows by Reference Column, proposed by Rick Rothstein:
=LET(
z,
LAMBDA(
x,
VSTACK(
XLOOKUP(
MIN(
x
),
x,
A:F
),
XLOOKUP(
MAX(
x
),
x,
A:F
)
)
),
VSTACK(
z(
C:C
),
z(
D:D
),
z(
E:E
),
z(
F:F
)
)
)Excel solution 5 for Sort Rows by Reference Column, proposed by Rick Rothstein:
=LET(
z,
LAMBDA(
x,
LET(
s,
SORT(
A3:F20,
x
),
VSTACK(
TAKE(
s,
1
),
TAKE(
s,
-1
)
)
)
),
VSTACK(
z(
3
),
z(
4
),
z(
5
),
z(
6
)
)
)Excel solution 6 for Sort Rows by Reference Column, proposed by Rick Rothstein:
=LET(
z,
LAMBDA(
x,
LET(
s,
SORT(
A3:F20,
x
),
CHOOSEROWS(
s,
1,
-1
)
)
),
VSTACK(
z(
3
),
z(
4
),
z(
5
),
z(
6
)
)
)Excel solution 7 for Sort Rows by Reference Column, proposed by Rick Rothstein:
=CHOOSEROWS(
REDUCE(
A2:F2,
{3,
4,
5,
6},
LAMBDA(
a,
x,
VSTACK(
a,
SORT(
A3:F20,
x
)
)
)
),
1,
2,
19,
20,
37,
38,
55,
56,
73
)Excel solution 8 for Sort Rows by Reference Column, proposed by John V.:
=REDUCE(
A2:F2,
ROW(
3:6
),
LAMBDA(
a,
v,
VSTACK(
a,
CHOOSEROWS(
SORT(
A3:F20,
v
),
1,
-1
)
)
)
)Excel solution 9 for Sort Rows by Reference Column, proposed by محمد حلمي:
=REDUCE(
A2:F2,
H3:H10,
LAMBDA(
a,
v,
LET(
r,
TOCOL(
FIND(
C2:F2,
v
)^0*C3:F20,
2
),
VSTACK(
a,
FILTER(
A3:F20,
r=IF(
LEFT(
v,
2
)="Mi",
MIN(
r
),
MAX(
r
)
)
)
)
)
)
)Excel solution 10 for Sort Rows by Reference Column, proposed by Kris Jaganah:
=REDUCE(
A2:F2,
H3:H10,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
A3:F20,
b,
XLOOKUP(
MID(
y,
5,
2
),
TAKE(
x,
1
),
a
),
FILTER(
a,
IF(
MID(
y,
2,
1
)="i",
MIN(
b
),
MAX(
b
)
)=b
)
)
)
)
)Excel solution 11 for Sort Rows by Reference Column, proposed by Julian Poeltl:
=LET(
T,
A2:F20,
TT,
DROP(
T,
1
),
D,
DROP(
TT,
,
2
),
MA,
BYCOL(
D,
LAMBDA(
A,
XMATCH(
MAX(
A
),
A
)
)
),
MI,
BYCOL(
D,
LAMBDA(
A,
XMATCH(
MIN(
A
),
A
)
)
),
VSTACK(
TAKE(
T,
1
),
CHOOSEROWS(
TT,
TOCOL(
VSTACK(
MI,
MA
),
,
TRUE
)
)
)
)Excel solution 12 for Sort Rows by Reference Column, proposed by Timothée BLIOT:
=DROP(
LET(
F,
LAMBDA(
n,
m,
INDEX(
A3:F20,
XMATCH(
m(
INDEX(
C3:F20,
,
n
)
),
INDEX(
C3:F20,
,
n
)
),
)
),
REDUCE(
"",
ROW(
1:4
),
LAMBDA(
w,
v,
VSTACK(
w,
VSTACK(
F(
v,
MIN
),
F(
v,
MAX
)
)
)
)
)
),
1
)Excel solution 13 for Sort Rows by Reference Column, proposed by Hussein SATOUR:
=DROP(
REDUCE(
"",
H3:H10,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
d,
A3:F20,
a,
INDEX(
d,
,
XMATCH(
MID(
y,
5,
2
),
C2:F2
)+2
),
b,
LEFT(
y,
3
),
CHOOSEROWS(
d,
XMATCH(
IF(
b="Min",
MIN(
a
),
MAX(
a
)
),
a
)
)
)
)
)
),
1
)Excel solution 14 for Sort Rows by Reference Column, proposed by Sunny Baggu:
=LAMBDA(
z,
LET(
v,
DROP(
z,
,
2
),
_a,
BYCOL(
v,
LAMBDA(
a,
XMATCH(
MIN(
a
),
a
)
)
),
_b,
BYCOL(
v,
LAMBDA(
b,
XMATCH(
MAX(
b
),
b
)
)
),
CHOOSEROWS(
z,
TOCOL(
VSTACK(
_a,
_b
),
,
1
)
)
)
)(A3:F20)Excel solution 15 for Sort Rows by Reference Column, proposed by LEONARD OCHEA 🇷🇴:
=REDUCE(
A2:F2,
SEQUENCE(
4
),
LAMBDA(
a,
b,
LET(
d,
A3:F20,
i,
INDEX(
d,
,
b+2
),
VSTACK(
a,
VSTACK(
XLOOKUP(
MIN(
i
),
i,
d
),
XLOOKUP(
MAX(
i
),
i,
d
)
)
)
&)
)
)Excel solution 16 for Sort Rows by Reference Column, proposed by Abdallah Ally:
=LET(
a,
A3:F20,
REDUCE(
A2:F2,
COLUMN(
C:F
),
LAMBDA(
x,
y,
LET(
b,
CHOOSECOLS(
a,
y
),
VSTACK(
x,
FILTER(
a,
b=MIN(
b
)
),
FILTER(
a,
b=MAX(
b
)
)
)
)
)
)
)Excel solution 17 for Sort Rows by Reference Column, proposed by Asheesh Pahwa:
=REDUCE(
A2:F2,
SEQUENCE(
,
COLUMNS(
C3:F20
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
I,
INDEX(
C3:F20,
,
y
),
mn,
MIN(
I
),
mx,
MAX(
I
),
fn,
FILTER(
A3:F20,
I=mn
),
fx,
FILTER(
A3:F20,
I=mx
),
VSTACK(
fn,
fx
)
)
)
)
)Excel solution 18 for Sort Rows by Reference Column, proposed by Andy Heybruch:
=TEXTSPLIT(TEXTJOIN("|",
,
MAP(--TEXTSPLIT(
TEXTJOIN(
"|",
,
BYCOL(
C3:F20,
LAMBDA(
a,
MIN(
a
)&"|"&MAX(
a
)
)
)
),
,
"|"
),
LAMBDA(x,
ARRAYTOTEXT(FILTER(A3:F20,
BYROW(C3:F20,
LAMBDA(a,
SUM(BYCOL(a,
LAMBDA(b,
--(b=x))))))))))),
",",
"|")
a better way creating an index
=LET(
_rng,
C3:F20,
_min,
BYCOL(
_rng,
LAMBDA(
a,
XMATCH(
MIN(
a
),
a,
0
)
)
),
_max,
BYCOL(
_rng,
LAMBDA(
a,
XMATCH(
MAX(
a
),
a,
0
)
)
),
_index,
TOCOL(
HSTACK(
TOCOL(
_min
),
TOCOL(
_max
)
)
),
DROP(
REDUCE(
"",
_index,
LAMBDA(
a,
v,
VSTACK(
a,
INDEX(
A3:F20,
v,
)
)
)
),
1
)
)
similar using an index but without the reduce
=LET(
_rng,
C3:F20,
_min,
BYCOL(
_rng,
LAMBDA(
a,
XMATCH(
MIN(
a
),
a,
0
)
)
),
_max,
BYCOL(
_rng,
LAMBDA(
a,
XMATCH(
MAX(
a
),
a,
0
)
)
),
_index,
TOCOL(
HSTACK(
TOCOL(
_min
),
TOCOL(
_max
)
)
),
INDEX(
A3:F20,
_index,
SEQUENCE(
,
6
)
)
)Excel solution 19 for Sort Rows by Reference Column, proposed by Milan Shrimali:
="MAX",
1,
2),
CEL,
MAP(
Z,
LAMBDA(
U,
RIGHT(
U,
2
)
)
),
RNNG1,
IFS(
CEL="C1",
1+2,
CEL="C2",
2+2,
CEL="C3",
3+2,
CEL="C4",
4+2
),
COLL1,
CHOOSECOLS(
DATA,
RNNG1
),
MXMN,
IF(
FCCT=1,
MAX(
COLL1
),
MIN(
COLL1
)
),
INDEX(
DATA,
MATCH(
MXMN,
COLL1,
0
)
))))Excel solution 20 for Sort Rows by Reference Column, proposed by Peter Bartholomew:
= LET(
recordNum,
TOCOL(
VSTACK(
BYCOL(
values,
LAMBDA(
v,
XMATCH(
MIN(
v
),
v
)
)
),
BYCOL(
values,
LAMBDA(
v,
XMATCH(
MAX(
v
),
v
)
)
)
),
,
TRUE
),
INDEX(
table,
recordNum,
{1,
2,
3,
4,
5,
6}
)
)Excel solution 21 for Sort Rows by Reference Column, proposed by Peter Bartholomew:
=LAMBDA(
v,
VSTACK(
XLOOKUP(
MIN(
v
),
TRANSPOSE(
v
),
table
),
XLOOKUP(
MAX(
v
),
TRANSPOSE(
v
),
table
)
)
);
The worksheet formula is then
= BYROWλ(TRANSPOSE(
values
),
ExtremeEventsλ)
The interesting part when combining pairs of thunks picked from the thunk array using MAP
LAMBDA(ϑ₁,
ϑ₂,
LET(
x₁,
(@ϑ₁)(),
x₂,
(@ϑ₂)(),
v,
IF(
@x₂="",
x₁,
VSTACK(
x₁,
x₂
)
),
LAMBDA(
v
)
)
)
The "@" is needed to convert a 1x1 array (type=64)Excel solution 22 for Sort Rows by Reference Column, proposed by Sandeep Marwal:
=MAKEARRAY(
8,
6,
LAMBDA(
r,
c,
INDEX(
XLOOKUP(
IF(
ISODD(
r
),
-100000,
100000
),
XLOOKUP(
INDEX(
C2:F2,
,
ROUNDUP(
r/2,
0
)
),
$C$2:$F$2,
$C$3:$F$20
),
$A$3:$F$20,
,
IF(
ISODD(
r
),
1,
-1
)
),
1,
c
)
)
)Excel solution 23 for Sort Rows by Reference Column, proposed by Anup Kumar:
=DROP(
REDUCE(
"",
SCAN(
"",
H3:H10,
LAMBDA(
x,
y,
ARRAYTOTEXT(
LET(
st,
y,
hd,
$A$2:$F$2,
dt,
$A$3:$F$20,
cl,
MID(
st,
5,
2
),
xl,
XLOOKUP(
cl,
hd,
dt
),
IF(
MID(
st,
2,
1
)="I",
INDEX(
dt,
XMATCH(
MIN(
xl
),
xl
),
,
1
),
INDEX(
dt,
XMATCH(
MAX(
xl
),
xl
),
,
1
)
)
)
)
)
),
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
","
)
)
)
),
1
)Excel solution 24 for Sort Rows by Reference Column, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=REDUCE(
HSTACK(
"",
A2:F2
),
C2:F2,
LAMBDA(
a,
x,
VSTACK(
a,
HSTACK(
VSTACK(
"Min "&x&" row",
"Max "&x&" row"
),
CHOOSEROWS(
SORTBY(
A3:F20,
CHOOSECOLS(
C3:F20,
XMATCH(
x,
C2:F2
)
)
),
1,
ROWS(
A3:F20
)
)
)
)
)
)Excel solution 25 for Sort Rows by Reference Column, proposed by Tyler Cameron:
=VSTACK(
A2:F2,
CHOOSEROWS(
A3:F20,
TOCOL(
HSTACK(
TOCOL(
BYCOL(
C3:F20,
LAMBDA(
x,
XMATCH(
MIN(
x
),
x
)
)
)
),
TOCOL(
BYCOL(
C3:F20,
LAMBDA(
x,
XMATCH(
MAX(
x
),
x
)
)
)
)
)
)
)
)
and for a bit of fun Just stacking each row
=LET(
z,
LAMBDA(
x,
y,
LET(
a,
A3:F20,
b,
CHOOSECOLS(
a,
x
),
FILTER(
a,
b=IF(
y,
MIN(
b
),
MAX(
b
)
)
)
)
),
VSTACK(
A2:F2,
z(
3,
1
),
z(
3,
0
),
z(
4,
1
),
z(
4,
0
),
z(
5,
1
),
z(
5,
0
),
z(
6,
1
),
z(
6,
0
)
)
)Excel solution 26 for Sort Rows by Reference Column, proposed by Caroline Blake:
=LET(
a,
A2:F20,
s,
LAMBDA(
x,
CHOOSECOLS(
a,
x
)
),
f,
LAMBDA(
x,
VSTACK(
FILTER(
a,
s(
x
)=MIN(
s(
x
)
)
),
FILTER(
a,
s(
x
)=MAX(
s(
x
)
)
)
)
),
VSTACK(
f(
3
),
f(
4
),
f(
5
),
f(
6
)
)
)Solving the challenge of Sort Rows by Reference Column with Python
Python solution 1 for Sort Rows by Reference Column, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel("467 Generate Min and Max Rows.xlsx", usecols="A:F", skiprows = 1, nrows = 19)
test = pd.read_excel("467 Generate Min and Max Rows.xlsx", usecols = "I:N", skiprows = 1, nrows = 8)
test.columns = test.columns.str.replace('.1', '')
inst = pd.read_excel("467 Generate Min and Max Rows.xlsx", usecols= "H", skiprows= 2, nrows = 8, header=None)
inst.columns = ['Inst']
inst['Inst'] = inst['Inst'].str[:6]
inst[['fun', 'column']] = inst['Inst'].str.split(' ', expand=True)
inst['fun'] = inst['fun'].str.lower()
r2 = inst.copy()
r2['index'] = r2.apply(lambda row: input[row['column']].idxmin() if row['fun'] == 'min' else input[row['column']].idxmax(), axis=1)
result = input.loc[r2['index']].reset_index(drop=True)
print(result.equals(test)) # True
Solving the challenge of Sort Rows by Reference Column with Python in Excel
Python in Excel solution 1 for Sort Rows by Reference Column, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_467 - Generate Min and Max Rows.xlsx'
df = pd.read_excel(file_path, usecols='A:F', skiprows=1)
# Perform data wrangling
dfs = [pd.concat([df[df[x] == min(df[x])], df[df[x] == max(df[x])]]) for x in df.columns[2 : ]]
df = pd.concat(dfs)
df.index = [x for y in df.columns[2 : ] for x in (f'Min {y} row', f'Max {y} row')]
df
Solving the challenge of Sort Rows by Reference Column with R
R solution 1 for Sort Rows by Reference Column, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/467 Generate Min and Max Rows.xlsx", range = "A2:F20")
test = read_excel("Excel/467 Generate Min and Max Rows.xlsx", range = "I2:N10")
ins&t = read_excel("excel/467 Generate Min and Max Rows.xlsx", range = "H3:H10", col_names = "Inst")
r1 = inst %>%
mutate(Inst = str_sub(Inst,1,6)) %>%
separate(Inst, into = c("fun", "column"), sep = " ", remove = F) %>%
mutate(fun = str_to_lower(fun))
r2 = r1 %>%
mutate(index = ifelse(fun == "min",
map_int(column, ~which.min(input[[.x]])),
map_int(column, ~which.max(input[[.x]]))))
result = map_dfr(r2$index, ~input[.x,])
identical(result, test)
# [1] TRUE
R solution 2 for Sort Rows by Reference Column, proposed by Anil Kumar Goyal:
library(readxl)
library(tidyverse)
df <- read_excel("Excel/Excel_Challenge_467 - Generate Min and Max Rows.xlsx",
range = cell_cols(LETTERS[1:6]))
df %>%
colnames() %>%
str_subset(regex("^C")) %>%
map_dfr(~ bind_rows(
slice_min(df, order_by = get(.x), n = 1),
slice_max(df, order_by = get(.x), n = 1)
))
&