Parasitic number – is a positive natural number which, when multiplied by single digit n, results in movement of the last digit to its front. Ex. 128205 multiplied by 4 = 512820 (5 has come in front of 12820) For the purpose of this challenge we will not consider n = 1. Hence, we will consider n = 2 through 9 only. Find all parasitic numbers up to 1000000 and its corresponding multiplier.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 452
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Parasitic Number Identification with Power Query
Power Query solution 1 for Parasitic Number Identification, proposed by Aditya Kumar Darak 🇮🇳:
let
Generate = List.TransformMany(
{2 .. 9},
(x) => {2 .. 9},
(x, y) =>
[
C1 = x / (10 * y - 1) * 1000000,
C2 = Number.IntegerDivide(C1, 1),
C3 = C2 * y,
T = Text.From(C2),
C4 = Number.From(Text.End(T, 1) & Text.Start(T, Text.Length(T) - 1)),
R = if C3 <> C4 then null else [Number = C2, Multiplier = y]
][R]
),
Table = Table.FromRecords(List.RemoveNulls(Generate)),
Return = Table.Sort(Table, "Number")
in
ReturnPower Query solution 2 for Parasitic Number Identification, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = Text.ToList,
b = Text.From,
c = Number.From,
d = List.Last,
Sol = Table.AddColumn(
Origen,
"Multiplier",
(o) =>
d(
List.Generate(
() => [x = 2, q = false],
each [q] <> true,
each [
x = [x] + 1,
r = c(d(a(b(o[Number])))),
s = [x] * o[Number],
t = c(a(b(s)){0}),
q = r = t
],
each [x]
)
)
)
in
SolPower Query solution 3 for Parasitic Number Identification, proposed by Ramiro Ayala Chávez:
let
A = {1 .. 1000000},
B = Table.FromColumns({A}, {"Number"}),
Fx = (x) =>
let
a = x,
b = List.Generate(() => [i = 2], each [i] <= 9, each [i = [i] + 1], each {a * [i]} & {[i]}),
c = Text.From(a),
d = Number.From(Text.RemoveRange(Text.Insert(c, 0, Text.End(c, 1)), Text.Length(c), 1)),
e = List.RemoveNulls(List.Transform(b, each if _{0} = d then _ else null)){0}
in
e,
C = Table.AddColumn(B, "Multiplier", each try Fx([Number]){1} otherwise null),
Sol = Table.SelectRows(C, each [Multiplier] <> null)
in
SolSolving the challenge of Parasitic Number Identification with Excel
Excel solution 1 for Parasitic Number Identification, proposed by Bo Rydobon 🇹🇭:
=LET(b,
SEQUENCE(
10^6
),
y,
MAP(b,
LAMBDA(c,
(RIGHT(
c
)&LEFT(
c,
LEN(
c
)-1
))/c)),
FILTER(HSTACK(
b,
y
),
(y>1)*(INT(
y
)=y)))Excel solution 2 for Parasitic Number Identification, proposed by Bo Rydobon 🇹🇭:
=LET(s,
SEQUENCE(
,
8,
2
),
p,
MAP(INT(TOCOL(
s
)/(s*10-1)*10^6),
LAMBDA(c,
c*10/(MOD((RIGHT(
c
)&LEFT(
c,
LEN(
c
)-1
)),
c)=0)))+s,
--MID(
TOCOL(
p,
3
),
{1,
7},
6
))Excel solution 3 for Parasitic Number Identification, proposed by John V.:
=LET(i,
3663*ROW(
1:273
),
c,
LEN(
i
),
n,
COLUMN(
B:I
),
r,
BYROW(n*(i*n=--MID(
i&i,
c,
c
)),
MAX),
FILTER(
HSTACK(
i,
r
),
r
))Excel solution 4 for Parasitic Number Identification, proposed by محمد حلمي:
=LET(s,
SEQUENCE(
2^17
)+102560,
j,
MAP(s,
LAMBDA(a,
IFNA(XMATCH(TRUE,
--(RIGHT(
a
)&LEFT(
a,
5
))=
a*SEQUENCE(
8,
,
2
))+1,
))),
FILTER(
HSTACK(
s,
j
),
j
))Excel solution 5 for Parasitic Number Identification, proposed by Kris Jaganah:
=LET(a,
SEQUENCE(
10^6
),
b,
{2,
3,
4,
5,
6,
7,
8,
9},
c,
(RIGHT(
a
)&LEFT(
a,
LEN(
a
)-1
)),
d,
BYROW(IF(((a*b)-c)=0,
b),
SUM),
FILTER(
HSTACK(
a,
d
),
d
))Excel solution 6 for Parasitic Number Identification, proposed by Julian Poeltl:
=LET(S,
SEQUENCE(
10^6
),
R,
IF((RIGHT(
S,
1
)&LEFT(
S,
LEN(
S
)-1
))*1=S*SEQUENCE(
,
8,
2
),
1),
B,
BYROW(
R,
LAMBDA(
A,
XMATCH(
1,
A
)
)
),
FILTER(
HSTACK(
S,
B+1
),
NOT(
ISNA(
B
)
)
))Excel solution 7 for Parasitic Number Identification, proposed by Timothée BLIOT:
=LET(A,
SEQUENCE(
10^6
),
B,
SEQUENCE(
,
8
)+1,
C,
RIGHT(A*(B/B))=LEFT(
A*B
),
HSTACK(
TOCOL(
IF(
C,
A*B/B,
1/0
),
3
),
TOCOL(
IF(
C,
A/A*B,
1/0
),
3
)
))Excel solution 8 for Parasitic Number Identification, proposed by Nikola Z Grujicic – Nikola Ž Grujičić:
=LET(
a,
SEQUENCE(
1000000
),
x,
MAP(
a,
LAMBDA(
d,
LET(
e,
""&d*{2;3;4;5;6;7;8;9},
f,
1*RIGHT(
e ,
LEN(
e
)-1
)&LEFT(
e,
1
),
g,
--f,
MATCH(
d,
g,
0
)+1
)
)
),
j,
FILTER(
a,
ISNUMBER(
x
)
),
k,
FILTER(
x,
ISNUMBER(
x
)
),
HSTACK(
j,
k
)
)Excel solution 9 for Parasitic Number Identification, proposed by Hussein SATOUR:
=LET(a,
SEQUENCE(
1000000
),
b,
FILTER(a,
MAP(a,
LAMBDA(x,
SUM(((RIGHT(
x
)&ROUNDDOWN(
x/10,
0
))/SEQUENCE(
8,
,
2
)= x)*1)))),
HSTACK(b,
(RIGHT(
b
)&LEFT(
b,
LEN(
b
)-1
))/b))Excel solution 10 for Parasitic Number Identification, proposed by Sunny Baggu:
=TEXTSPLIT(
TEXTJOIN(
";",
,
REDUCE(
"",
SEQUENCE(
8,
,
2
),
LAMBDA(x,
y,
VSTACK(
x,
LET(
n,
y,
s,
SEQUENCE(
11,
,
0
),
MAP(
n + SEQUENCE(
10 - n
) - 1,
LAMBDA(k,
LET(
_n,
IF(
s >= 0,
n
),
_v1,
SCAN(0,
s,
LAMBDA(a,
v,
n * (RIGHT(
a,
v
) & k))),
_v2,
(k & RIGHT(
_v1,
s
)) + 0,
IFERROR(
FILTER(
_v1,
_v1 = _v2
) / n & "," & n,
""
)
)
)
)
)
)
)
)
),
",",
";"
)Excel solution 11 for Parasitic Number Identification, proposed by LEONARD OCHEA 🇷🇴:
=LET(
v,
SEQUENCE(
275,
,
0,
3663
),
h,
SEQUENCE(
,
8,
2
),
TEXTSPLIT(
TEXTJOIN(
"*",
,
REPT(
v&"|"&h,
v*h&""=RIGHT(
v
)&INT(
v/10
)
)
),
"|",
"*"
)*1
)
It seems that the parasitic numbers have a greatest common divisor : => GCD(
A2:A8
)Excel solution 12 for Parasitic Number Identification, proposed by LEONARD OCHEA 🇷🇴:
=LET(
v,
SEQUENCE(
10^6
),
h,
SEQUENCE(
,
8,
2
),
TEXTSPLIT(
TEXTJOIN(
"*",
,
TOCOL(
IF(
v*h&""=RIGHT(
v
)&INT(
v/10
),
v&"/"&h,
z
),
2
)
),
"/",
"*"
)
)
For BETA version
=LET(
s,
SEQUENCE,
v,
s(
10^6
),
h,
s(
,
8,
2
),
TEXTSPLIT(
TEXTJOIN(
"*",
,
TOCOL(
IF(
v*h&""=RIGHT(
v
)&INT(
v/10
),
v&"/"&h,
z
),
2
)
),
"/",
"*"
)
)Excel solution 13 for Parasitic Number Identification, proposed by Abdallah Ally:
=LET(a,
SEQUENCE(
1000000
),
b,
MAP(a,
LAMBDA(x,
REDUCE(0,
SEQUENCE(
8
)+1,
LAMBDA(u,
v,
IF(--(RIGHT(
x
)&LEFT(
x,
LEN(
x
)-1
))=x*v,
u+v,
u))))),
FILTER(
HSTACK(
a,
b
),
b
))Excel solution 14 for Parasitic Number Identification, proposed by 🇵🇪 Ned Navarrete C.:
=REDUCE(A1:B1,
SEQUENCE(
10^6
),
LAMBDA(c,
v,
LET(n,
--(RIGHT(
v
)&LEFT(
v,
LEN(
v
)-1
)),
IF(
AND(
GCD(
v,
n
)=v,
n<>v
),
VSTACK(
c,
HSTACK(
v,
n/v
)
),
c
))))Excel solution 15 for Parasitic Number Identification, proposed by Md. Zohurul Islam:
=LET(
n,
SEQUENCE(
500000,
,
100001
),
sq,
SEQUENCE(
,
8
)+1,
a,
ABS(
RIGHT(
n,
1
)&LEFT(
n,
5
)
),
b,
ABS(
a=sq*n
),
d,
BYROW(
b,
LAMBDA(
x,
IFERROR(
FILTER(
sq,
x
),
0
)
)
),
e,
FILTER(
HSTACK(
n,
d
),
d>0
),
e
)Excel solution 16 for Parasitic Number Identification, proposed by Pieter de B.:
=LET(s,
SEQUENCE(
10^6
),
r,
ROUND(
RIGHT(
s
)/LEFT(
s,
3
)%,
),
WRAPROWS(TOCOL(IFS((FIND(
LEFT(
s,
LEN(
s
)-1
),
s*r
)=2)*(LEFT(
s*r
)=RIGHT(
s
)),
HSTACK(
s,
r
)),
2),
2))Excel solution 17 for Parasitic Number Identification, proposed by Andy Heybruch:
=LET(
_num,
SEQUENCE(
10^6
),
_mult,
SEQUENCE(
,
8,
2
),
_r,
--(RIGHT(
_num
)&LEFT(
_num,
LEN(
_num
)-1
)),
_check,
--((_num*_mult)=_r),
_array,
HSTACK(
_num,
BYROW(
_check,
LAMBDA(
a,
IFERROR(
XMATCH(
1,
a,
0
)+1,
0
)
)
)
),
FILTER(
_array,
TAKE(
_array,
,
-1
)>0
))Excel solution 18 for Parasitic Number Identification, proposed by Ernesto Vega Castillo:
=LET(s,
SEQUENCE(
1000000
),
m,
MOD(
s,
10
),
i,
INT(
s/10
),
c,
m&i,
d,
--c/s,
f,
FILTER(HSTACK(
s,
d
),
(d=4)+(d=5)),
f)Excel solution 19 for Parasitic Number Identification, proposed by Gabriel Raigosa:
=MID(LET(c,SEQUENCE(,8,2),f,SEQUENCE(10^6,,10^5),TOCOL(IF(--(RIGHT(f,1)&LEFT(f,5))=f*c,f&c,x),2)),{1,7},{6,1})
🔹ES:
=EXTRAE(LET(c,SECUENCIA(,8,2),f,SECUENCIA(10^6,,10^5),ENCOL(SI(--(DERECHA(f,1)&IZQUIERDA(f,5))=f*c,f&c,x),2)),{1,7},{6,1})Excel solution 20 for Parasitic Number Identification, proposed by Tyler Cameron:
=LET(a,
SEQUENCE(
1000000
),
b,
SEQUENCE(
,
8,
2
),
DROP(TEXTSPLIT(CONCAT(IF(a*b=--(RIGHT(
a,
1
)&LEFT(
a,
LEN(
a
)-1
)),
a&","&b&"-",
"")),
",",
"-"),
-1))Excel solution 21 for Parasitic Number Identification, proposed by Marek Tomanek:
=LET(
nums&;
SEQUENCE(
999900;
;
100
);
r_nums;
RIGHT(
nums
);
s_nums;
SUBSTITUTE(
nums;
r_nums;
""
);
j_nums;
--(r_nums&s_nums);
f_nums;
FILTER(nums;
(IF(
MOD(
j_nums;
nums
)=0;
1;
0
))=1);
d_nums;
XLOOKUP(
f_nums;
nums;
j_nums
)/f_nums;
CHOOSE(
{12};
f_nums;
d_nums
))Solving the challenge of Parasitic Number Identification with Python
Python solution 1 for Parasitic Number Identification, proposed by Konrad Gryczan, PhD:
import pandas as pd
test = pd.read_excel("452 Parasitic Numbers.xlsx", usecols="A:B")
a = pd.DataFrame({"Number": list(map(str, range(1, 1000001)))})
a["cycled"] = a["Number"].apply(lambda x: x[-1] + x[0:-1])
a = a[(a["Number"].str.len() == a["cycled"].str.len()) &
(a["cycled"].astype(int) % a["Number"].astype(int) == 0) &
(a["cycled"].astype(int) != a["Number"].astype(int))]
a["Multiplier"] = a["cycled"].astype(int) / a["Number"].astype(int)
a = a.drop("cycled", axis=1).reset_index(drop=True).astype("int64")
print(a.equals(test)) # True
Solving the challenge of Parasitic Number Identification with Python in Excel
Python in Excel solution 1 for Parasitic Number Identification, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_452 - Parasitic Numbers.xlsx'
df = pd.read_excel(file_path)
# Perform data transformation and cleansing
values = []
for x in range(1, 10 ** 6 + 1):
for y in range(2, 10):
if int(str(x)[-1] + str(x)[: -1]) == x * y and [x, y] not in values:
values.append([x,y])
df[['MyNumber', 'MyMultiplier']] = values
df
Solving the challenge of Parasitic Number Identification with R
R solution 1 for Parasitic Number Identification, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
test = read_excel("Excel/452 Parasitic Numbers.xlsx", range = "A1:B8")
a = tibble(Number = as.character(1:1000000)) %>%
mutate(cycled = str_c(str_sub(Number, -1), str_sub(Number, 1, -2)) %>%
as.numeric() %>%
as.character()) %>%
filter(nchar(Number) == nchar(cycled),
as.integer(cycled) %% as.integer(Number) == 0,
as.integer(cycled) != as.integer(Number)) %>%
mutate(across(everything(), as.numeric)) %>%
mutate(Multiplier = cycled / Number) %>%
select(-cycled)
identical(a, test)
# [1] TRUE
Solving the challenge of Parasitic Number Identification with Excel VBA
Excel VBA solution 1 for Parasitic Number Identification, proposed by Rushikesh K.:
Sub FindParasiticNumbers()
Dim i As Long
Dim j As Integer
Dim strNum As String
Dim multiplier As Integer
For i = 1 To 1000000
strNum = CStr(i)
' Check each digit from 2 to 9
For j = 2 To 9
If IsParasitic(strNum, j) Then
multiplier = CInt(Mid(strNum, Len(strNum) - 1, 1))
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = i ' Write parasitic number to column A
Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = multiplier ' Write corresponding multiplier to column B
Exit For
End If
Next j
Next i
End Sub
Function IsParasitic(num As String, digit As Integer) As Boolean
Dim result As String
Dim i As Integer
result = CStr(Val(num) * digit)
For i = 1 To Len(result)
If InStr(num, Mid(result, i, 1)) = 0 Then
IsParasitic = False
Exit Function
End If
Next i
For i = 1 To Len(num)
If InStr(result, Mid(num, i, 1)) = 0 Then
IsParasitic = False
Exit Function
End If
Next i
IsParasitic = True
End Function
&&