One Child Palindromes – Find all unique substrings of a number excluding 0 and if one and only one substring is divisible by the length of the number, then this is called One Child number. If more than one substring are divisible by the length of the number, that that number needs to be discarded. Find first 1000 One Child numbers which are Palindromes also. Ex. 202 => Its substrings are 2, 0, 2, 20, 02, 202 => Take unique and exclude 0 => 2, 20, 202 => None of these are divisible by length of 202 i.e. 3, hence 202 is not an answer 8338 => Its substrings are 8, 3, 3, 8, 83, 33, 38, 833, 338, 8338 => Unique ones are 8, 3, 83, 33, 38, 833, 338, 8338 => Out of these only 8 is divisible by length of 8338 i.e. 4. Hence, 8338 is an answer.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 562
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Palindrome One-Child Numbers with Power Query
Power Query solution 1 for Palindrome One-Child Numbers, proposed by Abdallah Ally:
let
isOneChildPalindrome = (value as number) as logical =>
[
a = Text.From(value),
b = Text.Length(a),
c = List.TransformMany(
{0 .. b - 1},
each {1 .. b},
(x, y) => Number.From(Text.Middle(a, x, y))
),
d = List.Select(List.Distinct(c), each _ > 0 and Number.Mod(_, b) = 0),
e = Text.Reverse(a) = a and List.Count(d) = 1 and value >= 10
][e],
Result = List.RemoveNulls(
List.Generate(
() => [num = 10, cond = isOneChildPalindrome(num), count = 0],
each [count] <= 1000,
each [num = [num] + 1, cond = isOneChildPalindrome(num), count = [count] + Byte.From(cond)],
each if [cond] then [num] else null
)
)
in
ResultPower Query solution 2 for Palindrome One-Child Numbers, proposed by Mihai Radu O:
let
fct = (nr) =>
[
lt = List.Transform,
a = Text.From(nr),
b = Text.Reverse(a) = a,
l = Text.Length(a),
c = List.Select(
List.Distinct(
List.Combine(
lt({1 .. l}, (x) => lt({0 .. l - x}, (y) => Number.From(Text.Middle(a, y, x))))
)
),
each _ > 0
),
d = List.Sum(lt(c, (x) => Byte.From(Number.Mod(x, l) = 0))) = 1,
e = b and d
][e],
a = List.RemoveNulls(
List.Generate(
() => [x = 10, k = fct(x), y = 0],
each [y] <= 1000,
each [x = [x] + 1, k = fct(x), y = [y] + Byte.From(k)],
each if [k] then [x] else null
)
)
in
aPower Query solution 3 for Palindrome One-Child Numbers, proposed by Tyler N.:
let
a = List.Transform(
{10 .. 7503057},
each
let
b = _,
c = 10,
d = Text.From(_),
e = Text.Length(d),
f = d = Text.Reverse(d),
g = List.Count(
List.Select(
List.Distinct(
List.Combine(
List.Transform(
{1 .. e},
each
let
h = _ - 1,
i = {1 .. e - _ + 1},
j = List.Transform(
i,
each
let
k = Number.Power(c, _),
l = Number.IntegerDivide(Number.Mod(b, k * Number.Power(c, h)), k / c)
in
l
)
in
j
)
)
),
each _ <> 0 and Number.Mod(_ / e, 1) = 0
)
)
= 1
in
if f and g then b else null
)
in
List.RemoveNulls(a)Solving the challenge of Palindrome One-Child Numbers with Excel
Excel solution 1 for Palindrome One-Child Numbers, proposed by Bo Rydobon 🇹🇭:
=LET(n,SEQUENCE(999),m,BYROW(MID(n,4-SEQUENCE(,3),1),CONCAT),
SORT(TOCOL(MAP(n&HSTACK("",SEQUENCE(,10,0))&m,LAMBDA(i,LET(l,LEN(i),s,SEQUENCE(l),j,UNIQUE(--TOCOL(MID(i,s,TOROW(s)))),i/(SUM(N(MOD(FILTER(j,j),l)=0))=1)))),3)))Excel solution 2 for Palindrome One-Child Numbers, proposed by John V.:
=LET(s,
SEQUENCE,
e,
TOCOL,
a,
s(
999
),
b,
BYROW(
MID(
a,
{3,
2,
1},
1
),
CONCAT
),
TAKE(e(MAP(SORT(
--e(
a&HSTACK(
"",
s(
,
10
)-1
)&b
)
),
LAMBDA(x,
LET(n,
LEN(
x
),
i,
e(
--MID(
x,
s(
n
),
1+n-s(
,
8
)
),
2
),
x/(SUM(
N(
MOD(
UNIQUE(
FILTER(
i,
i
)
),
n
)=0
)
)=1)))),
2),
1000))Excel solution 3 for Palindrome One-Child Numbers, proposed by Kris Jaganah:
=LET(a,SEQUENCE(999),b,MAP(a,LAMBDA(x,CONCAT(MID(x,SEQUENCE(LEN(x),,LEN(x),-1),1)))),c,DROP(UNIQUE(SORT(--TOCOL(VSTACK(a&TOROW(TAKE(a,10)-1)&b,a&b)))),-1),TAKE(UNIQUE(TOCOL(MAP(c,LAMBDA(v,LET(e,LEN(v),f,UNIQUE(--TOCOL(MID(v,SEQUENCE(e),SEQUENCE(,e)))),g,f/e,v/(SUM((INT(g)=g)*(f>0))=1)))),3)),1000))Excel solution 4 for Palindrome One-Child Numbers, proposed by Julian Poeltl:
=LET(L,
LAMBDA(
A,
CONCAT(
MID(
A,
SEQUENCE(
LEN(
A
),
,
LEN(
A
),
-1
),
1
)
)
),
S,
SEQUENCE(
1000
),
N,
SORT(
--TOCOL(
HSTACK(
MAP(
S&SEQUENCE(
,
10,
0
),
LAMBDA(
A,
A&L(
LEFT(
A,
LEN(
A
)-1
)
)
)
),
MAP(
S,
LAMBDA(
A,
A&L(
A
)
)
)
)
)
),
TAKE(FILTER(N,
MAP(N,
LAMBDA(A,
SUM(--(MOD(
LET(
U,
UNIQUE(
--TOCOL(
MID(
A,
SEQUENCE(
LEN(
A
)
),
LEN(
A
)-SEQUENCE(
,
LEN(
A
),
0
)
)
)
),
FILTER(
U,
U<>0
)
),
LEN(
A
)
)=0))))=1),
1000))Excel solution 5 for Palindrome One-Child Numbers, proposed by Timothée BLIOT:
=LET(A,SEQUENCE(999),B,SORT(TOCOL(--(A&HSTACK("",SEQUENCE(,10)-1) &MAP(A,LAMBDA(x,CONCAT(MID(x,LEN(x)+1-SEQUENCE(LEN(x)),1))))))),
F,LAMBDA(x,UNIQUE(--REDUCE(x,SEQUENCE(LEN(x)-1),LAMBDA(w,v, VSTACK(w,MID(x,SEQUENCE(LEN(x)+1-v),v)))))), TAKE(FILTER(B,MAP(B, LAMBDA(x,SUM(--(MOD(FILTER(F(x),F(x)<>0),LEN(x))=0))=1))),10^3))
Explaination: the function creates palindromes up to a billion by combining sequential numbers (left and right)Excel solution 6 for Palindrome One-Child Numbers, proposed by Sunny Baggu:
=LET(
_s,
SEQUENCE(
7503057 / 300,
300,
111
),
_c,
MAP(
_s,
LAMBDA(x,
LET(
l,
LEN(
x
),
_a,
SORT(
UNIQUE(
--TOCOL(
MID(
x,
SEQUENCE(
l
),
SEQUENCE(
,
l
)
)
)
)
),
SUM(N(MOD(TOCOL(_a / (_a <> 0),
3),
l) = 0)) = 1
)
)
),
_d,
TOCOL(_s / (--_c),
3),
TOCOL(
IF(
MAP(
_d,
LAMBDA(
a,
CONCAT(
MID(
a,
LEN(
a
) + 1 - SEQUENCE(
LEN(
a
)
),
1
)
) + 0 = a
)
),
_d,
y
),
3
)
)Excel solution 7 for Palindrome One-Child Numbers, proposed by Nonbow Wu:
=LET(
PAL,LAMBDA(fn,n, IF(n=1,SEQUENCE(9),
LET(p,fn(fn,n-1),i,n/2,
TOCOL(REPLACE(p,i+1,,IF(ISEVEN(n),MID(p,i,1),SEQUENCE(,10,0))))))),
OCP,LAMBDA(x,LET(
k,LEN(x), c,SEQUENCE(k),
d,UNIQUE(TOCOL(--MID(x,c,TOROW(c))))/k,
SUM((d>0)*(MOD(d,1)=0))=1 )),
n,REDUCE(0,SEQUENCE(6)+1,LAMBDA(a,i,VSTACK(a,--PAL(PAL,i)))),
TOCOL(MAP(n,LAMBDA(v,IFS(OCP(v),v))),2) )Solving the challenge of Palindrome One-Child Numbers with Python
Python solution 1 for Palindrome One-Child Numbers, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "562 One Child Palindromes.xlsx"
test = pd.read_excel(path, usecols="A").squeeze().tolist()
def has_one_child(n):
nchar = len(str(n))
substrings = {int(str(n)[i:j].lstrip('0')) for i in range(len(str(n))) for j in range(i + 1, len(str(n)) + 1) if str(n)[i:j].lstrip('0')}
substrings = [i for i in substrings if i != 0 and i % nchar == 0]
return len(substrings) == 1
def is_palindromic(n):
n = str(n)
return n == n[::-1] and len(n) > 1
def is_palindromic_and_has_one_child(n):
return is_palindromic(n) and has_one_child(n)
def find_first_1000():
result, n = [], 0
while len(result) < 1000:
n += 1
if is_palindromic_and_has_one_child(n):
result.append(n)
return result
result = find_first_1000()
print(result == test) # True
Python solution 2 for Palindrome One-Child Numbers, proposed by Abdallah Ally:
Same implementation takes less time in Power Query and longer in Python
import pandas as pd
from datetime import datetime
from itertools import islice, count
def is_one_child_palindrome(number):
a = str(number)
b = len(a)
c = [int(a[i : i + j]) for i in range(b) for j in range(1, b + 1)]
d = [n for n in set(c) if n > 0 and not n % b]
return len(d) == 1 and number == int(a[::-1])
# Perform data manipulation
start = datetime.now()
numbers = list(islice(filter(is_one_child_palindrome, count(10)), 1000))
df = pd.DataFrame({'Numbers': numbers})
end = datetime.now()
minutes = int((end - start).total_seconds() // 60)
seconds = int(((end - start).total_seconds() % 60))
print(f'Time taken: {minutes} minutes and {seconds} seconds')
print(df)
Solving the challenge of Palindrome One-Child Numbers with Python in Excel
Python in Excel solution 1 for Palindrome One-Child Numbers, proposed by Alejandro Campos:
def fct(nr):
a = str(nr)
b = a == a[::-1]
l = len(a)
c = list(set(
int(a[i:j]) for i in range(l) for j in range(i + 1, l + 1) if int(a[i:j]) > 0
))
d = sum(1 for x in c if x % l == 0) == 1
return b and d
result = []
x = 10
y = 0
while y <= 1000:
if fct(x):
result.append(x)
x += 1
y += 1 if fct(x) else 0
result = [x for x in result if x is not None]
result
Solving the challenge of Palindrome One-Child Numbers with Excel VBA
Excel VBA solution 1 for Palindrome One-Child Numbers, proposed by Md. Zohurul Islam:
Sub ExcelBI_Excel_Challenge562()
'Find One Child Palindromes
Dim i As Long
Dim count As Long
Dim lastRow As Long
Dim strNum As String
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("vbas")
'headers
ws.Range("A1") = "VBA Solution"
count = 0
Dim startRange As Long
' Loop until we find 1000 One Child Palindromes
Do While count < 1000
strNum = CStr(startRange)
If IsPalindrome(strNum) Then
If IsOneChild(strNum) Then
ws.Cells(lastRow, 1).Value = startRange
lastRow = lastRow + 1
count = count + 1
End If
End If
startRange = startRange + 1
Loop
End Sub
&&&