— CAN YOU SOLVE THIS - EXCEL CHALLENGE 201 —
(Solutions in any language are also welcome for Excel Challenges)
Find the next number which is having all digits distinct.
Example for 99, next number which is having all distinct digits is 102.
Post answers in Comment.
(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)
Download Practice File - https://lnkd.in/d7RGhEpu
#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r
LinkedIn Post:
Optimize without Brute force
for 1,100,000,000 => 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))))
This formula iterates considerably less than my previous solution.
=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)))))
Power Query
This code performs fewer iterations than my previous solution.
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
a
LinkedIn Post by:
[
Zoran Milokanović
](https://www.linkedin.com/in/ACoAAAEwhwwB2CI1Gk7IC6kZSO36Ug1bUvk9DGs)
Next Number Distinct Digit w/ hashtag#powerquery. hashtag#bitanbit hashtag#powerbi
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
Solution
My solution (brute force) with
byrow
=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)))
