— Looks like scheduling didn’t work today and challenge didn’t get posted— Remove the last minimum number from given strings. Ex. 2, 8, 2, 4, 2, 5 2 is minimum and last 2 is between 4 & 5. Hence, answer would be 2, 8, 2, 4, 5.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 328
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Remove last occurrence of minimum number with Power Query
Power Query solution 1 for Remove last occurrence of minimum number, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.AddColumn(
Source,
"Answer Expected",
each
let
n = Text.Split([String], ", ")
in
Text.Combine(
List.Transform(
List.Difference(
List.Positions(n),
{List.PositionOf(n, List.Min(n, 0, each Number.From(_)), 1)}
),
each n{_}
),
", "
)
)
in
SPower Query solution 2 for Remove last occurrence of minimum number, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.Split([String], ", "),
b = List.Transform(a, Number.From),
c = List.Min(b),
d = List.Zip({b, List.Positions(b)}),
e = List.Last(List.Select(d, each _{0} = c)),
f = List.Transform(List.RemoveItems(d, {e}), each Text.From(_{0}))
in
Text.Combine(f, ", ")
)[[Answer]]
in
SolPower Query solution 3 for Remove last occurrence of minimum number, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = List.Transform(Text.Split([String], ", "), Number.From),
b = Text.Combine(
List.Transform(List.RemoveRange(a, List.Max(List.PositionOf(a, List.Min(a), 2))), Text.From),
", "
)
][b]
)
in
resPower Query solution 4 for Remove last occurrence of minimum number, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = Table.AddColumn(
Table.TransformColumnTypes(Origen, {{"String", type text}}),
"A",
each Text.Split([String], ",")
),
b = Table.AddColumn(
Table.TransformColumns(a, {"A", each List.Transform(_, Number.From)}),
"B",
each {List.Min([A])}
),
c = Table.AddColumn(b, "Answer Expected", each List.Difference(List.Reverse([A]), [B]))[
[Answer Expected]
],
Sol = Table.TransformColumns(
c,
{"Answer Expected", each Text.Combine(List.Transform(List.Reverse(_), Text.From), ", ")}
)
in
SolSolving the challenge of Remove last occurrence of minimum number with Excel
Excel solution 1 for Remove last occurrence of minimum number, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
b,
--TEXTSPLIT(
a,
,
","
),
c,
b-SEQUENCE(
ROWS(
b
)
)%,
TEXTJOIN(
", ",
,
REPT(
b,
c<>MIN(
c
)
)
)
)
)
)Excel solution 2 for Remove last occurrence of minimum number, proposed by Rick Rothstein:
=MAP(
SUBSTITUTE(
A2:A7,
",",
""
),
LAMBDA(
a,
LET(
m,
MIN(
0+TEXTSPLIT(
a,
" "
)
),
c,
COUNTA(
TEXTSPLIT(
" "&a&" ",
" "&m&" "
)
)-1,
SUBSTITUTE(
TRIM(
SUBSTITUTE(
a,
m,
" ",
c
)
),
" ",
", "
)
)
)
)Excel solution 3 for Remove last occurrence of minimum number, proposed by John V.:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
d,
", ",
n,
--TEXTSPLIT(
x,
d
),
m,
MIN(
n
),
MID(
SUBSTITUTE(
d&x,
d&m,
,
SUM(
N(
m=n
)
)
),
3,
99
)
)
)
)
✅ =MAP(
A2:A7,
LAMBDA(
x,
LET(
n,
TEXTSPLIT(
x,
,
","
),
b,
n-SEQUENCE(
ROWS(
n
)
)%,
TEXTJOIN(
",",
,
IF(
b=MIN(
b
),
"",
n
)
)
)
)
)Excel solution 4 for Remove last occurrence of minimum number, proposed by محمد حلمي:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
c,
--TEXTSPLIT(
a,
,
","
),
TEXTJOIN(
", ",
,
REPT(
c,
SEQUENCE(
ROWS(
c
)
)<>XMATCH(
MIN(
c
),
c,
,
-1
)
)
)
)
)
)Excel solution 5 for Remove last occurrence of minimum number, proposed by محمد حلمي:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
c,
", ",
i,
--TEXTSPLIT(
a,
c
),
m,
MIN(
i
),
MID(
SUBSTITUTE(
c&a,
c&m,
,
SUM(
N(
m=i
)
)
),
3,
99
)
)
)
)Excel solution 6 for Remove last occurrence of minimum number, proposed by محمد حلمي:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
c,
", ",
i,
--TEXTSPLIT(
a,
c
),
m,
MIN(
i
),
MID(
SUBSTITUTE(
c&a,
c&m,
,
SUM(
N(
m=i
)
)
),
3,
99
)
)
)
)Excel solution 7 for Remove last occurrence of minimum number, proposed by Kris Jaganah:
=MAP(A2:A7,LAMBDA(x,LET(a,--TEXTSPLIT(x,,", "),b,SEQUENCE(ROWS(a)),c,IFNA(XMATCH(a,MIN(a)),0),ARRAYTOTEXT(FILTER(a,b*c<>MAX(b*c),"")))))Excel solution 8 for Remove last occurrence of minimum number, proposed by Julian Poeltl:
=MAP(
A2:A7,
LAMBDA(
S,
LET(
SP,
TEXTSPLIT(
S,
", "
)*1,
L,
COUNTA(
SP
),
MIN,
MIN(
SP
),
D,
XMATCH(
MIN,
SP,
,
-1
),
TEXTJOIN(
", ",
,
HSTACK(
IFERROR(
CHOOSECOLS(
SP,
SEQUENCE(
D-1
)
),
""
),
IFERROR(
CHOOSECOLS(
SP,
SEQUENCE(
L-D,
,
D+1
)
),
""
)
)
)
)
)
)Excel solution 9 for Remove last occurrence of minimum number, proposed by Timothée BLIOT:
=MAP(A2:A7,LAMBDA(z, LET(A,--TEXTSPLIT(z,,", "), B,SEQUENCE(ROWS(A)),
C,SORTBY(DROP(REDUCE(0,SORTBY(A,B,-1),LAMBDA(y,x, VSTACK(y,IF(SUM(y)=1,0,--(x=MIN(A)))))),1),B,-1),
D,TEXTJOIN(", ",,MAP(B,LAMBDA(x,IF(INDEX(C,x)=1,"",INDEX(A,x))))),D)))Excel solution 10 for Remove last occurrence of minimum number, proposed by Nikola Z Grujicic – Nikola Ž Grujičić:
=MAP(A2:A7,LAMBDA(a, LET(b, TEXTSPLIT(a, , ", "), c, SEQUENCE(ROWS(b)), d, INDEX(SORT(--b,,1,FALSE),1), e, FILTER(c, --b=d), f, INDEX(SORT(e, ,-1,FALSE),1),ARRAYTOTEXT(IF(COUNT(c)>1,FILTER(b, c<>f),d)))))Excel solution 11 for Remove last occurrence of minimum number, proposed by Hussein SATOUR:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
a,
--TEXTSPLIT(
x,
,
", "
),
IFERROR(
ARRAYTOTEXT(
FILTER(
a,
SEQUENCE(
COUNT(
a
)
) <> XMATCH(
MIN(
a
),
a,
,
-1
)
)
),
""
)
)
)
)Excel solution 12 for Remove last occurrence of minimum number, proposed by Sunny Baggu:
=MAP(
A2:A7,
LAMBDA(
a,
LET(
_ts,
TEXTSPLIT(
a,
,
", "
) + 0,
_seq,
SEQUENCE(
ROWS(
_ts
)
),
_loc,
XMATCH(
MIN(
_ts
),
_ts,
0,
-1
),
ARRAYTOTEXT(
FILTER(
_ts,
_seq <> _loc,
""
)
)
)
)
)Excel solution 13 for Remove last occurrence of minimum number, proposed by Abdallah Ally:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
a,
x,
b,
--TEXTSPLIT(
a,
", "
),
c,
MIN(
b
),
d,
COUNT(
FILTER(
b,
b=c
)
),
e,
TRIM(
IF(
COUNT(
b
)=1,
"",
SUBSTITUTE(
a,
c,
"",
d
)
)
),
TRIM(
IFS(
LEFT(
e
)=",",
RIGHT(
e,
LEN(
e
)-2
),
RIGHT(
e
)=",",
LEFT(
e,
LEN(
e
)-1
),
1,
SUBSTITUTE(
e,
", ,",
", "
)
)
)
)
)
)Excel solution 14 for Remove last occurrence of minimum number, proposed by Md. Zohurul Islam:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
a,
--TEXTSPLIT(
x,
,
", "
),
sq,
SEQUENCE(
COUNT(
a
)
),
b,
MAX(
FILTER(
sq,
a=MIN(
UNIQUE(
a
)
)
)
),
d,
ARRAYTOTEXT(
FILTER(
a,
sq<>b,
""
)
),
d
)
)
)Excel solution 15 for Remove last occurrence of minimum number, proposed by Asheesh Pahwa:
=LET(
arr,
E4:E9,
MAP(
arr,
LAMBDA(
x,
LET(
t,
--TEXTSPLIT(
x,
","
),
f,
IF(
COUNTA(
t
)=1,
"'",
LET(
m,
MIN(
t
),
s,
SEQUENCE(
& ,
COLUMNS(
t
)
),
x,
XLOOKUP(
m,
t,
s,
,
,
-1
),
r,
SEQUENCE(
COLUMNS(
t
)
),
I,
IF(
r=x,
"",
r
),
in,
INDEX(
t,
,
I
),
ARRAYTOTEXT(
TOCOL(
in,
3
)
)
)
),
f
)
)
)
)Excel solution 16 for Remove last occurrence of minimum number, proposed by Ankur Sharma:
=BYROW(
A2:A7,
LAMBDA(
a,
LET(
b,
TEXTSPLIT(
a,
", "
),
c,
COUNTA(
b
),
d,
VALUE(
b
),
TEXTJOIN(
", ",
TRUE,
FILTER(
b,
SEQUENCE(
,
c
) <> XMATCH(
MIN(
d
),
d,
,
-1
),
""
)
)
)
)
)Excel solution 17 for Remove last occurrence of minimum number, proposed by Owen Price:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
s,
--TEXTSPLIT(
x,
,
", "
),
TEXTJOIN(
", ",
,
FILTER(
s,
SEQUENCE(
ROWS(
s
)
) <> XMATCH(
MIN(
s
),
s,
,
-1
),
""
)
)
)
)
)Excel solution 18 for Remove last occurrence of minimum number, proposed by Pieter de Bruijn:
=MAP(A2:A7,LAMBDA(a,LET(t,--TEXTSPLIT(a,,","),TEXTJOIN(", ",1,REPT(t,SEQUENCE(ROWS(t))<>XMATCH(MIN(t),t,,-1))))))
At first I thought in case of 1 number it needn't remove the character:
=MAP(A2:A7,LAMBDA(a,LET(t,--TEXTSPLIT(a,,","),s,SEQUENCE(ROWS(t)),IFERROR(ARRAYTOTEXT(INDEX(t,TOROW(s/(s<>XMATCH(MIN(t),t,,-1)),2))),a))))Excel solution 19 for Remove last occurrence of minimum number, proposed by Nicolas Micot:
=LET(_nombres;FRACTIONNER.TEXTE(A2;",")+0;
_pos;SEQUENCE(1;COLONNES(_nombres));
_posMin;EQUIVX(MIN(_nombres);_nombres;0;-1);
JOINDRE.TEXTE(", ";VRAI;SI(_pos=_posMin;"";_nombres)))Excel solution 20 for Remove last occurrence of minimum number, proposed by Ziad A.:
=MAP(A2:A7,LAMBDA(a,REGEXREPLACE(a,"((.*), )?"&MIN(SPLIT(a,",")),"$2")))Excel solution 21 for Remove last occurrence of minimum number, proposed by Giorgi Goderdzishvili:
=
MAP(
A2:A7,
LAMBDA(
x,
LET(
str,
x,
spl,
-- TEXTSPLIT(
str,
", "
),
mn,
MIN(
spl
),
ps,
XMATCH(
TRUE,
mn=spl,
0,
-1
),
fn,
FILTER(
spl,
SEQUENCE(
,
COLUMNS(
spl
)
)<>ps
),
IFERROR(
TEXTJOIN(
", ",
,
fn
),
""
)
)
)
)Excel solution 22 for Remove last occurrence of minimum number, proposed by Edwin Tisnado:
=MAP(A2:A7,
LAMBDA(x,
LET(a,
--TEXTSPLIT(
x,
", "
),
b,
MIN(
a
),
c,
SUM(--(a=b)),
d,
COUNT(
a
)-1,
y,
TEXTJOIN(
",",
,
a
)&",",
t,
SUBSTITUTE(
y,
b&",",
"",
c
),
IFERROR(
TEXTBEFORE(
t,
",",
d
),
t
))))Excel solution 23 for Remove last occurrence of minimum number, proposed by Abdelrahman Omer, MBA, PMP:
=IFERROR(
MAP(
A2:A7,
LAMBDA(
a,
LET(
b,
--TEXTSPLIT(
a,
,
", "
),
c,
BYROW(
b,
LAMBDA(
x,
MIN(
b
)=x
)
)
)*SEQUENCE(
COUNTA(
b
)
),
ARRAYTOTEXT(
FILTER(
b,
c<>MAX(
c
)
)
)
)
)
),
"")Excel solution 24 for Remove last occurrence of minimum number, proposed by Anup Kumar:
=MAP(A2:A7,LAMBDA(x,LET(
s,TEXTSPLIT(x,,", "),
t, HSTACK(s*1,SEQUENCE(COUNTA(s))),
IFERROR(ARRAYTOTEXT(TAKE(SORT(DROP(SORT(t,1,-1),-1),2),,1)),"")
))Excel solution 25 for Remove last occurrence of minimum number, proposed by Hazem Hassan:
=XMATCH(
MIN(
a
),
a,
,
-1
),
"",
a),
"")))))Excel solution 26 for Remove last occurrence of minimum number, proposed by Gabriel Raigosa:
=MAP(A2:A7,LAMBDA(x,LET(n,--TEXTSPLIT(x,","),TEXTJOIN(", ",,FILTER(n,SEQUENCE(,COUNT(n))<>MATCH(1,--(n=MIN(n))),"")))))
🔹ES:
=MAP(A2:A7,LAMBDA(x,LET(n,--DIVIDIRTEXTO(x,","),UNIRCADENAS(", ",,FILTRAR(n,SECUENCIA(,CONTAR(n))<>COINCIDIR(1,--(n=MIN(n))),"")))))Excel solution 27 for Remove last occurrence of minimum number, proposed by Sandro Barsonidze:
=LET(
split, VALUE(TEXTSPLIT(A2,",")),
min, MIN(split),
join, TEXTJOIN(",",TRUE,split),
k, LEN(join)-LEN(SUBSTITUTE(join,min,"")),
x, IF(min<10,k,IF(min<100,k/2,k/3)),
min_droped, SUBSTITUTE(join,min,"",x),
answer, IFERROR(TEXTJOIN(",",TRUE,TEXTSPLIT(min_droped,",")),""),
answer)Solving the challenge of Remove last occurrence of minimum number with Python in Excel
Python in Excel solution 1 for Remove last occurrence of minimum number, proposed by Alejandro Campos:
df = xl("A1:A7", headers=True)
def remove_last_min(s):
nums = list(map(int, s.split(", ")))
min_val = min(nums)
min_indices = [i for i, x in enumerate(nums) if x == min_val]
if min_indices:
nums.pop(min_indices[-1])
return ", ".join(map(str, nums))
df["String"] = df["String"].apply(remove_last_min)
df
Solving the challenge of Remove last occurrence of minimum number with R
R solution 1 for Remove last occurrence of minimum number, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Remove Minimum.xlsx", range = "A1:A7")
test = read_excel("Remove Minimum.xlsx", range = "B1:B7")
remove_last_min <- function(x) {
nums <- str_split(x, ",\s*") %>%
map(~as.numeric(.x)) %>%
.[[1]]
min_num <- min(nums)
last_min_index <- max(which(nums == min_num))
modified_nums <- nums[-last_min_index]
if (length(modified_nums) == 0) {
NA_character_
} else {
str_c(modified_nums, collapse = ", ")
}
}
result = input %>%
mutate(result = map_chr(String, remove_last_min))
R solution 2 for Remove last occurrence of minimum number, proposed by Krzysztof Nowak:
RemoveLastMin <- function(x) {
NumberConversion <- as.numeric(unlist(str_split(x,",")))
min_value <- min(NumberConversion)
which(NumberConversion == min_value)
last_index <- tail(which(NumberConversion == min_value), 1)
RemoveIndex <- NumberConversion[-last_index]
result <- paste(unlist(str_split(RemoveIndex," ")),collapse = ",")
print(result)
}
df$Answer <- sapply(df$String,RemoveLastMin)
&&