Find the next number which is having all digits distinct. Example for 99, next number which is having all distinct digits is 102.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 201
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Next Distinct Digit Number with Power Query
Power Query solution 1 for Next Distinct Digit Number, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each List.Max(
List.Generate(
() => [Number] + 1,
each List.Count(List.Distinct(Text.ToList(Text.From(_)))) < Text.Length(Text.From(_)),
each _ + 1
)
)
+ 1
)
in
AnsPower Query solution 2 for Next Distinct Digit Number, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
Solution = Table.TransformRows(
Source,
each List.Last(
List.Generate(
() => [Number] + 1,
each not List.IsDistinct(Text.ToList(Text.From(_))),
each _ + 1,
each _ + 1
)
)
)
in
SolutionPower Query solution 3 for Next Distinct Digit Number, proposed by Aditya Kumar Darak 🇮🇳:
let
MyFun = (Number) =>
let
Add = Number + 1,
Split = Text.ToList(Text.From(Add)),
Check = List.IsDistinct(Split),
Final = if Check then Add else @MyFun(Add)
in
Final,
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(Source, "Answer", each MyFun([Number]))
in
ReturnPower Query solution 4 for Next Distinct Digit Number, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each List.Last(
List.Generate(
() => [x = [Number] + 1, y = 0, z = 1],
each [y] <> [z],
each [
x = [x] + 1,
y = List.Count(Text.ToList(Text.From([x]))),
z = List.Count(List.Distinct(Text.ToList(Text.From([x]))))
],
each [x]
)
)
)
in
SolPower Query solution 5 for Next Distinct Digit Number, proposed by Guillermo Arroyo:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla2"]}[Content],
m = (y) => List.Transform(Text.ToList(Text.From(y)), each Number.FromText(_)),
l = {0 .. 10},
a = List.Transform(
Origen[Number],
(x) =>
let
o = (r, t, s, i) =>
if i > s then
r
else
let
u = List.Min(List.Select(List.RemoveItems(l, m(r)), each _ >= m(t){i - 1}))
in
if u = 10 then
@o(
0,
(r + 1) * Number.Power(10, 1 + s - i),
List.Count(m((r + 1) * Number.Power(10, 1 + s - i))),
1
)
else
@o(
r * 10 + u,
if u = m(t){i - 1} then t else (r * 10 + u) * Number.Power(10, s - i),
s,
i + 1
)
in
o(0, x + 1, List.Count(m(x + 1)), 1)
)
in
aPower Query solution 6 for Next Distinct Digit Number, proposed by Guillermo Arroyo:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = List.Transform(
Origen[Number],
(x) =>
let
o = (y) =>
if y = Number.FromText(Text.Combine(List.Distinct(Text.ToList(Text.From(y))))) then
y
else
@o(y + 1)
in
o(x + 1)
)
in
aSolving the challenge of Next Distinct Digit Number with Excel
Excel solution 1 for Next Distinct Digit Number, proposed by Bo Rydobon 🇹🇭:
=LET(R,LAMBDA(R,n,LET(m,n+1,l,LEN(m),IF(ROWS(UNIQUE(MID(m,SEQUENCE(l),1)))=l,m,R(R,m)))),MAP(A2:A8,LAMBDA(a,R(R,a))))Excel solution 2 for Next Distinct Digit Number, proposed by Bo Rydobon 🇹🇭:
=> 1,203,456,789
that is 103,456,789 increment with Number+1 will take forever
Fast one
=MAP(A2:A8+1,LAMBDA(a,LET(s,SEQUENCE(,10,0),
r,--REDUCE(LEFT(a)+{0;1},SEQUENCE(LEN(a)-1),LAMBDA(c,w,
TAKE(TOCOL(IFS(ISERR(FIND(s,c))*(--c>=--LEFT(a,w)),c&s),3),10))),
XLOOKUP(a,r,r,,1))))Excel solution 3 for Next Distinct Digit Number, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A8,LAMBDA(a,MIN(TOCOL(MAP(a+SEQUENCE(999),LAMBDA(s,s/(ROWS(UNIQUE(MID(s,SEQUENCE(LEN(s)),1)))=LEN(s)))),3))))Excel solution 4 for Next Distinct Digit Number, proposed by Rick Rothstein:
=MAP(A2:A8,LAMBDA(n,MIN(MAP(SEQUENCE(999,,n+1),LAMBDA(x,IF(ISERROR(MODE(0+MID(x,SEQUENCE(LEN(x)),1))),x,""))))))Excel solution 5 for Next Distinct Digit Number, proposed by John V.:
=MAP(A2:A8,LAMBDA(x,LET(n,x+ROW(1:999),@FILTER(n,MAP(n,LAMBDA(x,LEN(x)=ROWS(UNIQUE(MID(x,SEQUENCE(LEN(x)),1)))))))))Excel solution 6 for Next Distinct Digit Number, proposed by محمد حلمي:
=MAP(A2:A8,LAMBDA(a,LET(r,SEQUENCE(300,,a+1),XLOOKUP(1,--MAP(r,LAMBDA(a,ROWS(UNIQUE(MID(a,SEQUENCE(
LEN(a)),1)))=LEN(a))),r))))Excel solution 7 for Next Distinct Digit Number, proposed by محمد حلمي:
=MAP(A2:A8,LAMBDA(a,LET(r,SEQUENCE(300,,a+1),XLOOKUP(1,--MAP(r,LAMBDA(a,ROWS(UNIQUE(MID(a,ROW(1:9),1)))=LEN(a)+1)),r))))Excel solution 8 for Next Distinct Digit Number, proposed by Julian Poeltl:
=MAP(A2:A8,LAMBDA(N,LET(S,N+SEQUENCE(1000),XLOOKUP(TRUE,MAP(S,LAMBDA(A,LET(SP,MID(A,SEQUENCE(LEN(A)),1),LEN(A)=ROWS(UNIQUE(SP))))),S))))Excel solution 9 for Next Distinct Digit Number, proposed by Timothée BLIOT:
=LET(F,LAMBDA(n,COUNT(MID(n,SEQUENCE(LEN(n)),1)*1)=COUNT(UNIQUE(MID(n,SEQUENCE(LEN(n)),1)*1))), S,LAMBDA(self,x,IF(F(x), x, self(self, x+1))), MAP(A2:A8, LAMBDA(a, S(S,a) )))Excel solution 10 for Next Distinct Digit Number, proposed by Sunny Baggu:
=LET(
_list, A2:A8 + SEQUENCE(, 500),
BYROW(
_list /
MAP(
_list,
LAMBDA(a, LET(_m, LAMBDA(x, MID(x, SEQUENCE(LEN(x)), 1)), ROWS(_m(a)) = ROWS(UNIQUE(_m(a)))))
),
LAMBDA(a, MIN(TOCOL(a, 2)))
)
)Excel solution 11 for Next Distinct Digit Number, proposed by Sunny Baggu:
=LET(
_list, A2:A8 + SEQUENCE(, 500),
_cond, MAP(
_list,
LAMBDA(a, LET(_m, LAMBDA(x, MID(x, SEQUENCE(LEN(x)), 1)), ROWS(_m(a)) = ROWS(UNIQUE(_m(a)))))
),
DROP(
REDUCE(
"",
SEQUENCE(ROWS(A2:A8)),
LAMBDA(a, v, VSTACK(a, XLOOKUP(TRUE, CHOOSEROWS(_cond, v), CHOOSEROWS(_list, v))))
),
1
)
)Excel solution 12 for Next Distinct Digit Number, proposed by LEONARD OCHEA 🇷🇴:
=BYROW(MAP(A2:A8+SEQUENCE(,1000),LAMBDA(a,LET(n,LEN(a),u,COUNTA(UNIQUE(MID(a,SEQUENCE(n),1))),IF(n=u,a,"")))),LAMBDA(x,MIN(x)))Excel solution 13 for Next Distinct Digit Number, proposed by Md. Zohurul Islam:
=LET(
z,
A2:A8,
v,
MAP(
z,
LAMBDA(
x,
LET(
n,
SEQUENCE(
999,
,
x+1
),
a,
MAP(
n,
LAMBDA(
y,
LET(
p,
LEN(
y
),
q,
COUNT(
UNIQUE(
--MID(
y,
SEQUENCE(
,
p
),
1
),
1
)
),
IF(
p=q,
1,
0
)
)
)
),
b,
MIN(
FILTER(
n,
a
)
),
b
)
)
),
v
)Excel solution 14 for Next Distinct Digit Number, proposed by Charles Roldan:
=LET(M, LAMBDA(g, g(g)),
S, LAMBDA(f, M(LAMBDA(g, LAMBDA(n, IF(f(n + 1), n + 1, g(g)(n + 1)))))),
F, M(LAMBDA(g, LAMBDA(x, IF(x = "", TRUE,
AND(ISERROR(FIND(LEFT(x), x, 2)), g(g)(RIGHT(x, LEN(x) - 1))))))),
MAP(A2:A8, S(F)))Excel solution 15 for Next Distinct Digit Number, proposed by Charles Roldan:
=LET(Mock, LAMBDA(g, g(g)),
Succ, LAMBDA(f, Mock(LAMBDA(g, LAMBDA(n,
IF(f(n + 1), n + 1, g(g)(n + 1)))))),
_IsDistinct, Mock(LAMBDA(g, LAMBDA(Str,
IF(Str = "", TRUE,
IF(ISERROR(FIND(LEFT(Str), Str, 2)),
g(g)(RIGHT(Str, LEN(Str) - 1))))
))), MAP(A2:A8, Succ(_IsDistinct)))Excel solution 16 for Next Distinct Digit Number, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=MAP(A2:A8;LAMBDA(b;LET(a;SEQUENCE(1000);MIN(IF(MAP(b+a;LAMBDA(x;COUNTA(UNIQUE(MID(x;SEQUENCE(LEN(x));1)))))=MAP(b+a;LAMBDA(y;COUNTA(MID(y;SEQUENCE(LEN(y));1))));b+a;"")))))Excel solution 17 for Next Distinct Digit Number, proposed by Pieter de Bruijn:
=LET(
z,
TOROW(
A2:L2,
3
),
REDUCE(
TOCOL(
z
),
DROP(
z,
,
1
),
LAMBDA(
a,
w,
TOCOL(
IFS(
ISERR(
FIND(
z,
a
)
),
a&z
),
3
)
)
)
)
The above will be able to calculate 362880 permutations for 9 (unique)Excel solution 18 for Next Distinct Digit Number, proposed by Guillermo Arroyo:
=LET(f,LAMBDA(a,b,IF(b=--CONCAT(UNIQUE(MID(b,SEQUENCE(LEN(b)),1))),b,a(a,b+1))),MAP(A2:A8,LAMBDA(c,f(f,c+1))))Excel solution 19 for Next Distinct Digit Number, proposed by Guillermo Arroyo:
=LET(m,LAMBDA(n,--MID(n,SEQUENCE(LEN(n)),1)),l,SEQUENCE(10,,0),MAP(A2:A10+1,LAMBDA(a,LET(p,LAMBDA(q,r,t,s,i,IF(i>s,r,LET(u,MIN(REDUCE(10,l,LAMBDA(d,e,IF(OR(INDEX(m(t),i)>e,OR(m(r)=e)),d,VSTACK(d,e))))),IF(u=10,q(q,0,(r+1)*10^(s-i+1),LEN((r+1)*10^(s-i+1)),1),q(q,r*10+u,IF(@INDEX(m(t),i)=u,t,(r*10+u)*10^(s-i)),s,i+1))))),p(p,0,a,LEN(a),1)))))Excel solution 20 for Next Distinct Digit Number, proposed by Daniel Garzia:
=MAP(
A2:A8,
LAMBDA(
n,
LET(
s,
SEQUENCE(
999,
,
n+1
),
MIN(
FILTER(
s,
MAP(
s,
LAMBDA(
x,
LEN(
x
)=ROWS(
UNIQUE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
)
)
)
)
)
)
)
)Excel solution 21 for Next Distinct Digit Number, proposed by Rayan S.:
=MAP(A2:A8, LAMBDA(e, LET(r, MAP(SEQUENCE(1000, , e + 1, 1), LAMBDA(n, LET(s, CODE(MID(n, SEQUENCE(LEN(n)), 1) + 0), IF(SUM(UNIQUE(s)) = SUM(s), n, 0)))), TAKE(FILTER(r, r > 0), 1))))Solving the challenge of Next Distinct Digit Number with Python in Excel
Python in Excel solution 1 for Next Distinct Digit Number, proposed by Alejandro Campos:
[next(i for i in range(n+1,10**10) if len(set(str(i)))-len(str(i))==0) for n in xl("A2:A8")[0]]&&&