Stepping Number is that number where adjacent digits have the difference of 1 or -1. Hence 5434 is a Stepping Number where adjacent digits differ by 1 or -1. 5-4=1 and 4-3=1 and 3-4=-1 But 5435 is not a Stepping Number as difference between last two digits is 2 not 1. Find the Min, Max and Count of Stepping numbers between From and To columns.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 238
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Count Stepping Numbers with Power Query
Power Query solution 1 for Count Stepping Numbers, proposed by Bo Rydobon 🇹🇭:
let
Num = List.Buffer(
List.Combine(
List.Accumulate(
{1 .. 9},
{{1 .. 9}},
(s, l) =>
s
& {
List.Combine(
List.Transform(
List.Last(s),
each
let
r = Number.Mod(_, 10)
in
if r = 0 then
{_ * 10 + 1}
else if r = 9 then
{_ * 10 + 8}
else
{_ * 10 + r - 1, _ * 10 + r + 1}
)
)
}
)
)
),
Ans = Table.ExpandRecordColumn(
Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
"A",
each
let
L = List.Select(Num, (n) => n >= [From] and n <= [To])
in
[Min = List.Min(L), Max = List.Max(L), Count = List.Count(L)]
),
"A",
{"Min", "Max", "Count"}
)
in
AnsPower Query solution 2 for Count Stepping Numbers, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromRecords(
Table.TransformRows(
Source,
each
let
s = List.Buffer(
List.Select(
{[From] .. [To]},
each
let
n = List.Transform(Text.ToList(Text.From(_)), Number.From)
in
List.AllTrue(
List.Transform({1 .. List.Count(n) - 1}, each Number.Abs(n{_} - n{_ - 1}) = 1)
)
)
)
in
[Min = s{0}, Max = List.Reverse(s){0}, Count = List.Count(s)]
)
)
in
SPower Query solution 3 for Count Stepping Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Calc = Table.AddColumn(
Source,
"Custom",
each
let
a = {[From] .. [To]},
b = List.Buffer(
List.Transform(
a,
each
let
b1 = Text.ToList(Text.From(_)),
b2 = List.Transform(b1, Number.From),
b3 = List.Transform(
{0 .. List.Count(b2) - 1},
each Number.Abs(b2{_} - b2{_ - 1}) = 1
),
b4 = {Number.From(Text.Combine(b1)), List.AllTrue(List.Skip(b3))}
in
b4
)
),
c = List.Select(b, each _{1} = true),
d = [Min = List.First(c){0}, Max = List.Last(c){0}, Count = List.Count(c)]
in
d
),
Sol = Table.ExpandRecordColumn(Calc, "Custom", {"Min", "Max", "Count"})
in
SolPower Query solution 4 for Count Stepping Numbers, proposed by Felipe Perez Arevalo:
let
Source = Table,
Numbers = Table.AddColumn(Source, "Numbers", each {[From] .. [To]}),
SteppingNumbers = Table.AddColumn(
Numbers,
"Stepping",
each List.Select(
[Numbers],
each
let
TextSplit = Text.ToList(Text.From(_)),
SteppingNumberCheck = List.Generate(
() => [
l0 = TextSplit,
n = 0,
d = Number.From(l0{n + 1}) - Number.From(l0{n}),
i = d = 1 or d = - 1
],
each [n] < List.Count([l0]) - 1,
each [
l0 = [l0],
n = [n] + 1,
d = Number.From(l0{n + 1}) - Number.From(l0{n}),
i = d = 1 or d = - 1
],
each [i]
),
SteppingNumber = not List.Contains(SteppingNumberCheck, false)
in
SteppingNumber
)
),
Count = Table.AddColumn(SteppingNumbers, "Count", each List.Count([Stepping]), Int64.Type),
Min = Table.AddColumn(Count, "Min", each List.Min([Stepping]), Int64.Type),
Max = Table.AddColumn(Min, "Max", each List.Max([Stepping]), Int64.Type)[
[From],
[To],
[Min],
[Max],
[Count]
]
in
MaxSolving the challenge of Count Stepping Numbers with Excel
Excel solution 1 for Count Stepping Numbers, proposed by Bo Rydobon 🇹🇭:
=LET(s,SEQUENCE(9),t,TOCOL(REDUCE(s,s,LAMBDA(a,v,LET(p,TAKE(a,,-1),r,--RIGHT(p),HSTACK(a,--TOCOL(p&r+IFS(r=9,{-1,""},r,{-1,1},1,{1,""}),3))))),3,1),
a,XMATCH(A2:B7,t,{1,-1}),HSTACK(INDEX(t,a),MMULT(a,{-1;1})+1))
=LET(s,SEQUENCE(9),t,REDUCE(s,s,LAMBDA(a,v,UNIQUE(VSTACK(a,LET(r,RIGHT(a)+{-1,1},--TOCOL(IFS(LEN(r)=1,a&r),3)))))),
a,XMATCH(A2:B7,t,{1,-1}),HSTACK(INDEX(t,a),MMULT(a,{-1;1})+1))Excel solution 2 for Count Stepping Numbers, proposed by John V.:
=LET(r,REDUCE(ROW(1:9),ROW(1:6),LAMBDA(a,v,LET(n,MOD(a,10)+{-1,1},UNIQUE(VSTACK(a,--TOCOL(a&IFS(LEN(n)<2,n),2)))))),p,XMATCH(A2:B7,r,{1,-1}),HSTACK(INDEX(r,p),1+MMULT(p,{-1;1})))Excel solution 3 for Count Stepping Numbers, proposed by محمد حلمي:
=REDUCE({"Min","Max","Count"},A2:A5,LAMBDA(a,b,LET(
e,SEQUENCE(OFFSET(b,,1)-b+1,,b),
i,FILTER(e,MAP(e,LAMBDA(a,LET(
e,MID(a,SEQUENCE(LEN(a)),1),AND(ABS(DROP(e,1)-DROP(e,-1))=1))))),
VSTACK(a,HSTACK(MIN(i),MAX(i),ROWS(i))))))Excel solution 4 for Count Stepping Numbers, proposed by Kris Jaganah:
=LET(f,TOCOL(MAP(SEQUENCE(1000000,10),LAMBDA(x,LET(a,MID(x,SEQUENCE(LEN(x)),1),b,DROP(a,1),c,DROP(a,-1),IFS(--(SUM(--(ABS(b-c)=1))=ROWS(b)),x)))),3),--TEXTSPLIT(TEXTJOIN("#",,MAP(A2:A7,B2:B7,LAMBDA(v,w,LET(m,TOCOL(f/((f>=v)*(f<=w)),3),TEXTJOIN("-",1,MIN(m),MAX(m),ROWS(m)))))),"-","#"))Excel solution 5 for Count Stepping Numbers, proposed by Julian Poeltl:
=LET(Fr,A2:A7,T,B2:B7,S,SEQUENCE(10^6,5),C,TOCOL(MAP(S,LAMBDA(A,LET(L,LEN(A),S,MID(A,SEQUENCE(L),1),IF(SUM(--(ABS(DROP(S-DROP(S,1),-1))=1))=L-1,A,D)))),3),REDUCE(HSTACK("Min","Max","Count"),SEQUENCE(ROWS(Fr)),LAMBDA(A,B,VSTACK(A,LET(F,FILTER(C,(C>=INDEX(Fr,B))*(C<=INDEX(T,B))),HSTACK(MIN(F),MAX(F),ROWS(F)))))))Excel solution 6 for Count Stepping Numbers, proposed by Timothée BLIOT:
=REDUCE({"Min","Max","Count"},A2:A7,LAMBDA(ac,z,LET(w,XLOOKUP(z,A2:A7,B2:B7),F,LAMBDA(n,LET(A,MID(n,SEQUENCE(LEN(n)-1,,1),2),SUM(MAP(A,LAMBDA(x,--(ABS((LEFT(x)*1)-(RIGHT(x)*1))=1))))=ROWS(A))),S,SEQUENCE( MIN(w-z+1,10^6),ROUNDUP((w-z)/10^6,0),z),B,MAP(S,LAMBDA(x ,F(x))),C, TOCOL( IF(B,IF(S<=w,S,1/0),1/0),3),VSTACK(ac,HSTACK(MIN(C),MAX(C),COUNT(C))))))Excel solution 7 for Count Stepping Numbers, proposed by LEONARD OCHEA 🇷🇴:
= 30 seconds
=LET(d,A2:B7,f,INDEX(d,,1),t,INDEX(d,,2),VSTACK(HSTACK("Min","Max","Count"),--TEXTSPLIT(TEXTJOIN("%",,MAP(f,t,LAMBDA(a,b,LET(n,b-a+1,c,ROUNDUP(n/2^20,0),s,SEQUENCE(n/c,c,a),m,--(MAP(s,LAMBDA(x, LET( e,MID(x,SEQUENCE(LEN(x)),1), AND(ABS(DROP(e,1)-DROP(e,-1))=1))))), z,TOCOL(s/m,2), MIN(z)&"@"&MAX(z)&"@"&COUNTA(z) )))),"@","%")))Excel solution 8 for Count Stepping Numbers, proposed by Julien Lacaze:
=LET(from,A2:A7,to,B2:B7,
IsStepping,LAMBDA(number,MAP(number,
LAMBDA(n,LET(split,--MID(n,SEQUENCE(LEN(n)),1),--AND(ABS(
INDEX(split,SEQUENCE(LEN(n)-1))-INDEX(split,SEQUENCE(LEN(n)-1,,2)))=1))))),
MAP(from,to,LAMBDA(f,t,LET(L,SEQUENCE(ROUNDUP((t-f+1)/10,0),10,f),SUM(--(IsStepping(L)=1))))))
for line 2 to 5 :
=LET(from,A2:A7,to,B2:B7,
IsStepping,LAMBDA(number,MAP(number,LAMBDA(n,LET(split,--MID(n,SEQUENCE(LEN(n)),1),--AND(ABS(INDEX(split,SEQUENCE(LEN(n)-1))-INDEX(split,SEQUENCE(LEN(n)-1,,2)))=1))))),
MAP(from,to,LAMBDA(f,t,LET(L,SEQUENCE(t-f+1,,f),SUM(IF(IsStepping(L)=1,1,0))))))Excel solution 9 for Count Stepping Numbers, proposed by Diarmuid Early:
=a)*(stepNums<=b)),MIN(hits)&"-"&MAX(hits)&"-"&COUNT(hits))))Solving the challenge of Count Stepping Numbers with Python in Excel
Solving the challenge of Count Stepping Numbers with Excel VBA
Excel VBA solution 1 for Count Stepping Numbers, proposed by Vasin Nilyok:
VBA:
Sub steppingNumber()
Dim AnsCollection As New Collection
With ActiveSheet
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To LastRow
NumStart = .Cells(r, 1)
NumFinish = .Cells(r, 2)
iNum = NumStart
For i = NumStart To NumFinish
lenNum = Len(iNum)
For ii = 1 To lenNum - 1
If Abs(CInt(Mid(iNum, ii, 1)) - CInt(Mid(iNum, ii + 1, 1))) = 1 Then
Else
GoTo nextNum
End If
Next ii
AnsCollection.Add iNum
nextNum:
iNum = iNum + 1
Next i
nCollection = AnsCollection.Count
.Cells(r, 7) = AnsCollection(1)
.Cells(r, 8) = AnsCollection(nCollection)
.Cells(r, 9) = nCollection
Set AnsCollection = New Collection
Next r
End With
End Sub
&&&