Find the next number which is greater and made from arranging digits from that number. If no such number is possible, write No such number. Ex. 405 => Next number greater than 405 is 450 which is made from same digits. 4332 = There is no such number as 4332 itself is the largest number from these digits.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 470
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Next Larger Permutation with Power Query
Power Query solution 1 for Next Larger Permutation, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.AddColumn(
Source,
"Ans",
each
let
t = Text.From([Number]),
b = Text.ToList(t),
n = List.Last(
List.PositionOf(List.Transform(List.Zip({b, List.Skip(b)}), each _{0} < _{1}), true, 2)
),
e = List.Select(List.Sort(List.Skip(b, n + 1)), each _ > b{n}){0},
f = Text.Combine(List.Sort(List.Skip(b, n)))
in
try
Number.From(Text.Start(t, n) & e & Text.ReplaceRange(f, Text.PositionOf(f, e), 1, ""))
otherwise
"No such number"
)
in
AnsPower Query solution 2 for Next Larger Permutation, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
T = Text.From([Number]),
S = Text.ToList(T),
O = List.Sort(S, 1),
G = List.Generate(
() => [Number] + 1,
(f) => List.Sort(Text.ToList(Text.From(f)), 1) <> O,
(f) => f + 1
),
R = if S = O then "No such number" else List.Last(G) + 1
][R]
)
in
ReturnPower Query solution 3 for Next Larger Permutation, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
G = (f, g) =>
let
a = List.Transform({f, g}, each List.Sort(Text.ToList(Text.From(_)))),
b = if f - g > 1000 then "No Such Number" else if a{0} = a{1} then f else @G(f + 1, g)
in
b,
Sol = Table.AddColumn(Source, "Answer", each G([Number] + 1, [Number]))
in
SolPower Query solution 4 for Next Larger Permutation, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
f = (x) =>
let
g = (y) => Number.From(Text.Combine(List.Sort(Text.ToList(Text.From(y)), 1))),
a = g(x),
b = List.Numbers(x + 1, a - x),
c = if x = a then "No such number" else List.First(List.Select(b, each g(_) = a))
in
c,
ResultTable = Table.AddColumn(Source, "My Answer", each f([Number]))
in
ResultTableSolving the challenge of Next Larger Permutation with Excel
Excel solution 1 for Next Larger Permutation, proposed by Bo Rydobon 🇹🇭:
=IFNA(
--MAP(
A2:A10,
LAMBDA(
a,
LET(
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
n,
XMATCH(
TRUE,
bExcel solution 2 for Next Larger Permutation, proposed by Bo Rydobon 🇹🇭:
=IFNA(
--MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
SEQUENCE(
LEN(
a
)-1
),
n,
XMATCH(
TRUE,
MID(
a,
s,
1
)b,
c
)
)
)
),
"No such number"
) Excel solution 3 for Next Larger Permutation, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
n,
LET(
f,
LAMBDA(
x,
CONCAT(
SORT(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
,
-1
)
)
),
z,
n+MIN(
MAP(
SEQUENCE(
811
),
LAMBDA(
x,
IF(
f(
n
)=f(
n+x
),
x,
""
)
)
)
),
IF(
z=n,
"No such number",
z
)
)
)
)Excel solution 4 for Next Larger Permutation, proposed by John V.:
=LET(
f,
LAMBDA(
n,
CONCAT(
SORT(
MID(
n,
SEQUENCE(
LEN(
n
)
),
1
)
)
)
),
r,
LAMBDA(
r,
b,
i,
IF(
f(
i
)=f(
b
),
i,
r(
r,
b,
i+1
)
)
),
IFERROR(
MAP(
A2:A10,
LAMBDA(
x,
r(
r,
x,
1+x
)
)
),
"No such number"
)
)Excel solution 5 for Next Larger Permutation, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
d,
LET(
s,
SEQUENCE(
999
),
r,
LAMBDA(
x,
SORT(
MID(
x,
s,
1
)
)
),
XLOOKUP(
1,
MAP(
d+s,
LAMBDA(
a,
--AND(
r(
a
)=r(
d
)
)
)
),
d+s,
"No such number"
)
)
)
)Excel solution 6 for Next Larger Permutation, proposed by Kris Jaganah:
=MAP(A2:A10,
LAMBDA(y,
LET(a,
{1;2;3},
b,
RIGHT(
y,
3
),
c,
MID(
IF(
LEN(
b
)=2,
0&b,
b
),
a,
1
),
d,
SEQUENCE(
3^3
),
e,
3-{1,
2,
3},
f,
INT(d/(3^e)),
g,
INDEX(
c,
MOD(
f,
3
)+1
),
h,
TOCOL(
--BYROW(
g,
LAMBDA(
x,
LET(
p,
COUNT(
UNIQUE(
-x,
1
)
),
IF(
p=3,
CONCAT(
x
),
""
)
)
)
),
3
),
i,
IFERROR(--(MID(
y,
1,
MAX(
LEN(
y
)-3,
0
)
)&TAKE(SORT(TOCOL(h/(h>--b),
3)),
1)),
"No such number"),
i)))Excel solution 7 for Next Larger Permutation, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
N,
LET(
S,
SEQUENCE(
999,
,
N+1
),
ST,
LAMBDA(
A,
CONCAT(
LEN(
A
)-LEN(
SUBSTITUTE(
A,
SEQUENCE(
,
10,
0
),
""
)
)
)
),
IFERROR(
TAKE(
FILTER(
S,
ST(
N
)=MAP(
S,
LAMBDA(
A,
ST(
A
)
)
)
),
1
),
"No such number"
)
)
)
)Excel solution 8 for Next Larger Permutation, proposed by Timothée BLIOT:
=MAP(A2:A10,
LAMBDA(z,
LET(I,
--RIGHT(
z,
9
),
A,
LEN(
I
),
B,
SEQUENCE(
,
A
),
C,
MID(
I,
B,
1
),
D,
C&CHAR(
B
),
F,
REDUCE(
TOCOL(
D
),
SEQUENCE(
A-1
),
LAMBDA(
w,
v,
LET(
E,
FILTER(
w,
LEN(
w
)=v*2
),
TOCOL(
IF(
ISERR(
FIND(
D,
E
)
) ,
E&D,
1/0
),
3
)
)
)
),
G,
SORT(
MAP(
F,
LAMBDA(
x,
--CONCAT(
REGEXEXTRACT(
x,
"d",
1
)
)
)
)
),
TAKE(FILTER(--(IF(
LEN(
z
)>9,
LEFT(
z,
LEN(
z
)-9
),
""
)&G),
G>I,
"No such number"),
1))))Excel solution 9 for Next Larger Permutation, proposed by Sunny Baggu:
=IFERROR(
MAP(
A2:A10,
LAMBDA(
n,
LET(
a,
n + SEQUENCE(
999
),
b,
SORT(
MID(
n,
SEQUENCE(
LEN(
n
)
),
1
)
),
c,
MAP(
a,
LAMBDA(
a,
AND(
SORT(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
) = b
)
)
),
TAKE(
FILTER(
a,
IFNA(
c,
FALSE
)
),
1
)
)
)
),
"No such number"
)Excel solution 10 for Next Larger Permutation, proposed by Abdallah Ally:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
x,
f,
LAMBDA(
x,
SORT(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
& )
)
),
b,
SEQUENCE(
1000,
,
a+1
),
IF(
--CONCAT(
SORT(
f(
a
),
,
-1
)
)=a,
"No such number",
TAKE(
FILTER(
b,
MAP(
b,
LAMBDA(
x,
NOT(
ISERROR(
1/AND(
f(
x
)=f(
a
)
)
)
)
)
)
),
1
)
)
)
)
)Excel solution 11 for Next Larger Permutation, proposed by Andy Heybruch:
=LET(
_n,
A2,
_x,
LAMBDA(
a,
CONCAT(
SORT(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)
)
),
_ncheck,
_x(
_n
),
_seq,
SEQUENCE(
1000,
,
_n+1
),
IFERROR(
TAKE(
FILTER(
_seq,
MAP(
_seq,
LAMBDA(
v,
_x(
v
)
)
)=_ncheck
),
1
),
"No such number"
)
)Excel solution 12 for Next Larger Permutation, proposed by Andres Rojas Moncada:
=LET(
ord,
LAMBDA(
n,
CONCAT(
SORT(
MID(
n,
SEQUENCE(
LEN(
n
)
),
1
)
)
)
),
inc,
LAMBDA(
f,
v,
s,
IF(
ord(
v
)=s,
v,
f(
f,
v+1,
s
)
)
),
IFERROR(
MAP(
A2:A11,
LAMBDA(
x,
inc(
inc,
x+1,
ord(
x
)
)
)
),
"No such number"
)
)Excel solution 13 for Next Larger Permutation, proposed by Caroline Blake:
=MAP(
A2:A10,
LAMBDA(
v,
LET(
b,
MAP(
SEQUENCE(
1000,
,
v
),
LAMBDA(
a,
LET(
x,
CONCAT(
SORT(
VALUE(
MID(
a,
SEQUENCE(
,
LEN(
a
)
),
1
)
),
,
-1,
TRUE
)
),
x
)
)
),
f,
CONCAT(
SORT(
VALUE(
MID(
v,
SEQUENCE(
,
LEN(
v
)
),
1
)
),
,
-1,
TRUE
)
),
l,
b=f,
IF(
VALUE(
v
)=VALUE(
f
),
"No such number",
CHOOSEROWS(
FILTER(
SEQUENCE(
1000,
,
v
),
b=f
),
2
)
)
)
)
)Solving the challenge of Next Larger Permutation with Python
Python solution 1 for Next Larger Permutation, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel("469 Next Greater Number with Same Digits.xlsx", sheet_name="Sheet1", usecols="A")
test = pd.read_excel("469 Next Greater Number with Same Digits.xlsx", sheet_name="Sheet1", usecols="B")
def find_greater_from_same_digits(number):
number = str(number)
n = len(number)
number_splitted = list(map(int, list(number)))
i = n - 2
while i >= 0:
if number_splitted[i] < number_splitted[i + 1]:
break
i -= 1
if i == -1:
return "No such number"
j = n - 1
while j > i:
if number_splitted[j] > number_splitted[i]:
break
j -= 1
number_splitted[i], number_splitted[j] = number_splitted[j], number_splitted[i]
number_splitted = number_splitted[:i+1] + number_splitted[i+1:][::-1]
return ''.join(map(str, number_splitted))
result = input.copy()
result["Answer Expected"] = result["Number"].map(find_greater_from_same_digits)
print(pd.concat([result, test], axis=1))
Solving the challenge of Next Larger Permutation with Python in Excel
Python in Excel solution 1 for Next Larger Permutation, proposed by Abdallah Ally:
# Simple with Python
import pandas as pd
file_path = 'Excel_Challenge_469 - Next Greater Number with Same Digits.xlsx'
df = pd.read_excel(file_path)
# Perform data wrangling
def next_greater_number(number):
largest_num = int(''.join(sorted(str(number))[::-1]))
if number == largest_num:
return 'No such number'
for num in range(number + 1, largest_num + 1):
if sorted(str(num)) == sorted(str(number)):
return num
df['My Answer'] = df['Number'].map(next_greater_number)
df['Check'] = df.iloc[:, 1] == df.iloc[:, 2]
df
Solving the challenge of Next Larger Permutation with R
R solution 1 for Next Larger Permutation, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(gtools)
input = read_excel("Excel/469 Next Greater Number with Same Digits.xlsx", range = "A1:A10")
test = read_excel("Excel/469 Next Greater Number with Same Digits.xlsx", range = "B1:B10")
find_greater_from_same_digits = function(number) {
number = as.character(number)
n = nchar(number)
number_splitted = as.numeric(unlist(strsplit(number, "")))
i = n - 1
while (i > 0) {
if (number_splitted[i] < number_splitted[i + 1]) {
break
}
i = i - 1
}
if (i == 0) {
return("No such number")
}
j = n
while (j > i) {
if (number_splitted[j] > number_splitted[i]) {
break
}
j = j - 1
}
number_splitted[c(i, j)] = number_splitted[c(j, i)]
number_splitted = c(number_splitted[1:i], rev(number_splitted[(i + 1):n]))
return(paste(number_splitted, collapse = ""))
}
result = input %>%
mutate(`Answer Expected` = map_chr(Number, find_greater_from_same_digits))
identical(result$`Answer Expected`, test$`Answer Expected`)
# [1] TRUE
&&