An n digit number is Pandigital if it makes use of all the digits 1 to n exactly once. For ex. 12, 231, 4312…. List the first 100 Pandigital numbers which are prime numbers also.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 436
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List Prime Pandigital Numbers with Power Query
Power Query solution 1 for List Prime Pandigital Numbers, proposed by Bo Rydobon 🇹🇭:
let
Source = 2,
Pan = List.Transform(
List.Accumulate(
{"2" .. "7"},
{},
(a, i) =>
let
n = {"1" .. i}
in
a
& List.Accumulate(
n,
{""},
(s, l) =>
List.Combine(
List.Transform(
s,
each List.RemoveNulls(
List.Transform(n, (n) => if Text.Contains(_, n) then null else _ & n)
)
)
)
)
),
each Number.From(_)
),
Prime = List.FirstN(
List.Select(
Pan,
(n) =>
List.AllTrue(
List.Transform(
{2, 3, 5, 7}
& List.Combine(
List.Transform(
{2 .. Number.IntegerDivide(Number.Sqrt(n), 6) + 1},
each List.Transform({- 1, 1}, (d) => _ * 6 + d)
)
),
each Number.Mod(n, _) > 0
)
)
),
100
)
in
PrimePower Query solution 2 for List Prime Pandigital Numbers, proposed by Aditya Kumar Darak 🇮🇳:
let
MyFun = (Number as number) =>
let
Sqrt = Number.Sqrt(Number),
Int = Number.IntegerDivide(Sqrt, 1),
Seq = List.Buffer({3 .. Int}),
Mod = List.Transform(Seq, each Number.Mod(Number, _)),
TF = List.Min(Mod) <> 0
in
TF,
Generate = List.Generate(
() => [a = 3, c = false, z = 0],
each [z] < 1200,
each [
a = [a] + 2,
b = Text.From(a),
c = List.ContainsAll(Text.ToList(b), {"1" .. Text.From(Text.Length(b))})
and List.Contains({"1", "3", "7"}, Text.End(b, 1)),
z = [z] + Number.From(c)
]
),
Table = Table.FromRecords(Generate),
Output = List.Buffer(Table.SelectRows(Table, each [c])[a]),
Select = List.Select(Output, MyFun),
Return = List.FirstN(Select, 100)
in
ReturnPower Query solution 3 for List Prime Pandigital Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
a = 2600000,
b = List.Select(
List.Select({10 .. a}, each Number.IsOdd(_)),
each List.AllTrue(
List.Transform(
List.Transform({2 .. Int64.From(Number.Sqrt(_))}, (x) => Number.Mod(_, x)),
(y) => y <> 0
)
)
),
c = List.RemoveNulls(
List.Transform(
b,
each if Text.Contains(Text.From(_), "0") then null else Text.ToList(Text.From(_))
)
),
e = List.Select(
c,
each
let
a = {1 .. List.Count(_)},
b = List.Transform(List.Sort(_), Number.From),
d = a = b
in
d
),
f = List.Transform(e, each Number.From(Text.Combine(_)))
in
fPower Query solution 4 for List Prime Pandigital Numbers, proposed by Ramiro Ayala Chávez:
let
G = List.Generate(
() => [i = 2],
each [i] <= 9,
each [i = [i] + 1],
each
let
a = {1 .. [i]},
b = List.Accumulate(
List.Repeat({0}, List.Count(a) - 1),
a,
(s, c) => List.TransformMany(s, each a, (x, y) => Text.From(x) & Text.From(y))
),
c = List.Transform(b, each Text.ToList(_)),
d = List.Select(c, each List.Count(_) = List.Count(List.Distinct(_))),
e = List.Transform(d, each Text.Combine(_))
in
e
),
f = Table.TransformColumnTypes(
Table.FromList(List.Combine(G), null, {"Answer"}),
{"Answer", Int64.Type}
),
Fx = (x) =>
let
A = Number.IntegerDivide(Number.Sqrt(x), 1),
B = List.Transform(List.Buffer({2 .. A}), each Number.Mod(x, _)),
C = List.Min(B) <> 0
in
C,
g = Table.AddColumn(f, "L", each Fx([Answer])),
Sol = Table.FirstN(Table.SelectRows(g, each [L] = true)[[Answer]], 100)
in
SolSolving the challenge of List Prime Pandigital Numbers with Excel
Excel solution 1 for List Prime Pandigital Numbers, proposed by Bo Rydobon 🇹🇭:
=TAKE(
TOCOL(
MAP(
DROP(
REDUCE(
,
SEQUENCE(
7
),
LAMBDA(
a,
v,
VSTACK(
a,
SORT(
TOCOL(
REPLACE(
FILTER(
a,
LEN(
a
)=v-1
),
SEQUENCE(
,
v
),
,
v
)
)
)
)
)
),
1
),
LAMBDA(
k,
k/AND(
MOD(
k,
SEQUENCE(
k^0.5
)+1
)
)
)
),
3
),
100
)Excel solution 2 for List Prime Pandigital Numbers, proposed by John V.:
=LET(
f,
LAMBDA(
s,
b,
LET(
m,
REPT(
1,
b
)+BASE(
SEQUENCE(
s
),
b,
b
),
TOCOL(
m/MAP(
m,
LAMBDA(
x,
AND(
FIND(
SEQUENCE(
b
),
x
)
)
)
),
2
)
)
),
i,
VSTACK(
f(
255,
4
),
f(
4^9,
7
)
),
TAKE(
TOCOL(
i/MAP(
i,
LAMBDA(
x,
AND(
MOD(
x,
1+SEQUENCE(
x^0.5
)
)
)
)
),
2
),
100
)
)Excel solution 3 for List Prime Pandigital Numbers, proposed by محمد حلمي:
=LET(q,
LAMBDA(n,
LET(
s,
SEQUENCE(
n
)-1,
e,
MOD(
INT(
SEQUENCE(
n^n,
,
0
)/n^TOROW(
s
)
),
n
)+1,
r,
FILTER(
e,
IFERROR(
BYROW(
e,
LAMBDA(
a,
AND(
FIND(
SEQUENCE(
,
n
),
SORT(
a,
,
,
1
)
)
)
)
),
0
)
),
TOCOL(BYROW(FILTER(
r,
IFNA(
XMATCH(
DROP(
r,
,
n-1
),
{1,
3,
7}
),
0
)
),
LAMBDA(a,
LET(i,
CONCAT(
a
),
IFS(1-(OR(
MOD(
i,
SEQUENCE(
i^0.5
)+1
)=0
)),
i)))),
2))),
TAKE(
SORT(
--VSTACK(
q(
4
),
q(
7
)
)
),
100
))Excel solution 4 for List Prime Pandigital Numbers, proposed by Timothée BLIOT:
=LET(A,
SEQUENCE(
,
9
),
B,
REDUCE(
TOCOL(
A
),
SEQUENCE(
6
),
LAMBDA(
w,
v,
LET(
C,
FILTER(
w,
LEN(
w
)=v
),
VSTACK(
w,
TOCOL(
IF(
ISERR(
FIND(
A,
C
)
),
C&A,
1/0
),
3
)
)
)
)
),
D,
FILTER(
B,
MAP(
B,
LAMBDA(
x,
CONCAT(
SORT(
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
)=CONCAT(
SEQUENCE(
LEN(
x
)
)
)
)
)
),
TAKE(--FILTER(D,
MAP(D,
LAMBDA(x,
AND(x>1,
IF(INT(
x^0.5
)>=2,
SUM(--(MOD(
x,
SEQUENCE(
INT(
x^0.5
)-1,
,
2
)
)=0))=0,
1))))),
100))Excel solution 5 for List Prime Pandigital Numbers, proposed by LEONARD OCHEA 🇷🇴:
=LET(
m,
DROP(
BYROW(
IFNA(
REDUCE(
1,
SEQUENCE(
6
)+1,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
p,
FACT(
y-1
),
m,
IFNA(
HSTACK(
SEQUENCE(
p
)^0,
x+1
),
1
),
REDUCE(
m,
SEQUENCE(
y-1
),
LAMBDA(
a,
b,
LET(
c,
TAKE(
a,
-p
),
VSTACK(
TAKE(
a,
-p*b
),
IF(
c=b,
b+1,
IF(
c=b+1,
b,
c
)
)
)
)
)
)
)
)
)
),
""
),
CONCAT
),
1
),
TAKE(
TOCOL(
m/MAP(
m,
LAMBDA(
n,
LET(
x,
n/SEQUENCE(
n^0.5,
,
2
),
NOT(
OR(
x=INT(
x
)
)
)
)
)
),
2
),
100
)
)Solving the challenge of List Prime Pandigital Numbers with Python
Python solution 1 for List Prime Pandigital Numbers, proposed by Konrad Gryczan, PhD:
import pandas as pd
from itertools import permutations
from sympy import isprime
test = pd.read_excel("436 Pandigital Primes.xlsx", usecols="A", nrows=101)
def generate_pandigital(n):
digits = list(range(1, n+1))
digits = list(permutations(digits, n))
digits = [int(''.join(map(str, x))) for x in digits]
return digits
df = pd.DataFrame(columns=["numbers"])
for i in range(1, 8):
pandigitals = generate_pandigital(i)
df = df._append(pd.DataFrame({"numbers": pandigitals}))
df["is_prime"] = df["numbers"].apply(isprime)
df["numbers"] = df["numbers"].astype("int64")
result = df[df["is_prime"]].head(100).drop(columns="is_prime").reset_index(drop=True)
result.rename(columns={"numbers": "Answer Expected"}, inplace=True)
print(result.equals(test)) # True
Solving the challenge of List Prime Pandigital Numbers with Python in Excel
Python in Excel solution 1 for List Prime Pandigital Numbers, proposed by Abdallah Ally:
import pandas as pd
def is_prime(number):
if number < 2:
return False
for i in range(2, int(number ** 0.5) + 1):
if number % i == 0: # alternatively => if not number % i
return False
return True
def is_pandigital(number):
available_digits = [int(x) for x in str(number)]
expected_digits = list(range(1, len(str(number)) + 1))
return expected_digits == sorted(available_digits)
def prime_pandigital(n):
numbers = []
number = 10
while len(numbers) < n:
if is_prime(number) and is_pandigital(number):
numbers.append(number)
number += 1
return numbers
file_path = 'Excel_Challenge_436 - Pandigital Primes.xlsx'
df = pd.read_excel(file_path)
df['My Answer'] = pd.Series(prime_pandigital(100))
prin&t(f'nRequired Results:n{df}')
Python in Excel solution 2 for List Prime Pandigital Numbers, proposed by Abdallah Ally:
import pandas as pd
def is_prime(number):
if number < 2: return False
for i in range(2, int(number ** 0.5) + 1):
if not number % i: return False
return True
def is_pandigital(number):
available_digits = [int(x) for x in str(number)]
expected_digits = list(range(1, len(str(number)) + 1))
return expected_digits == sorted(available_digits)
def prime_pandigital(n):
numbers = filter(lambda x: is_prime(x) and is_pandigital(x), range(10, 10 ** 7))
return list(numbers)[ : n]
file_path = 'Excel_Challenge_436 - Pandigital Primes.xlsx'
df = pd.read_excel(file_path)
df['My Answer'] = pd.Series(prime_pandigital(100))
print(f'Required Results:n{df}')
Solving the challenge of List Prime Pandigital Numbers with R
R solution 1 for List Prime Pandigital Numbers, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(primes)
library(gtools)
test = read_excel("Excel/436 Pandigital Primes.xlsx", range = "A1:A101")
generate_pandigital = function(n) {
digits = 1:n
digits = permutations(n,n)
digits = apply(digits, 1, function(x) as.numeric(paste(x, collapse = "")))
return(digits)
}
df = data.frame(numbers = NA)
for (i in 1:7) {
pandigitals = generate_pandigital(i)
df = rbind(df, data.frame(numbers = pandigitals))
}
result = df %>%
mutate(is_prime = map_lgl(numbers, is_prime)) %>%
filter(is_prime) %>%
head(100)
Solving the challenge of List Prime Pandigital Numbers with Excel VBA
Excel VBA solution 1 for List Prime Pandigital Numbers, proposed by Julian Poeltl:
VBA-Code:
Sub CH()
Dim N, C As Long
N = 1001
Do
N = N + 2
If IsPrime(N) = True Then
If NoDouble(N) = True Then
If AllL(N) = True Then
Cells(C + 2, 2).Value = N
C = C + 1
End If
End If
End If
Loop Until C >= 100
End Sub
Function IsPrime(N) As Boolean
Dim i As Long
For i = 2 To Sqr(N)
If N Mod i = 0 Then
IsPrime = False
Exit Function
End If
Next
IsPrime = True
End Function
Function NoDouble(N) As Boolean
Dim L, R, T As Long
L = Len(N)
For R = 1 To L
For T = 1 To L
If Mid(N, R, 1) = Mid(N, T, 1) And R <> T Then
NoDouble = False
Exit Function
End If
Next
Next
NoDouble = True
End Function
Function AllL(N) As Boolean
Dim L, T As Long
L = Len(N)
For T = 1 To L
If InStr(1, Right(Str(N), L), Right(T, 1), vbTextCompare) = 0 Then
AllL = False
Exit Function
End If
Next
AllL = True
End Function
&&