List first 500 numbers starting with 101 which don’t end with 0 and are divisible at least once by removing one digit at a time from the number. Also list their divisors if more than 1. Ex. 405 = Removing 1st digit yields 05 which divides 405, removing 0 yields 45 which divides 405 and removing 5 yields 40 which doesn’t divide 405. Hence answer is 5 and 45.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 531
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Remove Digit to Make Divisible with Power Query
Power Query solution 2 for Remove Digit to Make Divisible, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = List.Generate(
() => [a = 101, z = 0, r = null],
each [z] <= 500,
each [
a = [a] + 1,
b = Text.From(a),
c = not Text.EndsWith(b, "0"),
d = Text.Length(b) - 1,
e = List.Transform(
{0 .. d},
(f) =>
[
rr = Text.RemoveRange(b, f, 1),
nb = Number.From(rr),
dv = Number.Mod(a, nb),
r = if dv = 0 then Text.From(nb) else null
][r]
),
g = List.RemoveMatchingItems(List.RemoveNulls(e), {"1"}),
h = not List.IsEmpty(g),
i = Text.Combine(List.Distinct(g), ", "),
j = c and h,
r = if j then [Number = a, Divisors = i] else null,
z = [z] + Number.From(j)
],
each [r]
),
Filter = List.RemoveNulls(Source),
Return = Table.FromRecords(Filter)
in
ReturnPower Query solution 3 for Remove Digit to Make Divisible, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = List.Generate(
() => [x = 101, y = 0, k = false, z = {}],
each [y] <= 500,
each [
x = [x] + 1,
z =
let
a = Text.From(x),
b = List.Transform(
{0 .. Text.Length(a) - 1},
each Number.From(Text.Combine(List.RemoveRange(Text.ToList(a), _, 1)))
),
c = List.Select(b, each Number.Mod(x / _, 1) = 0)
in
c,
k = if List.Count(z) = 0 or Text.EndsWith(Text.From(x), "0") or z = {1} then false else true,
y = if k then [y] + 1 else [y]
],
each {[x], [z], [k]}
),
Sol = Table.Combine(
List.Transform(
List.Select(Source, each _{2} = true),
each Table.FromRows(
{{_{0}, Text.Combine(List.Transform(List.Distinct(_{1}), Text.From), ", ")}},
{"Number", "Divisors"}
)
)
)
in
SolPower Query solution 4 for Remove Digit to Make Divisible, proposed by Ramiro Ayala Chávez:
let
A = List.Transform({101 .. 1000000}, Text.From),
B = List.Select(A, each Text.End(_, 1) <> "0"),
C = Table.FromColumns({List.Transform(B, Number.From)}, {"Number"}),
Fx = (x) =>
let
a = x,
b = Text.ToList(Text.From(a)),
c = List.Generate(
() => [i = 0],
each [i] < List.Count(b),
each [i = [i] + 1],
each Text.Combine(List.RemoveRange(b, [i], 1))
),
d = List.Transform(c, Number.From),
e = List.Repeat({a}, List.Count(d)),
f = List.Transform(
List.Positions(e),
each if Number.Mod(e{_} / d{_}, 1) = 0 then d{_} else null
),
g = List.Distinct(List.Transform(f, each if _ = 1 then null else _)),
h = Text.Combine(List.Transform(List.RemoveNulls(g), Text.From), ", ")
in
h,
D = Table.AddColumn(C, "Divisors", each Fx([Number])),
Sol = Table.FirstN(Table.SelectRows(D, each [Divisors] <> ""), 500)
in
SolPower Query solution 5 for Remove Digit to Make Divisible, proposed by Mihai Radu O:
let
p1 = [
lt = List.Transform,
tf = Text.From,
nf = Number.From,
ls = List.Select,
a = ls({102 .. 1000000}, (x) => Number.Mod(x, 10) > 0),
b = lt(
a,
(x) =>
Text.Combine(
List.Distinct(
ls(
lt(
{0 .. Text.Length(tf(x)) - 1},
(y) =>
[
_a = nf(Text.ReplaceRange(tf(x), y, 1, "")),
_b = Number.Mod(x, _a),
_c = lt(List.Zip({{_a}, {_b}}), (z) => if z{1} = 0 then tf(z{0}) else null){0}
][_c]
),
each _ <> "1"
)
),
", "
)
),
c = Table.FirstN(Table.SelectRows(Table.FromColumns({a, b}), each [Column2] <> ""), 500)
][c]
in
p1Solving the challenge of Remove Digit to Make Divisible with Excel
Excel solution 1 for Remove Digit to Make Divisible, proposed by Bo Rydobon 🇹🇭:
=LET(z,DROP(SORT(REDUCE(0,VSTACK(SEQUENCE(700,,2),SEQUENCE(250,,750,125)),LAMBDA(b,n,
IF(-RIGHT(n),LET(l,LEN(n),s,SEQUENCE(10)-1,
d,UNIQUE(DROP(REDUCE(0,SEQUENCE(l),LAMBDA(a,i,LET(j,REPLACE(n,i,,IF(i=1,DROP(s,1)&REPT(0,SEQUENCE(,7-l,1-(l>1))),s)),k,TOCOL(IFS(MOD(j,n)=0,--j),3),IF(ISERR(@k),a,VSTACK(a,IF({1,0},k,n)))))),1)),
IF(ISERR(@d),b,VSTACK(b,d))),b)))),1),GROUPBY(TAKE(z,,1),DROP(z,,1),ARRAYTOTEXT,,0))Excel solution 2 for Remove Digit to Make Divisible, proposed by John V.:
=LET(n,
SCAN(100,
SEQUENCE(
812723
),
LAMBDA(a,
v,
1+a+(RIGHT(
a
)="9"))),
i,
--REPLACE(
n,
{1,
2,
3,
4,
5,
6},
1,
),
a,
BYROW(IF((i1)*(MOD(
n/i,
1
)=0),
i,
""),
LAMBDA(
x,
TEXTJOIN(
", ",
,
UNIQUE(
x,
1
)
)
)),
z,
HSTACK(
IF(
a="",
"",
n
),
a
),
FILTER(
z,
a>""
)) Excel solution 3 for Remove Digit to Make Divisible, proposed by محمد حلمي:
=REDUCE(A2:B2,SEQUENCE(96000,,101),LAMBDA(a,v,
LET(
s,--REPLACE(v,SEQUENCE(LEN(v)),1,),
e,TEXTJOIN(", ",,
IF((s>1)*IF(s,INT(v/s)=v/s)*RIGHT(v),s,"")),
IF(e="",a,VSTACK(a,HSTACK(v,e))))))Excel solution 4 for Remove Digit to Make Divisible, proposed by Kris Jaganah:
=LET(p,SEQUENCE(10^6,,101),q,TOCOL(p/(MOD(p/10,1)<>0),3),r,MAP(q,LAMBDA(x,LET(a,SEQUENCE(LEN(x)),b,MAP(a,LAMBDA(y,REPLACE(x,y,1,""))),c,x/b,d,ARRAYTOTEXT(--FILTER(b,(c=INT(c))*(x<>c),"")),d))),TAKE(FILTER(HSTACK(q,r),--ISERR(r)=0),500))Excel solution 5 for Remove Digit to Make Divisible, proposed by Julian Poeltl:
=TAKE(LET(S,
SEQUENCE(
10^6,
,
101
),
SS,
FILTER(
S,
--RIGHT(
S
)<>0
),
M,
MAP(SS,
LAMBDA(N,
LET(R,
--REPLACE(
N,
SEQUENCE(
LEN(
N
)
),
1,
""
),
M,
MOD(
N/R,
1
),
TEXTJOIN(", ",
,
FILTER(R,
(M=0)*(R>1)))))),
N,
NOT(
ISERR(
M
)
),
H,
HSTACK(
FILTER(
SS,
N
),
FILTER(
M,
N
)
),
IFERROR(
--H,
H
)),
500)Excel solution 6 for Remove Digit to Make Divisible, proposed by Timothée BLIOT:
=LET(A,SEQUENCE(10^6)+99,B,FILTER(A,RIGHT(A)<>"0"), C,MAP(B, LAMBDA(x, LET(D,--REPLACE(x,SEQUENCE(LEN(x)),1,""), E,MOD(x,FILTER(D,D<>1)), ARRAYTOTEXT(FILTER(D,E=0,1/0))))), TAKE(HSTACK(FILTER(B,NOT(ISERROR(C))),TOCOL(C,3)),500))Excel solution 7 for Remove Digit to Make Divisible, proposed by Hussein SATOUR:
=LET(a,
SEQUENCE(
904000
)+100,
List,
FILTER(
a,
MOD(
a,
10
)>0
),
Calc,
MAP(List,
LAMBDA(x,
LET(c,
REPLACE(
x,
SEQUENCE(
LEN(
x
)
),
1,
""""
),
ARRAYTOTEXT(FILTER(c,
(MOD(
x,
c
)=0)*(--c>1)))))),
FILTER(
HSTACK(
List,
Calc
),
NOT(
ISERR(
Calc
)
)
))Excel solution 8 for Remove Digit to Make Divisible, proposed by Oscar Mendez Roca Farell:
=LET(
s,
SEQUENCE(
7.1^7,
,
101
),
f,
FILTER(
s,
MOD(
s,
10
)>0
),
m,
MAP(
f,
LAMBDA(
a,
LET(
s,
-REPLACE(
a,
SEQUENCE(
LEN(
a
)
),
1,
""
),
r,
TOCOL(
IFS(
MOD(
a,
s
)=0,
-s
),
2
),
IFERROR(
IFS(
AND(
r<>1
),
TEXTJOIN(
", ",
,
r
)
),
)
)
)
),
FILTER(
HSTACK(
f,
m
),
m<>0
)
)Excel solution 9 for Remove Digit to Make Divisible, proposed by Sunny Baggu:
=LET(
n,
SEQUENCE(
905000,
,
101
),
nu,
FILTER(
n,
RIGHT(
n
) <> "0"
),
_d,
MAP(
nu,
LAMBDA(
x,
LET(
_m,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
_s,
REPLACE(
x,
SEQUENCE(
LEN(
x
)
),
1,
""
) + 0,
_c,
MOD(
x,
_s
),
_t,
UNIQUE(
FILTER(
_s,
_c = 0
)
),
ARRAYTOTEXT(
FILTER(
_t,
_t > 1
)
)
)
)
),
TAKE(
HSTACK(
FILTER(
nu,
1 - ISERR(
_d
)
),
TOCOL(
_d,
3
)
),
500
)
)Excel solution 10 for Remove Digit to Make Divisible, proposed by Bilal Mahmoud kh.:
=DROP(LET(Test,
LAMBDA(x,
LET(m,
MAP(
SEQUENCE(
LEN(
x
),
,
0
),
LAMBDA(
n,
CONCAT(
LEFT(
x,
n
),
RIGHT(
x,
LEN(
x
)-n-1
)
)
)
),
TEXTJOIN(",",
TRUE,
UNIQUE(IF((--m<>1)*(INT(
x/m
)=(
x/m
)),
--m,
""))))),
REDUCE(
,
SEQUENCE(
903125,
,
101
),
LAMBDA(
x,
y,
IF(
--RIGHT(
y,
1
)<>0,
LET(
z,
Test(
y
),
IF(
z<>"",
VSTACK(
x,
HSTACK(
y,
z
)
),
x
)
),
x
)
)
)),
1)Excel solution 11 for Remove Digit to Make Divisible, proposed by Eddy Wijaya:
=LET(
a,
SEQUENCE(
905000,
,
102
),
notZ,
FILTER(
a,
RIGHT(
a,
1
)<>"0"
),
dbCalc,
HSTACK(notZ,
MAP(notZ,
LAMBDA(m,
LET(
sp,
MID(
m,
SEQUENCE(
LEN(
m
)
),
1
),
l_comb_ord,
REDUCE(
0,
sp,
LAMBDA(
a,
v,
VSTACK(
a,
a&v
)
)
),
genArr,
--DROP(
FILTER(
l_comb_ord,
LEN(
l_comb_ord
)>=COUNTA(
sp
)
),
-1
),
res,
TOROW(UNIQUE(SORT(FILTER(genArr,
(MOD(
m,
genArr
)=0)*(genArr<>1)),
1,
1))),
IF(
COUNTA(
res
)=1,
res,
TEXTJOIN(
", ",
,
res
)
))))),
FILTER(
dbCalc,
ISERROR(
CHOOSECOLS(
dbCalc,
-1
)
)=FALSE
))Excel solution 12 for Remove Digit to Make Divisible, proposed by Nonbow Wu:
=LET(s,
NOW(),
foo,
LAMBDA(n,
LET(d,
UNIQUE(
--REPLACE(
n,
SEQUENCE(
LEN(
n
)
),
1,
)
),
ARRAYTOTEXT(TOROW(IFS((MOD(
n/d,
1
)=0)*MOD(
n,
10
)*(d>1),
d),
2)))),
xn,
LET(
k,
10^4,
i,
SEQUENCE(
8
),
a,
VSTACK(
1,
2^i,
5^i
),
p,
{9,
21,
39,
3,
7,
11,
13,
17,
19,
23,
29,
31,
37,
43,
47,
59,
73},
x,
a*p,
xa,
TOCOL(x/(x0,
nn
),
2
),
TOCOL(
ra,
2
)
),
VSTACK((NOW()-s)*86400,
A2:B2,
ans)) Solving the challenge of Remove Digit to Make Divisible with Python
Python solution 1 for Remove Digit to Make Divisible, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "531 Numbers Divisible after Removing a Digit.xlsx"
test = pd.read_excel(path, skiprows=1)
def transform_number(number):
return [int(str(number)[:i] + str(number)[i+1:]) for i in range(len(str(number)))]
numbers = pd.DataFrame({'number': range(101, 1000001)})
numbers = numbers[numbers['number'] % 10 != 0]
numbers['new_number'] = numbers['number'].apply(transform_number)
numbers = numbers.explode('new_number')
numbers['new_number'] = pd.to_numeric(numbers['new_number'])
numbers = numbers.drop_duplicates(subset=['number', 'new_number'])
filtered_numbers = numbers[(numbers['number'] % numbers['new_number'] == 0) & (numbers['new_number'] != 1)]
result = filtered_numbers.groupby('number').agg(divisors=('new_number', lambda x: ', '.join(map(str, x)))).reset_index()
n1 = result.head(500)
n1.columns = test.columns
print(all(n1.eq(test))) # True
Solving the challenge of Remove Digit to Make Divisible with Python in Excel
Python in Excel solution 1 for Remove Digit to Make Divisible, proposed by Alejandro Campos:
def find_valid_numbers(limit):
valid_numbers = []
number = 102
while len(valid_numbers) < limit:
str_num = str(number)
if str_num[-1] != '0' and not str_num.endswith('01'):
divisors = []
for i in range(len(str_num)):
reduced_number = int(str_num[:i] + str_num[i+1:])
if reduced_number != 0 and number % reduced_number == 0:
divisors.append(reduced_number)
if divisors:
divisors.sort()
divisors_str = ', '.join(map(str, divisors))
valid_numbers.append([number, divisors_str])
number += 1
return pd.DataFrame(valid_numbers, columns=['Number', 'Divisors'])
df = find_valid_numbers(500)
df
Python in Excel solution 2 for Remove Digit to Make Divisible, proposed by Abdallah Ally:
# Perform data manipulation
values = []
number = 101
while len(values) != 500:
if str(number)[-1] != '0':
str_num = str(number)
combs = [str_num[:i] + str_num[i+1:] for i in range(len(str_num))]
combs = [x for x in sorted(int(x) for x in combs) if x not in [0, 1]]
combs = [str(x) for x in combs if not number % x]
divisors = ', '.join(combs)
if divisors:
values.append([number, divisors])
number += 1
df = pd.DataFrame(data=values, columns=['Number', 'Divisors'])
df
Solving the challenge of Remove Digit to Make Divisible with R
R solution 1 for Remove Digit to Make Divisible, proposed by Konrad Gryczan, PhD:
Took about 13-15 seconds just like Python one, not 1 minute.
library(tidyverse)
library(readxl)
path = "Excel/531 Numbers Divisible after Removing a Digit.xlsx"
test = read_excel(path, range = "A2:B502")
find_divisors = function(n) {
digits = strsplit(as.character(n), NULL)[[1]]
divisors = integer(0)
for (i in seq_along(digits)) {
reduced_number = as.integer(paste0(digits[-i], collapse = ""))
if (reduced_number > 1 && n %% reduced_number == 0) {
divisors = c(divisors, reduced_number)
}
}
return(divisors)
}
find_numbers = function(count = 500, start = 101) {
result = list()
number = start
while (length(result) < count) {
if (number %% 10 != 0) {
divisors = find_divisors(number)
if (length(divisors) > 0) {
result[[as.character(number)]] = divisors
}
}
number = number + 1
}
return(result)
}
numbers_with_divisors = find_numbers()
numbers_with_divisors = map(numbers_with_divisors, unique)
df = tibble(
Number = names(numbers_with_divisors) %>% as.numeric(),
Divisors = I(unname(numbers_with_divisors))
) %>%
mutate(Divisors = map_chr(Divisors, ~ paste(.x, collapse = ", ")))
identical(df, test) # TRUE
R solution 2 for Remove Digit to Make Divisible, proposed by Konrad Gryczan, PhD:
library(charcuterie)
library(tidyverse)
library(readxl)
path = "Excel/531 Numbers Divisible after Removing a Digit.xlsx"
test = read_excel(path, range = "A2:B502")
transform_number <- function(number) {
num_str <- as.character(number)
num_split <- chars(num_str)
len <- length(num_split)
result <- character(len)
for (i in seq_along(num_split)) {
result[i] <- paste0(num_split[-i], collapse = "")
}
result <- paste(result, collapse = ", ")
return(result)
}
transform_number <- Vectorize(transform_number)
numbers <- data.frame(number = 101:1000000) %>%
filter(number %% 10 != 0)
n1 = numbers %>%
mutate(new_number = map_chr(number, transform_number)) %>%
separate_rows(new_number, sep = ", ") %>%
distinct() %>%
mutate(new_number = as.numeric(new_number)) %>%
filter(number %% new_number == 0, new_number != 1) %>%
summarise(divisors = paste(new_number, collapse = ", "), .by = "number") %>%
head(500)
all.equal(n1, test, check.attributes = FALSE)
#> [1] TRUE
&&