Provide a formula to list those numbers in A2:A10 where every succeeding digit is >= previous digit. Hence, 1336 is a valid answer but 13365 is not a valid answer as 5 is not >=6. Similarly, 51678 is not a valid answer as 1 is not >=5.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 49
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Digits in Increasing Sequence with Power Query
Power Query solution 1 for Digits in Increasing Sequence, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Result = Table.SelectRows(
Source,
each Number.From(Text.Combine(List.Sort(Text.ToList(Text.From([Numbers]))))) = [Numbers]
)
in
ResultPower Query solution 2 for Digits in Increasing Sequence, proposed by Luan Rodrigues:
let
Fonte = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content],
Result = Table.SelectRows(
Fonte,
each Text.ToList(Text.From([Numbers])) = List.Sort(Text.ToList(Text.From([Numbers])))
)
in
ResultPower Query solution 3 for Digits in Increasing Sequence, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "CompDigits"]}[Content],
#"Filtered Rows" = Table.SelectRows(
Source,
each Text.From([Numbers]) = Text.Combine(List.Sort(Text.ToList(Text.From([Numbers]))))
)
in
#"Filtered Rows"Power Query solution 4 for Digits in Increasing Sequence, proposed by Antriksh Sharma:
let
Source = DataSource,
ChangedType = Table.TransformColumnTypes(Source, {{"Numbers", Int64.Type}}),
Transformation = Table.AddColumn(
ChangedType,
"Answer",
(Digits) =>
let
NumList = List.Transform(Text.ToList(Text.From(Digits[Numbers])), Number.From),
NumCount = List.Count(NumList),
Index = {1 .. NumCount - 1},
Check = List.Accumulate(
Index,
{NumList{0}},
(State, Current) =>
if NumList{Current} >= List.Max({List.Last(State), 0}) then
State & {NumList{Current}}
else
{}
),
Result = if List.Count(Check) = NumCount then true else false
in
Result,
Logical.Type
),
FilteredRows = Table.SelectRows(Transformation, each ([Answer] = true))[[Numbers]]
in
FilteredRowsPower Query solution 5 for Digits in Increasing Sequence, proposed by Venkata Rajesh:
let
Source = Data,
Result = Table.SelectRows(
Source,
each (
let
_check = Text.ToList(Text.From([Numbers]))
in
_check = List.Sort(_check)
)
)
in
ResultPower Query solution 6 for Digits in Increasing Sequence, proposed by Mahmoud Bani Asadi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
FilteredRows = Table.SelectRows(
Source,
each [
NtoT = Text.From([Numbers]),
TxtToList = Text.ToList(NtoT),
SrtList = List.Sort(TxtToList),
Compare = TxtToList = SrtList
][Compare]
)
in
FilteredRowsSolving the challenge of Digits in Increasing Sequence with Excel
Excel solution 1 for Digits in Increasing Sequence, proposed by Rick Rothstein:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,0+CONCAT(SORT(MID(x,SEQUENCE(LEN(x)),1)))=x)))Excel solution 2 for Digits in Increasing Sequence, proposed by Rick Rothstein:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(c,LEN(x)-1,SUM(0+(MID(x,SEQUENCE(,c),1)<=MID(x,SEQUENCE(,c,2),1)))=c))))Excel solution 3 for Digits in Increasing Sequence, proposed by John V.:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
AND(
MMULT(
-MID(
x,
SEQUENCE(
LEN(
x
)-1
)+{0,
1},
1
),
{1;-1}
)>=0
)
)
)
)
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
c,
MID(
x,
ROW(
1:9
),
1
),
CONCAT(
SORT(
c
)
)=CONCAT(
c
)
)
)
)
)
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
--CONCAT(
SORT(
MID(
x,
ROW(
1:9
),
1
)
)
)=x
)
)
)Excel solution 4 for Digits in Increasing Sequence, proposed by محمد حلمي:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(A,AND(SCAN("",SEQUENCE(,LEN(A)),LAMBDA(B,C,MID(A&9,C+1,1)>=MID(A&9,C,1)))))))Excel solution 5 for Digits in Increasing Sequence, proposed by محمد حلمي:
=TOCOL(SORTBY(FORMULATEXT(B2:J2),
LEN(FORMULATEXT(B2:J2))))Excel solution 6 for Digits in Increasing Sequence, proposed by محمد حلمي:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
x=0+CONCAT(
SORT(
MID(
x,
ROW(
1:20
),
1
)
)
)
)
)
)Excel solution 7 for Digits in Increasing Sequence, proposed by محمد حلمي:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,x=0+CONCAT(SORT(MID(x,ROW(1:20),1))))))Excel solution 8 for Digits in Increasing Sequence, proposed by محمد حلمي:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
A,
AND(
MID(
A&9,
SEQUENCE(
LEN(
A
)
)+1,
1
)>=MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)
)
)
)
)Excel solution 9 for Digits in Increasing Sequence, proposed by محمد حلمي:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,
LET(c,MID(a,SEQUENCE(LEN(a)),1),
AND((DROP(c,1)-DROP(c,-1))>=0)))))Excel solution 10 for Digits in Increasing Sequence, proposed by محمد حلمي:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
A,
AND(
MID(
A&9,
ROW(
2:11
),
1
)>=MID(
A,
ROW(
1:10
),
1
)
)
)
)
)Excel solution 11 for Digits in Increasing Sequence, proposed by Kris Jaganah:
=LET(
b,
A2:A10,
a,
MAP(
b,
LAMBDA(
x,
TEXTJOIN(
"",
TRUE,
SMALL(
MID(
x,
SEQUENCE(
,
LEN(
x
),
1,
1
),
1
)/1,
SEQUENCE(
,
LEN(
x
),
1,
1
)
)
)/1
)
),
FILTER(
b,
b=a
)
)Excel solution 12 for Digits in Increasing Sequence, proposed by Julian Poeltl:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
N,
LET(
L,
LEN(
N
),
SP,
MID(
N,
SEQUENCE(
L
),
1
),
L-1=SUM(
--DROP(
SP<=DROP(
SP,
1
),
-1
)
)
)
)
)
)Excel solution 13 for Digits in Increasing Sequence, proposed by Aditya Kumar Darak 🇮🇳:
= FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
_splt,
--MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
AND(
DROP(
_splt,
1
) >= DROP(
_splt,
-1
)
)
)
)
)
)Excel solution 14 for Digits in Increasing Sequence, proposed by Aditya Kumar Darak 🇮🇳:
= FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
a,
LET(
_splt,
--MID(a, SEQUENCE(LEN(a)), 1),
AND(_splt = SORT(_splt))))))Excel solution 15 for Digits in Increasing Sequence, proposed by Timothée BLIOT:
=LET(
Numbers,A2:A10,
Digits, MAKEARRAY(ROWS(Numbers),MAX(LEN(Numbers)+1),LAMBDA(a,b,
IFERROR(VALUE(MID(INDEX(Numbers,a),b,1)),0)
)),
DigitsPrev, HSTACK(INDEX(Digits,,1), TAKE(Digits, , COLUMNS(Digits)-1)),
Compare, --(Digits >= DigitsPrev)* (DigitsPrev > 0),
SumCompare,BYROW(Compare, LAMBDA(a,SUM(a))),
NumberOfDigits,BYROW(Numbers, LAMBDA(a,LEN(a))),
IsValid, IF(SumCompare=NumberOfDigits,1,0),
Answer, FILTER(Numbers,IsValid,""),
Answer)Excel solution 16 for Digits in Increasing Sequence, proposed by Hussein SATOUR:
=FILTER(A2:A10, BYROW(A2:A10, LAMBDA(x,
LET(y, MID(x, SEQUENCE(LEN(x)),1)*1, z, MID(x, SEQUENCE(LEN(x)) + 1,1)*1, SUM(DROP(y>z, -1)*1)))) = 0)Excel solution 17 for Digits in Increasing Sequence, proposed by Duy Tùng:
=TOCOL(MAP(A2:A10,LAMBDA(x,LET(a,MID(x,SEQUENCE(LEN(x)),1),x/AND(DROP(a,1)>=DROP(a,-1))))),3)Excel solution 18 for Digits in Increasing Sequence, proposed by Bhavya Gupta:
=FILTER(A2:A10,
MAP(A2:A10,
LAMBDA(n,
LET(m,
MID(
n,
SEQUENCE(
LEN(
n
)
),
1
),
PRODUCT(--(DROP(
m,
1
)>=DROP(
m,
-1
)))))))Excel solution 19 for Digits in Increasing Sequence, proposed by Charles Roldan:
=LET(
Diff,
LAMBDA(
x,
DROP(
x,
1
) - DROP(
x,
-1
)
),
TextToArray,
LAMBDA(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
FixedBy,
LAMBDA(
f,
LAMBDA(
x,
x = f(
x
)
)
),
B,
LAMBDA(
f,
g,
LAMBDA(
x,
f(
g(
x
)
)
)
),
FlMp,
LAMBDA(
f,
LAMBDA(
x,
FILTER(
x,
MAP(
x,
f
)
)
)
),
FlMp(
B(
B(
B(
FixedBy(
ABS
),
MIN
),
Diff
),
TextToArray
)
)
)(A2:A10)Excel solution 20 for Digits in Increasing Sequence, proposed by Stefan Olsson:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
_n,
IF(
_n=VALUE(
JOIN(
"",
SORT(
TRANSPOSE(
SPLIT(
REGEXREPLACE(
& _n&"",
"(?s)(.{1})",
"$1"&CHAR(
127
)
)
,
CHAR(
127
)
)
)
)
)
),
_n,
)
)
)
)Excel solution 21 for Digits in Increasing Sequence, proposed by Jardiel Euflázio:
=LET(a,
A2:A10,
FILTER(a,
BYROW(a,
LAMBDA(a,
LET(b,
LEN(
a
),
SUM(0+(MID(
a,
SEQUENCE(
b-1,
,
b,
-1
),
1
)>=MID(
a,
SEQUENCE(
b-1,
,
b-1,
-1
),
1
)))=b-1)))))Excel solution 22 for Digits in Increasing Sequence, proposed by Victor Momoh (MVP, MOS, R.Eng):
Excel solution 23 for Digits in Increasing Sequence, proposed by Victor Momoh (MVP, MOS, R.Eng):
=FILTER($A$2:$A$10,MAP(A2:A10,LAMBDA(x,x=CONCAT(SORT(MID(x,SEQUENCE(,LEN(x)),1)+0,,,1))+0)))Excel solution 24 for Digits in Increasing Sequence, proposed by Mahmoud Bani Asadi:
=FILTER(
Table1[Numbers],
BYROW(
Table1[Numbers],
LAMBDA(
x,
LET(
length,
LEN(
x
),
txtToList,
MID(
x,
SEQUENCE(
length
),
1
),
srt,
SORT(
txtToList
),
AND(
txtToList=srt
)
)
)
)
)Excel solution 25 for Digits in Increasing Sequence, proposed by Gabriel Raigosa:
=LET(
d,
A2:A10,
m,
--MID(
d,
SEQUENCE(
1,
16
),
1
),
FILTER(
d,
BYROW(
--IFERROR(
m<=TAKE(
m,
ROWS(
d
),
-15
),
1
),
LAMBDA(
f,
MIN(
f
)
)
)
)
)Excel solution 26 for Digits in Increasing Sequence, proposed by Sarun Chimamphant:
=REDUCE(
,
A2:A10,
LAMBDA(
acc,
cur,
LET(
l,
LEN(
cur
),
a,
MID(
cur,
SEQUENCE(
l
),
1
),
IF(
AND(
SORT(
a
)=a
),
VSTACK(
acc,
cur
),
acc
)
)
)
)Excel solution 27 for Digits in Increasing Sequence, proposed by Ibrahim Sadiq:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,IFERROR((RIGHT(x))*1>=(RIGHT(OFFSET(x,-1,)))*1,TRUE))))
This formular should work based on my new understanding of the challenge
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,(RIGHT(x))*1>=(IF(LEFT(RIGHT(x,2))="0",LEFT(RIGHT(x,3)),LEFT(RIGHT(x,2)))*1))))Excel solution 28 for Digits in Increasing Sequence, proposed by Meni Porat:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,x=(--TEXTJOIN("",,SORT(MID(x,SEQUENCE(LEN(x)),1)))))))Solving the challenge of Digits in Increasing Sequence with Python in Excel
Python in Excel solution 1 for Digits in Increasing Sequence, proposed by Alejandro Campos:
numbers = xl("A2:A10")[0]
[n for n in numbers if str(n) == ''.join(sorted(str(n)))]Solving the challenge of Digits in Increasing Sequence with DAX
DAX solution 1 for Digits in Increasing Sequence, proposed by Zoran Milokanović:
EVALUATE
FILTER(Input,
VAR N = CONVERT(Input[Numbers], STRING)
VAR T = COUNTROWS(FILTER(ADDCOLUMNS(GENERATESERIES(1, LEN(N)),
"C", MID(N, [Value], 1),
"P", MID(N, SWITCH(TRUE(), [Value] = 1, 1, [Value] - 1), 1)
), [P] > [C]))
RETURN
ISBLANK(T)
)
&&