Find the numbers which are divisible by their individual digits. Say if a number is 9864, it is divisible by all its individual digits i.e. 9, 8, 6 & 4.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 234
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Divisible by Each Digit with Power Query
Power Query solution 1 for Divisible by Each Digit, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.SelectRows(
Source,
each 0
= List.Sum(
List.Transform(
Text.ToList(Text.From([Number])),
(n) => Number.Mod([Number], Number.From(n))
)
)
)
in
Ans
Power Query solution 2 for Divisible by Each Digit, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Number],
m = (n, x) => Number.Mod(n, x),
d = (n, x) => Number.IntegerDivide(n, x),
S = List.Select(
Source,
(i) =>
List.AllTrue(
List.Generate(
() => [c = Number.RoundUp(Number.Log10(i)), n = d(i, 10), r = m(i, 10)],
each [c] > 0,
each [c = [c] - 1, n = d([n], 10), r = m([n], 10)],
each m(i, [r]) = 0
)
)
)
in
S
Power Query solution 3 for Divisible by Each Digit, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.SelectRows(
Source,
each List.AllTrue(
List.TransformMany(
{[Number]},
(x) => Text.ToList(Text.From(x)),
(x, y) => Number.Mod(x, Number.From(y)) = 0
)
)
)
in
Return
Power Query solution 4 for Divisible by Each Digit, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Answer = Table.SelectRows(
Source,
each [
ToText = Text.From([Number]),
Split = Text.ToList(ToText),
Check = List.Transform(Split, (f) => Number.Mod([Number], Number.From(f)) = 0),
Return = List.AllTrue(Check)
][Return]
)
in
Answer
Power Query solution 5 for Divisible by Each Digit, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.SelectRows(
Source,
(x) =>
let
a = Text.ToList(Number.ToText(x[Number])),
b = List.Transform(a, each Number.Mod(x[Number], Number.From(_)) = 0)
in
List.AllTrue(b)
)
in
Sol
Power Query solution 6 for Divisible by Each Digit, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.SelectRows(
Fonte,
each List.AllTrue(
List.Transform(
Text.ToList(Text.From([Number])),
(x) => not Text.Contains(Text.From([Number] / Number.From(x)), ",")
)
)
= true
)
in
res
Power Query solution 7 for Divisible by Each Digit, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ToText = Table.TransformColumnTypes(Source, {{"Number", type text}}),
ModNumber = Table.AddColumn(
ToText,
"Anwer",
each
let
a = ToText[Number],
b = Text.ToList([Number]),
c = Table.FromList(b, Splitter.SplitByNothing(), {"Divisor"}),
d = [Number],
e = Table.AddColumn(c, "Integer", each d),
f = Table.AddColumn(
e,
"Mod",
each Number.Mod(Number.From([Integer]), Number.From([Divisor]))
)[Mod],
g = List.Sum(f)
in
g
),
Result = Table.SelectRows(ModNumber, each [Anwer] = 0)[[Number]]
in
Result
Solving the challenge of Divisible by Each Digit with Excel
Excel solution 1 for Divisible by Each Digit, proposed by Bo Rydobon 🇹🇭:
=TOCOL(MAP(A2:A10,LAMBDA(a,a/AND(ISERR(FIND(".",a/MID(a,SEQUENCE(LEN(a)),1)))))),3)
Excel solution 2 for Divisible by Each Digit, proposed by Bo Rydobon 🇹🇭:
=TOCOL(MAP(A2:A10,LAMBDA(a,LET(b,a/MID(a,SEQUENCE(LEN(a)),1),a/AND(INT(b)=b)))),3)
Excel solution 3 for Divisible by Each Digit, proposed by Bo Rydobon 🇹🇭:
=TOCOL(MAP(A2:A10,LAMBDA(a,a/(0=MOD(a,LCM(MID(a,SEQUENCE(LEN(a)),1)))))),3)
Excel solution 4 for Divisible by Each Digit, proposed by John V.:
=TOCOL(MAP(A2:A10,LAMBDA(x,LET(d,x/MID(x,SEQUENCE(LEN(x)),1),x/AND(d=INT(d))))),2)
Based on nice LCM idea from Bo Rydobon:
✅=TOCOL(MAP(A2:A10,LAMBDA(x,x/(LCM(x,MID(x,SEQUENCE(LEN(x)),1))=x))),2)
Excel solution 5 for Divisible by Each Digit, proposed by محمد حلمي:
=TOCOL(MAP(A2:A10,LAMBDA(a,
LET(i,MID(a,SEQUENCE(LEN(a)),1),a/AND(a/i=INT(a/i))))),2)
Excel solution 6 for Divisible by Each Digit, proposed by Kris Jaganah:
=TOCOL(MAP(A2:A10,LAMBDA(x,LET(a,MID(x,SEQUENCE(LEN(x)),1),MIN(IFS(INT(x/a)=x/a,x))))),3)
Excel solution 7 for Divisible by Each Digit, proposed by Julian Poeltl:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(N,PRODUCT(--LET(C,N/UNIQUE(MID(N,SEQUENCE(LEN(N)),1),1),INT(C)=C)))))
Excel solution 8 for Divisible by Each Digit, proposed by Aditya Kumar Darak 🇮🇳:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(a,
LET(
splt, MID(a, SEQUENCE(LEN(a)), 1),
lcm, LCM(splt),
r, MOD(a, lcm) = 0,
r
)
)
)
)
Excel solution 9 for Divisible by Each Digit, proposed by Timothée BLIOT:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(z,LET(A,UNIQUE(MID(z,SEQUENCE(LEN(z)),1)*1),SUM(--((z/A)=INT(z/A)))=ROWS(A)))))
Excel solution 10 for Divisible by Each Digit, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(a,
LET(
_n, MAKEARRAY(1, LEN(a), LAMBDA(r, c, MID(a, c, 1))),
AND(a / _n - TRUNC(a / _n) = 0)
)
)
)
)
Excel solution 11 for Divisible by Each Digit, proposed by Sunny Baggu:
=FILTER(
A2:A10,
MAP(
A2:A10,
LAMBDA(num, AND(LET(a, num / MID(num, SEQUENCE(LEN(num)), 1), a - INT(a) = 0)))
)
)
Excel solution 12 for Divisible by Each Digit, proposed by LEONARD OCHEA 🇷🇴:
=LET(d,A2:A10,FILTER(d,MAP(d,LAMBDA(a,LCM(a,MID(a,SEQUENCE(LEN(a)),1))=a))))
Excel solution 13 for Divisible by Each Digit, proposed by Julien Lacaze:
=FILTER(A2:A9,MAP(A2:A9,LAMBDA(a,AND(MOD(a,--UNIQUE(MID(a,SEQUENCE(LEN(a)),1)))=0))))
Last number reached out Excel Limit for MOD calculations.
Hence you have to check it "manually" :
->I added a "split" Lambda to easy readability
=LET(data,A2:A10,
split,LAMBDA(text,--UNIQUE(MID(text,SEQUENCE(LEN(text)),1))),
FILTER(data,MAP(data,LAMBDA(a,AND(INT(a/split(a))=a/split(a))))))
Excel solution 14 for Divisible by Each Digit, proposed by Daniel Garzia:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(l,LET(i,MAP(MID(l,SEQUENCE(LEN(l)),1),LAMBDA(x,l/x)),AND(i-INT(i)=0)))))
Excel solution 15 for Divisible by Each Digit, proposed by Quadri Olayinka Atharu:
=TOCOL(MAP(A2:A10,LAMBDA(x,LET(l,LEN(x),i,x/(--MID(x,SEQUENCE(l),1)),IF(SUM(N(i=INT(i)))=l,x,p)))),2)
Excel solution 16 for Divisible by Each Digit, proposed by Quadri Olayinka Atharu:
=TOCOL(MAP(A2:A10,LAMBDA(x,IF(LCM(x,--MID(x,SEQUENCE(LEN(x)),1))=x,x,NA()))),2)
Excel solution 17 for Divisible by Each Digit, proposed by Quadri Olayinka Atharu:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(l,LEN(x),i,x/(--MID(x,SEQUENCE(l),1)),SUM(N(i=INT(i)))=l))))
Excel solution 18 for Divisible by Each Digit, proposed by Diarmuid Early:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,LET(s,--MID(a,SEQUENCE(LEN(a)),1),AND(INT(a/s)=a/s)))))
* MID / SEQUENCE splits the number into digits
* a/s = INT(a/s) is an alternative way of checking that s divides a (for each digit s in the number a)
Excel solution 19 for Divisible by Each Digit, proposed by Henriette Hamer:
=FILTER(A2:A10;MAP(A2:A10;LAMBDA(input;AND((input/MID(input;SEQUENCE(LEN(input));1))=ROUND(input/MID(input;SEQUENCE(LEN(input));1);0)))))
Excel solution 20 for Divisible by Each Digit, proposed by Amr Tawfik CMA®,FMVA,Lean Coach:
=FILTER(A2:A10,IFERROR(--MAP(A2:A10,LAMBDA(x,IF(SUM(MOD(x,MID(x,SEQUENCE(LEN(x),,1,1),1)))<>0,FALSE,TRUE))),"")=1)
Excel solution 21 for Divisible by Each Digit, proposed by Amr Tawfik CMA®,FMVA,Lean Coach:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,IF(SUM(x/MID(x,SEQUENCE(LEN(x),,1,1),1)<>SUM(INT(x/MID(x,SEQUENCE(LEN(x),,1,1),1)))
,FALSE,TRUE)))=TRUE)
Excel solution 22 for Divisible by Each Digit, proposed by Surendra Reddy:
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,MOD(x,LCM(MID(x,SEQUENCE(LEN(x)),1)))=0)))
Solving the challenge of Divisible by Each Digit with Python in Excel
Python in Excel solution 1 for Divisible by Each Digit, proposed by Alejandro Campos:
def is_divisible_by_digits(n):
digits = [int(d) for d in str(n) if d != '0']
for digit in digits:
if n % digit != 0:
return False
return True
numbers = xl("A2:A10")[0]
divisible_numbers = [n for n in numbers if is_divisible_by_digits(n)]
divisible_numbers
&&&
