Find the smallest numbers whose product of digits is equal to numbers in column A. Ex. 28 => 47 is the smallest number where 4×7 = 28 If no such number is possible, write NP which means not possible.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 516
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Smallest Product Digit Number with Power Query
_x000D_Power Query solution 1 for Smallest Product Digit Number, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
S,
"Answer Expected",
each
let
L = List.Transform,
a = [Number],
b = L({11 .. 9999}, Text.From),
c = L(b, Text.ToList),
d = L(c, each L(_, Number.From)),
e = L(d, each if List.Product(_) = a then _ else null),
f = List.First(List.RemoveNulls(e)),
g = if f <> null then Text.Combine(L(f, Text.From)) else "NP"
in
g
)
in
SolPower Query solution 2 for Smallest Product Digit Number, proposed by Ahmed Ariem:
let
Fx = (x) =>
[
P = (x) =>
[
a = List.Buffer(Text.ToList(Text.From(x))),
b = List.Product(List.Transform(a, Number.FromText))
][b],
G = (x) =>
[
lst = List.Buffer(List.Numbers(1, 10000)),
v = List.Select(lst, (y) => P(y) = x),
s = List.Min(v, "NP")
][s],
F = G(x)
][F],
Source = Excel.CurrentWorkbook(){[Name = "tbl"]}[Content],
trans = Table.TransformColumns(Source, {"Number", Fx})
in
transSolving the challenge of Smallest Product Digit Number with Excel
_x000D_Excel solution 1 for Smallest Product Digit Number, proposed by John V.:
=IFNA(XMATCH(A2:A10,BYROW(-MID(ROW(1:5000)&111,{1,2,3,4},1),PRODUCT)),"NP")Excel solution 2 for Smallest Product Digit Number, proposed by محمد حلمي:
=IFNA(XMATCH(A2:A10,MAP(SEQUENCE(5000),LAMBDA(a,
PRODUCT(--MID(a,SEQUENCE(LEN(a)),1))))),"NP")Excel solution 3 for Smallest Product Digit Number, proposed by Kris Jaganah:
=IFNA(XMATCH(A2:A10,MAP(SEQUENCE(9999),LAMBDA(x,PRODUCT(--MID(x,SEQUENCE(LEN(x)),1))))),"NP")Excel solution 4 for Smallest Product Digit Number, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
A,
IFNA(
XMATCH(
A,
MAP(
SEQUENCE(
5000
),
LAMBDA(
A,
PRODUCT(
--MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)
)
)
)
),
"NP"
)
)
)Excel solution 5 for Smallest Product Digit Number, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
x,
TAKE(
FILTER(
SEQUENCE(
10^4
),
MAP(
SEQUENCE(
10^4
),
LAMBDA(
x,
PRODUCT(
--MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
)
)=x,
"NP"
),
1
)
)
)Excel solution 6 for Smallest Product Digit Number, proposed by Nikola Z Grujicic – Nikola Ž Grujičić:
=LET(
dd,
SEQUENCE(
10000
),
e,
MAP(
SEQUENCE(
10000
),
LAMBDA(
d,
PRODUCT(
--MID(
d,
SEQUENCE(
LEN(
d
),
,
,
1
),
1
)
)
)
),
MAP(
A2:A10,
LAMBDA(
a,
IFERROR(
TAKE(
FILTER(
dd,
e=a
),
1
),
"NP"
)
)
)
)Excel solution 7 for Smallest Product Digit Number, proposed by Sunny Baggu:
=LET(
s, SEQUENCE(5000),
f, MAP(
s,
LAMBDA(a,
PRODUCT(MID(a, SEQUENCE(LEN(a)), 1) + 0)
)
),
XLOOKUP(A2:A10, f, s, "NP")
)Excel solution 8 for Smallest Product Digit Number, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
SEQUENCE(
5000
),
f,
MAP(
s,
LAMBDA(
a,
PRODUCT(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
) + 0
)
)
) = a,
TAKE(
FILTER(
s,
f,
"NP"
),
1
)
)
)
)Excel solution 9 for Smallest Product Digit Number, proposed by ferhat CK:
=IFNA(
MAP(
A1:A9,
LAMBDA(
n,
MIN(
DROP(
REDUCE(
,
SEQUENCE(
n*10,
,
n
),
LAMBDA(
x,
y,
VSTACK(
x,
IF(
n=PRODUCT(
--MID(
y,
SEQUENCE(
LEN(
y
)
),
1
)
),
y
)
)
)
),
1
)
)
)
),
"NP"
)Excel solution 10 for Smallest Product Digit Number, proposed by Bilal Mahmoud kh.:
=MAP(A2:A10,LAMBDA(R,LET(a,LAMBDA(x,PRODUCT(--MID(x,SEQUENCE(LEN(x)),1))),b,MAP(SEQUENCE(9),LAMBDA(n,TEXTJOIN(",",,MAP(SEQUENCE(9),LAMBDA(m,IF(a(n&m)=R,n&m,TEXTJOIN(",",,MAP(SEQUENCE(9),LAMBDA(z,IF(a(n&m&z)=R,n&m&z,TEXTJOIN(",",,MAP(SEQUENCE(9),LAMBDA(s,IF(a(n&m&z&s)=R,n&m&z&s,"NP")))))))))))))),c,TOCOL(TEXTSPLIT(TEXTJOIN(",",,b),",")),LET(o,MIN(FILTER(--c,c<>"NP",0)),IF(o=0,"NP",o)))))Excel solution 11 for Smallest Product Digit Number, proposed by Imam Hambali:
=LET(
a, SEQUENCE(5000,,11),
b, MAP(a,LAMBDA(x, PRODUCT(MID(x,SEQUENCE(,LEN(x)),1)*1))),
XLOOKUP(A2:A10,b,a,"NP")
)Excel solution 12 for Smallest Product Digit Number, proposed by abdelaziz kamal allam:
=XLOOKUP(
A2:A10,
MAP(
SEQUENCE(
5000
),
LAMBDA(
a,
PRODUCT(
--MID(
a,
SEQUENCE(
,
LEN(
a
)
),
1
)
)
)
),
SEQUENCE(
5000
),
"np",
0,
1
)Solving the challenge of Smallest Product Digit Number with Python
_x000D_Python solution 1 for Smallest Product Digit Number, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "516 Product of Digits of Result is Equal to Number.xlsx"
input = pd.read_excel(path, usecols="A")
test = pd.read_excel(path, usecols="B")
def find_smallest_number_with_digit_product(n):
if n == 0:
return 10
if n == 1:
return 1
factors = []
for i in range(9, 1, -1):
while n % i == 0:
factors.append(i)
n //= i
return int(''.join(map(str, sorted(factors)))) if n == 1 else "NP"
result = [find_smallest_number_with_digit_product(n) for n in input["Number"]]
result_df = pd.DataFrame(result, columns=["Answer Expected"])
print(result_df.equals(test)) # True
Solving the challenge of Smallest Product Digit Number with Python in Excel
_x000D_Python in Excel solution 1 for Smallest Product Digit Number, proposed by Alejandro Campos:
def min_number_with_digit_product(target):
def get_factors(n):
if n < 10:
return [n]
for i in range(9, 1, -1):
if n % i == 0:
return get_factors(n // i) + [i]
return []
factors = get_factors(target)
if not factors or np.prod(factors) != target:
return 'NP'
return ''.join(map(str, sorted(factors)))
numbers = xl("A2:A10").to_numpy().tolist()
df = pd.DataFrame(numbers, columns=['Number'])
df['Result'] = df['Number'].apply(min_number_with_digit_product)
df
Python in Excel solution 2 for Smallest Product Digit Number, proposed by Anshu Bantra:
lst = xl("A2:A10").values
def multiples(num):
ans = [lvl1*10+lvl2 for lvl1 in rng for lvl2 in rng if lvl1*lvl2==num] +
[lvl1*100+lvl2*10+lvl3 for lvl1 in rng for lvl2 in rng for lvl3 in rng if lvl1*lvl2*lvl3==num] +
[lvl1*1000+lvl2*100+lvl3*10+lvl4 for lvl1 in rng for lvl2 in rng for lvl3 in rng for lvl4 in rng if lvl1*lvl2*lvl3*lvl4==num]
return min(ans) if ans else 'NP'
rng = range(2,10)
ans = [multiples(_) for _ in lst]
ans
Solving the challenge of Smallest Product Digit Number with R
_x000D_R solution 1 for Smallest Product Digit Number, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/516 Product of Digits of Result is Equal to Number.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10") %>%
mutate(`Answer Expected` = as.character(`Answer Expected`))
find_smallest_number_with_digit_product = function(n) {
if (n == 0) return(10)
if (n == 1) return(1)
factors = c()
for (i in 9:2) {
while (n %% i == 0) {
factors = c(factors, i)
n = n / i
}
}
if (n > 1) return("NP")
return(paste(sort(factors), collapse = ""))
}
result = input %>%
mutate(`Answer Expected` = map_chr(Number, find_smallest_number_with_digit_product)) %>%
select(2)
identical(result, test)
# [1] TRUE