List the numbers where absolute difference between two succeeding digits is 1, 2, 3, 4….sequentially. Ex. 3463 Absolute diff between 2nd and 1st digit = ABS(4-3) = 1 Absolute diff between 3rd and 2nd digit = ABS(6-4) = 2 Absolute diff between 4th and 3rd digit = ABS(3-6) = 3
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 364
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sequential Digit Difference Match with Power Query
Power Query solution 1 for Sequential Digit Difference Match, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Answer = Table.TransformColumns(
Source,
{
"Number",
each
let
a = Text.From(_),
b = List.Transform(Splitter.SplitTextByRepeatedLengths(1)(a), each Number.From(_)),
c = List.InsertRange(List.RemoveLastN(b), 0, {List.First(b)}),
d = List.Transform(List.Zip({b, c}), each Number.Abs(_{1} - _{0})),
e = List.Transform(List.Zip({d, List.Numbers(0, Text.Length(a))}), each _{1} - _{0}),
f = if List.Sum(e) = 0 then _ else null
in
f
}
)
in
Table.SelectRows(Answer, each ([Number] <> null))Power Query solution 2 for Sequential Digit Difference Match, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.SelectRows(
Source,
each
let
a = Text.ToList(Text.From([Number])),
b = List.Transform(a, Number.From),
c = {1 .. List.Count(b) - 1},
d = List.Transform(c, each Number.Abs(b{_} - b{_ - 1})) = c
in
d
)
in
SolPower Query solution 3 for Sequential Digit Difference Match, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.SelectRows(
Fonte,
each [
a = Text.From([Number]),
b = List.Transform(
{1 .. Text.Length(Text.From([Number])) - 1},
(x) => Text.Length(Text.Range(a, 0, x))
),
c = List.Transform(Text.ToList(a), Number.From),
d = List.Transform(
{1 .. Text.Length(Text.From([Number])) - 1},
each Number.Abs(c{_} - c{_ - 1})
)
= b
][d]
)
in
resPower Query solution 4 for Sequential Digit Difference Match, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Fx = (x) =>
let
a = x,
b = List.Transform(Text.ToList(Text.From(x)), Number.From),
c = List.Skip(b),
d = Table.AddColumn(Table.FromColumns({b, c}), "D", each Number.Abs([Column1] - [Column2])),
e = List.FirstN(d[D], List.Count(d[D]) - 1),
f = List.Difference(e, {1 .. List.Count(e)}),
g = if f = {} then a else null
in
g,
h = Table.AddColumn(Origen, "Answer Expected", each Fx([Number]))[[Answer Expected]],
Sol = Table.SelectRows(h, each [Answer Expected] <> null)
in
SolPower Query solution 5 for Sequential Digit Difference Match, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook{0}[Content],
Result = Table.SelectRows(Source, each
let
N = [Number],
T = Text.From(N),
TL = List.Transform(Text.ToList(T), each Number.From(_)),
LA = List.Accumulate({1.. List.Count(TL)-1}, {0}, (s,c) => s & {Number.Abs( TL{c} - TL{c-1})}),
LS = {0..List.Count(TL)-1},
CH = LA = LS
in
CH
)
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 6 for Sequential Digit Difference Match, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom1 = Source[[Number]],
#"Added Custom" = Table.AddColumn(
Custom1,
"Custom",
each [
t = Text.ToList(Text.From([Number])),
p = Table.FromColumns({List.RemoveLastN(t, 1), List.RemoveFirstN(t, 1)}, {"a", "b"}),
d = Table.AddColumn(p, "-", each Number.Abs(Int64.From([a]) - Int64.From([b]))),
c = {1 .. Table.RowCount(d)},
r = List.AllTrue(
Table.AddColumn(
Table.FromColumns({d[#"-"], c}, type table [a = Int64.Type, b = Int64.Type]),
"c",
each [a] = [b]
)[c]
)
][r]
),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true))[[Number]]
in
#"Filtered Rows"Solving the challenge of Sequential Digit Difference Match with Excel
Excel solution 1 for Sequential Digit Difference Match, proposed by Bo Rydobon 🇹🇭:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
SEQUENCE(
LEN(
a
)-1
),
a/AND(
ABS(
MID(
a,
s+1,
1
)-MID(
a,
s,
1
)
)=s
)
)
)
),
3
)Excel solution 2 for Sequential Digit Difference Match, proposed by Rick Rothstein:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
s,
SEQUENCE(
LEN(
x
)-1
),
m,
MID(
x,
s,
2
),
AND(
s=ABS(
LEFT(
m
)-RIGHT(
m
)
)
)
)
)
)
)Excel solution 3 for Sequential Digit Difference Match, proposed by John V.:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
x,
LET(
s,
ROW(
1:9
),
x/AND(
IFERROR(
ABS(
MID(
x,
s,
1
)-MID(
x,
1+s,
1
)
),
s
)=s
)
)
)
),
2
)Excel solution 4 for Sequential Digit Difference Match, proposed by محمد حلمي:
=TOCOl(
MAP(
A2:A10,
lAMBDA(
a,
lET(
i,
LEN(
a
),
s,
SEqUENCE(
i
),
a/AND(
s=ABS(
IFErrOR(
MID(
a,
s,
1
)-MID(
a,
s+1,
1
),
i
)
)
)
)
)
),
2
)Excel solution 5 for Sequential Digit Difference Match, proposed by Kris Jaganah:
=TOCOL(MAP(A2:A10,
LAMBDA(x,
LET(a,
LEN(
x
),
b,
SEQUENCE(
a
),
c,
MID(
x,
b,
1
),
x/(SUM(
b-ABS(
c-VSTACK(
@c,
DROP(
c,
-1
)
)
)
)=a)))),
3)Excel solution 6 for Sequential Digit Difference Match, proposed by Julian Poeltl:
=FILTER(A2:A10,
TRUE=MAP(A2:A10,
LAMBDA(N,
LET(L,
LEN(
N
),
S,
MID(
N,
SEQUENCE(
1,
L
),
1
)*1,
D,
ABS(
CHOOSECOLS(
S,
SEQUENCE(
1,
L-1,
1
)
)-CHOOSECOLS(
S,
SEQUENCE(
1,
L-1,
2
)
)
),
A,
(CHOOSECOLS(
D,
SEQUENCE(
1,
L-2,
2
)
)-CHOOSECOLS(
D,
SEQUENCE(
1,
L-2,
1
)
))=1,
IFERROR(
SUM(
--A
)=L-2,
TRUE
)))))Excel solution 7 for Sequential Digit Difference Match, proposed by Timothée BLIOT:
=x))))=1))))Excel solution 8 for Sequential Digit Difference Match, proposed by Nikola Z Grujicic – Nikola Ž Grujičić:
=LET(x,
MAP(A2:A10,
LAMBDA(a,
LET(d,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
e,
TOCOL(
INDEX(
d,
SEQUENCE(
ROWS(
d
)+1,
,
2,
1
)
),
3
),
f,
ABS(
d-e
),
g,
FILTER(
f,
ISNUMBER(
f
)
),
h,
FILTER(
g,
g<>0
),
i,
SEQUENCE(
ROWS(
h
)
),
IF(PRODUCT(--(h=i))=1,
TEXTJOIN(
"",
,
d
),
"")))),
--FILTER(
x,
x<>""
))Excel solution 9 for Sequential Digit Difference Match, proposed by Hussein SATOUR:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
ABS(
DROP(
a,
-1
) - DROP(
a,
1
)
) = SEQUENCE(
LEN(
x
)-1
),
PRODUCT(
b*1
)
)
)
)
)Excel solution 10 for Sequential Digit Difference Match, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
x,
LET(
_m,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
) + 0,
AND(
ABS(
DROP(
_m,
1
) - DROP(
_m,
-1
)
) = SEQUENCE(
LEN(
x
) - 1
)
)
)
)
)
)Excel solution 11 for Sequential Digit Difference Match, proposed by Abdallah Ally:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
x,
x/LET(
a,
x,
b,
SEQUENCE(
LEN(
a
)
),
c,
--MID(
a,
b,
1
),
AND(
ABS(
DROP(
c,
-1
)-DROP(
c,
1
)
)=DROP(
b,
-1
)
)
)
)
),
2
)Excel solution 12 for Sequential Digit Difference Match, proposed by 🇵🇪 Ned Navarrete C.:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(r,LET(s,SEQUENCE(LEN(r)-1),x,MID(r,s+{0,1},1)*{-1,1},AND(s= BYROW(x,LAMBDA(y,ABS(SUM(y)))))))))Excel solution 13 for Sequential Digit Difference Match, proposed by 🇵🇪 Ned Navarrete C.:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
r,
LET(
t,
LEN(
r
),
s,
SEQUENCE(
t-1
),
x,
MID(
r,
s,
1
),
y,
MID(
r,
s+1,
1
),
r/AND(
s=ABS(
y-x
)
)
)
)
),
3
)Excel solution 14 for Sequential Digit Difference Match, proposed by JvdV –:
=LET(
r,
A2:A10,
s,
SEQUENCE(
,
9
),
FILTER(
r,
BYROW(
ABS(
IFERROR(
MID(
r,
s+1,
1
)-MID(
r,
s,
1
),
s
)
)=s,
AND
)
)
)
Here,
'SEQUENCE(
,
9
)Excel solution 15 for Sequential Digit Difference Match, proposed by Pieter de Bruijn:
=TOCOL(A2:A10/(MMULT(
--IFERROR(
ABS(
MID(
A2:A10,
SEQUENCE(
,
15
),
1
)-MID(
A2:A10,
SEQUENCE(
,
15,
2
),
1
)
)=SEQUENCE(
,
15
),
1
),
SEQUENCE(
15
)^0
)=15),
2)Excel solution 16 for Sequential Digit Difference Match, proposed by Giorgi Goderdzishvili:
=TOCOL(MAP(A2:A10,
LAMBDA(t,
LET(
_nm,
t,
_sq,
MID(
_nm,
SEQUENCE(
,
LEN(
_nm
)
),
1
),
_dif,
DROP(
_sq,
,
-1
)-DROP(
_sq,
,
1
),
_ch,
ABS(
_dif
)=SEQUENCE(
,
LEN(
_nm
)-1
),
_nm / (SUM(
--_ch
)=(LEN(
_nm
)-1))))),
3)Excel solution 17 for Sequential Digit Difference Match, proposed by Edwin Tisnado:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(
t,
LET(
s,
SEQUENCE(
LEN(
t
)-1
),
REDUCE(
"",
s,
LAMBDA(
x,
y,
x&ABS(
MID(
t,
y,
1
)-MID(
t,
y+1,
1
)
)
)
)=CONCAT(
s
)
)
)
)
)Excel solution 18 for Sequential Digit Difference Match, proposed by Abdelrahman Omer, MBA, PMP:
=TOCOL(MAP(A2:A10,
LAMBDA(x,
LET(a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
x/PRODUCT(--(DROP(
DROP(
ABS(
a-VSTACK(
0,
a
)
),
1
),
-1
)=SEQUENCE(
LEN(
x
)-1
)))))),
2)Excel solution 19 for Sequential Digit Difference Match, proposed by Md Ismail Hosen:
=LET(Datas,A2:A10,Mask,MAP(Datas, LAMBDA(Number,LET(Digits,MID(Number,SEQUENCE(LEN(Number)),1),AND(ABS(DROP(Digits,1)-DROP(Digits,-1))=SEQUENCE(LEN(Number)-1))))),FILTER(Datas,Mask))Excel solution 20 for Sequential Digit Difference Match, proposed by Hazem Hassan:
=TOCOL(
MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
SEQUENCE(
LEN(
x
)
),
TAKE(
IF(
ABS(
TOCOL(
MID(
x,
a,
1
) -
MID(
x,
a + 1,
1
),
3
)
) = DROP(
a,
-1
),
x,
1 / 0
),
-1
)
)
)
),
3
)Excel solution 21 for Sequential Digit Difference Match, proposed by Hazem Hassan:
=TOCOL(MAP(A2:A10,LAMBDA(x,LET(a,LEN(x),b,MID(x,SEQUENCE(a),1),IF(SUM((ABS(TOCOL(b-DROP(b,1),3))=SEQUENCE(a-1))*1)=a-1,x,1/0)))),3)Excel solution 22 for Sequential Digit Difference Match, proposed by Luis Couto:
=TOCOL(
A2:A10/MAP(
A2:A10,
LAMBDA(
x,
LET(
l,
SEQUENCE(
LEN(
x
)
),
AND(
TOCOL(
ABS(
DROP(
DROP(
MID(
x,
l,
1
),
1
)-MID(
x,
l,
1
),
-1
)
),
3
)=DROP(
l,
-1
)
)
)
)
),
3
)Solving the challenge of Sequential Digit Difference Match with Python
_x000D_Python solution 1 for Sequential Digit Difference Match, proposed by Jan Willem Van Holst:
in Python
import pandas as pd
df = pd.read_csv(r"C:JWLENOVOPYTHONExcel_Challenge_364.csv", sep=";")
mylist=df['Number'].to_list()
def fxAbsDiff(inputNumber):
unpack = [int(x) for x in [*str(inputNumber)]]
result = False
diffList = []
for i in range(1,len(unpack)):
diffList.append(abs(unpack[i]-unpack[i-1]))
if diffList == list(range(min(diffList),max(diffList)+1)):
result = True
return result
[x for x in mylist if fxAbsDiff(x)]
Solving the challenge of Sequential Digit Difference Match with R
_x000D_R solution 1 for Sequential Digit Difference Match, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/364 Difference Consecutive Digits.xlsx", range = "A1:A10")
test = read_excel("Excel/364 Difference Consecutive Digits.xlsx", range = "B1:B6")
check_seq_diff = function(x) {
digits = as.character(strsplit(as.character(x), "")[[1]])
diffs = map2_dbl(digits[-length(digits)],
digits[-1],
~abs(as.numeric(.x) - as.numeric(.y)))
all(diffs == 1:length(diffs))
}
result = input %>%
mutate(test = map_lgl(Number, check_seq_diff)) %>%
filter(test) %>%
select(-test)