Find the next 3 Palindrome numbers for the given numbers where any digit is repeated by the numbers given in Repeats column. The repetitions need to be exact not at least. So, if 3 is there in repeat column, a digit needs to be repeated by 3 times not at least 3 times.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 534
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Next Palindrome with Repeats with Power Query
Power Query solution 1 for Next Palindrome with Repeats, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Record = Table.AddColumn(
Source,
"R",
each [
N = [#"No. of Digits"],
Rp = [Repeats],
G = List.Generate(
() => [a = N, i = false, z = 0],
(f) => f[z] < 3,
(f) => [
a = f[a] + 1,
b = Text.From(a),
c = Text.Reverse(b) = b,
d = Text.ToList(b),
e = List.Distinct(d),
g = List.Select(e, (x) => List.Count(Text.PositionOf(b, x, 2)) = Rp),
h = List.Count(g) > 0,
i = c and h,
z = f[z] + Number.From(i)
],
(f) => if f[i] then f[a] else null
),
Rn = List.RemoveNulls(G),
C = List.Count(G),
A = Rn & {N + C},
R = Record.FromList(A, {"N1", "N2", "N3"})
][R]
),
Return = Table.ExpandRecordColumn(Record, "R", {"N1", "N2", "N3"})
in
ReturnSolving the challenge of Next Palindrome with Repeats with Excel
Excel solution 1 for Next Palindrome with Repeats, proposed by Bo Rydobon 🇹🇭:
=DROP(REDUCE(0,B2:B9,LAMBDA(a,r,LET(n,@+A9:r,l,LEN(n)/2,VSTACK(a,TAKE(TOROW(MAP(IF(ISODD(r)*ISEVEN(l*2),1&REPT(0,l),
LEFT(n,ROUNDUP(l,)))+SEQUENCE(50)-1,LAMBDA(x,LET(m,MID(x,-SORT(-SEQUENCE(l)),1),c,--CONCAT(x,m),IFS((c>n)*OR(LEN(c)-LEN(SUBSTITUTE(c,m,))=r),c)))),3),,3))))),1)Excel solution 2 for Next Palindrome with Repeats, proposed by Bo Rydobon 🇹🇭:
=DROP(REDUCE(0,
B2:B9,
LAMBDA(a,
r,
LET(n,
@+A9:r,
l,
LEN(
n
),
m,
--IF(rn
),
,
3
)
)))),
1) Excel solution 3 for Next Palindrome with Repeats, proposed by Bo Rydobon 🇹🇭:
=DROP(REDUCE(0,B2:B9,LAMBDA(a,r,LET(n,@+A9:r,l,LEN(n),m,--MAP(LEFT(n,MAX(EVEN(l)/2,r/(ISEVEN(r)+1)))+{0,1,2,3},LAMBDA(a,a&CONCAT(MID(a,-SORT(-SEQUENCE(LEN(a)-ISODD(l))),1)))),
VSTACK(a,TAKE(FILTER(m,m>n),,3))))),1)Excel solution 4 for Next Palindrome with Repeats, proposed by John V.:
=LET(
z,
LAMBDA(
z,
n,
r,
LET(
l,
LEN(
n
),
s,
SEQUENCE(
l
),
d,
-MID(
n,
s,
1
),
IF(
OR(
FREQUENCY(
d,
d
)=r
)*AND(
d=-MID(
n,
1+l-s,
1
)
),
n,
z(
z,
1+n,
r
)
)
)
),
DROP(
REDUCE(
A2:A9,
{1;2;3},
LAMBDA(
a,
v,
HSTACK(
a,
MAP(
TAKE(
a,
,
-1
),
B2:B9,
LAMBDA(
x,
y,
z(
z,
1+x,
y
)
)
)
)
)
),
,
1
)
)Excel solution 5 for Next Palindrome with Repeats, proposed by محمد حلمي:
=DROP(
REDUCE(
0,
B2:B9,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
s,
@+v:A9+SEQUENCE(
,
51000
),
TAKE(
FILTER(
s,
MAP(
s,
LAMBDA(
a,
LET(
i,
LEN(
a
),
s,
SEQUENCE(
i
),
e,
MID(
a,
s,
1
),
AND(
SUM(
N(
MMULT(
N(
e=TOROW(
e
)
),
s^0
)=v
)
)=v,
a=--CONCAT(
MID(
a,
i+1-s,
1
)
)
)
)
)
),
{"",
"",
""}
),
,
3
)
)
)
)
),
1
)Excel solution 6 for Next Palindrome with Repeats, proposed by Julian Poeltl:
=DROP(REDUCE(0,
A2:A9,
LAMBDA(A,
B,
VSTACK(A,
LET(S,
B+SEQUENCE(
400000
),
L,
LEN(
S
),
P,
TOCOL(
MAP(
S,
L,
LAMBDA(
A,
B,
LET(
L,
LEFT(
A,
B/2
),
C,
CONCAT(
MID(
A,
SEQUENCE(
B/2,
,
B,
-1
),
1
)
),
IF(
L=C,
A,
X
)
)
)
),
3
),
TAKE(TOROW(FILTER(P,
MAP(P,
LAMBDA(A,
SUM(--(XLOOKUP(
B,
A2:A9,
B2:B9
)=(LEN(
A
)-LEN(
SUBSTITUTE(
A,
SEQUENCE(
,
10,
0
),
""
)
)))))))),
,
3))))),
1)Excel solution 7 for Next Palindrome with Repeats, proposed by Timothée BLIOT:
=DROP(REDUCE(0,
SEQUENCE(
8
),
LAMBDA(w,
v,
LET(A,
INDEX(
A2:A9,
v,
) +SEQUENCE(
400000
),
VSTACK(w,
TOROW(TAKE(FILTER(A,
MAP(A,
LAMBDA(x,
AND(--CONCAT(
MID(
x,
LEN(
x
)+1-SEQUENCE(
LEN(
x
)
),
1
)
)=x,
SUM(--((LEN(
x
)-LEN(
SUBSTITUTE(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
""
)
))=INDEX(
B2:B9,
v,
)))>0)))),
3),
3))))),
1)Excel solution 8 for Next Palindrome with Repeats, proposed by Sunny Baggu:
=LET(
t, A2:B9,
DROP(
REDUCE(
"",
SEQUENCE(ROWS(t)),
LAMBDA(a, v,
VSTACK(
a,
LET(
_g1, INDEX(t, v, 1),
_g2, INDEX(t, v, 2),
LET(
_n, SEQUENCE(363541, , _g1),
l, LAMBDA(a, MID(a, LEN(a) + 1 - SEQUENCE(LEN(a)), 1)),
_r, MAP(_n, LAMBDA(x, CONCAT(l(x)) + 0)),
_n1, FILTER(_n, _n = _r),
_c2, MAP(_n1, LAMBDA(y, LET(_m, l(y), _um, UNIQUE(_m), OR(MAP(_um, LAMBDA(a, SUM(N(_m = a)))) = _g2)))),
TOROW(TAKE(FILTER(_n1, _c2), 3))
)
)
)
)
),
1
)
)Excel solution 9 for Next Palindrome with Repeats, proposed by LEONARD OCHEA 🇷🇴:
=LET(n,
A2:A9,
r,
B2:B9,
P,
LAMBDA(P,
x,
y,
LET(l,
LEN(
x
),
s,
SEQUENCE(
l
),
e,
--MID(
x,
s,
1
),
IF((OR(
FREQUENCY(
e,
e
)=y
))*AND(
e=--MID(
x,
l-s+1,
1
)
),
x,
P(
P,
x+1,
y
)))),
DROP(
REDUCE(
n,
SEQUENCE(
3
),
LAMBDA(
i,
j,
HSTACK(
i,
MAP(
TAKE(
i+1,
,
-1
),
r,
LAMBDA(
a,
b,
P(
P,
a,
b
)
)
)
)
)
),
,
1
))Excel solution 10 for Next Palindrome with Repeats, proposed by Fabián Abdala:
=CONCATENAR(IZQUIERDA(A2;1);REPETIR("2";4);DERECHA(A2;1))Solving the challenge of Next Palindrome with Repeats with Python
Python solution 1 for Next Palindrome with Repeats, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "534 Palindrome Numbers with Digit Repeations.xlsx"
input = pd.read_excel(path, usecols="A:B")
test = pd.read_excel(path, usecols="C:E", names=["res1", "res2", "res3"])
def is_palindrome(n):
return str(n) == str(n)[::-1]
def has_repeated_digits(n, times):
return any(str(n).count(d) == times for d in str(n))
def find_next_palindromic_numbers(start_number, repeats):
found_numbers = []
num = start_number + 1
while len(found_numbers) < 3:
if is_palindrome(num) and has_repeated_digits(num, repeats):
found_numbers.append(num)
num += 1
return found_numbers
output = input.apply(lambda x: find_next_palindromic_numbers(x["No. of Digits"], x["Repeats"]), axis=1)
output = output.apply(pd.Series)
output.columns = ["res1", "res2", "res3"]
output = output.astype("int64")
print(output.equals(test)) # True
Solving the challenge of Next Palindrome with Repeats with Python in Excel
Python in Excel solution 1 for Next Palindrome with Repeats, proposed by Alejandro Campos:
def is_palindrome(n):
s = str(n)
return s == s[::-1]
def has_exact_repeats(n, repeats):
digit_count = {}
for digit in str(n):
digit_count[digit] = digit_count.get(digit, 0) + 1
return any(count == repeats for count in digit_count.values())
def next_palindromes_with_repeats(number, repeats, count=3):
palindromes = []
current_number = number + 1
while len(palindromes) < count:
if is_palindrome(current_number) and has_exact_repeats(current_number, repeats):
palindromes.append(current_number)
current_number += 1
return palindromes
df = xl("A1:B9", headers=True)
df[['Next Palindrome 1', 'Next Palindrome 2', 'Next Palindrome 3']] = df.apply(
lambda row: pd.Series(next_palindromes_with_repeats(row['Numbers'], row['Repeats'])),
axis=1
)
df
Python in Excel solution 2 for Next Palindrome with Repeats, proposed by Anshu Bantra:
import numpy as np
import itertools
# Return True if Number is Palindrome
def is_palindrome(num: int) -> bool:
str_num = str(num)
return str_num == str_num[::-1]
# Returns True is a digit in number is repeating x times
def is_x_repeating(num: int, digit: int) -> bool:
num = str(num)
lst = [num.count(x) for x in num]
return np.any(np.array(lst)==digit)
# Returns next 3 Palindromes with x matching digits
def x_palindromes(num: int, digits: int) -> list:
rng = range(num+1,num+100000001)
rng = filter(is_palindrome, rng)
rng = filter(lambda x: is_x_repeating(x, digits), rng)
return list(itertools.islice(rng, 3))
df = xl("A1:B9", headers=True)
pd.DataFrame(
df.apply(lambda x: x_palindromes(x['No. of Digits'], x['Repeats']), axis=1).to_list(),
columns=['Ans1', 'Ans2', 'Ans3']
)
Solving the challenge of Next Palindrome with Repeats with R
R solution 1 for Next Palindrome with Repeats, propose&d by Konrad Gryczan, PhD:
library(tidyverse)
library(stringi)
library(readxl)
path = "Excel/534 Palindrome Numbers with Digit Repeations.xlsx"
input = read_excel(path, range = "A1:B9")
test = read_excel(path, range = "C1:E9")
has_digit_repeated_n_times <- function(num, N) {
any(table(strsplit(as.character(num), "")[[1]]) == N)
}
is_palindrome <- function(num) {
as.character(num) == stri_reverse(as.character(num))
}
result <- input %>%
rowwise() %>%
mutate(
res = list(
seq(.data$`No. of Digits`, .data$`No. of Digits` + 1000000) %>%
keep(~ is_palindrome(.x)) %>%
keep(~ has_digit_repeated_n_times(.x, .data$Repeats)) %>%
head(3)
)
) %>%
unnest(res) %>%
ungroup()
res <- result %>%
mutate(nr = row_number(), .by = `No. of Digits`) %>%
select(-c(Repeats)) %>%
pivot_wider(names_from = nr, names_glue = "P_{nr}", values_from = res) %>%
select(-`No. of Digits`)
all.equal(test, res, check.attributes = FALSE)
#> [1] TRUE
&&