Find those numbers which when multiplied by 2 to number of digits in that number generates the same digits in any other order. For example: 17802 17802 needs to be multiplied by 2 to 5 where 5 is the number of digits in 17802. 17802*2 = 35604 17802*3 = 53406 17802*4 = 71208 17802*5 = 89010 In this 17802*4 leads to 71208 which are same digits in a different order.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 251
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Digit Rearranged Multiplication Match with Power Query
Power Query solution 1 for Digit Rearranged Multiplication Match, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.SelectRows(
Source,
each
let
l = List.Transform(
{1 .. Text.Length(Text.From([Numbers]))},
(m) => Text.Combine(List.Sort(Text.ToList(Text.From(m * [Numbers]))))
)
in
List.PositionOf(List.Skip(l), l{0}) >= 0
)
in
AnsPower Query solution 2 for Digit Rearranged Multiplication Match, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Numbers],
l = (n) => List.Sort(Text.ToList(Text.From(n))),
S = List.Select(
Source,
each List.Accumulate(
{2 .. Number.RoundUp(Number.Log10(_))},
false,
(s, c) => s or l(c * _) = l(_)
)
)
in
SPower Query solution 3 for Digit Rearranged Multiplication Match, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.SelectRows(
Source,
each [
T = Text.From([Numbers]),
G = List.Transform(
{2 .. Text.Length(T)},
(f) =>
[
t = Text.From([Numbers] * f),
d = List.Difference(Text.ToList(t), Text.ToList(T)),
r = d{0}? = null
][r]
),
R = List.AnyTrue(G)
][R]
)
in
ReturnPower Query solution 4 for Digit Rearranged Multiplication Match, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.SelectRows(
Source,
each [
Text = Text.From([Numbers]),
Len = Text.Length(Text),
ToList = List.Sort(Text.ToList(Text)),
Seq = {2 .. List.Min({9, Len})},
Check = List.Transform(Seq, (f) => List.Sort(Text.ToList(Text.From([Numbers] * f))) = ToList),
TF = List.AnyTrue(Check)
][TF]
)
in
ReturnPower Query solution 5 for Digit Rearranged Multiplication Match, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.SelectRows(
Source,
each
let
a = Text.From([Numbers]),
b = {2 .. Text.Length(a) + 1}
in
List.AnyTrue(
List.Transform(
b,
(x) => List.Sort(Text.ToList(Text.From(x * [Numbers]))) = List.Sort(Text.ToList(a))
)
)
)
in
SolPower Query solution 6 for Digit Rearranged Multiplication Match, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.SelectRows(
Fonte,
each [
a = Text.ToList(Text.From([Numbers])),
b = List.Count(a),
c = List.Transform(
{2 .. b},
(x) => List.ContainsAll(a, Text.ToList(Text.From([Numbers] * x)))
),
d = List.Select(c, each _ = true){0}? ?? null
][d]
)
in
resPower Query solution 7 for Digit Rearranged Multiplication Match, proposed by Kalyan Kumar Reddy Kethireddy:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"LYzLDcAgDEN34dxDcEJsZkHsv0ZL6MHS80deqyE42n5Wm0kv6JShKFPIIhhz3poCfwzr0ODdytOPyrm+PDoZ91Jyhhlynvl+AQ==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Numbers = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Numbers", Int64.Type}}),
List = Table.AddColumn(
#"Changed Type",
"Custom",
each [a = [Numbers], b = Text.ToList(Text.From(a)), c = {2 .. List.Count(b)}][c]
),
#"Expanded Custom" = Table.ExpandListColumn(List, "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom", {{"Custom", Int64.Type}}),
Multiply = Table.AddColumn(#"Changed Type1", "Custom.1", each [Numbers] * [Custom]),
IsTrue = Table.AddColumn(
Multiply,
"Custom.2",
each [
a = Text.ToList(Text.From([Numbers])),
b = Text.ToList(Text.From([Custom.1])),
c = List.ContainsAll(a, b)
][c]
),
#"Filtered Rows" = Table.SelectRows(IsTrue, each ([Custom.2] = true)),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Numbers"})[Numbers]
in
#"Removed Duplicates"Solving the challenge of Digit Rearranged Multiplication Match with Excel
Excel solution 1 for Digit Rearranged Multiplication Match, proposed by Bo Rydobon 🇹🇭:
=TOCOL(
MAP(
A2:A14,
LAMBDA(
a,
LET(
s,
SEQUENCE(
LEN(
a
)
),
IF(
MODE(
MAP(
a*s,
LAMBDA(
b,
--CONCAT(
SORT(
MID(
b,
s,
1
)
)
)
)
)
),
a
)
)
)
),
3
)Excel solution 2 for Digit Rearranged Multiplication Match, proposed by Rick Rothstein:
=LET(a,A2:A11,f,LAMBDA(x,ISNUMBER(MODE(BYCOL(MID({1,2,3,4,5}*x,SEQUENCE(99),1),LAMBDA(r,0+CONCAT(SORT(r))))))),FILTER(a,MAP(a,LAMBDA(n,REPT(f(n),1)))))Excel solution 3 for Digit Rearranged Multiplication Match, proposed by John V.:
=TOCOL(
MAP(
A2:A11,
LAMBDA(
n,
IF(
MODE(
MAP(
n*ROW(
1:9
),
LAMBDA(
x,
--CONCAT(
SORT(
MID(
x,
ROW(
1:13
),
1
)
)
)
)
)
),
n
)
)
),
2
)Excel solution 4 for Digit Rearranged Multiplication Match, proposed by محمد حلمي:
=TOCOL(MAP(A2:A11,LAMBDA(a,LET(i,ROW(1:10)-1,
a/OR(MAP(SEQUENCE(LEN(a)-1,,2)*a,LAMBDA(x,
AND(ISERR(FIND(i,a))=ISERR(FIND(i,x))))))))),2)Excel solution 5 for Digit Rearranged Multiplication Match, proposed by Kris Jaganah:
=TOCOL(
MAP(
A2:A11,
LAMBDA(
x,
LET(
a,
SEQUENCE(
LEN(
x
)
),
b,
MID(
x,
a,
1
),
c,
MAP(
DROP(
a,
1
)*x,
LAMBDA(
y,
--CONCAT(
TEXTSPLIT(
y,
b
)
)
)
),
x/MAX(
--ISERR(
c
)
)
)
)
),
3
)Excel solution 6 for Digit Rearranged Multiplication Match, proposed by Julian Poeltl:
=FILTER(
A2:A11,
MAP(
A2:A11,
LAMBDA(
N,
LET(
L,
LAMBDA(
A,
CONCAT(
UNIQUE(
SORT(
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)
)
)
)
),
M,
N*SEQUENCE(
LEN(
N
)-1,
,
2
),
ISNUMBER(
XMATCH(
L(
N
),
MAP(
M,
LAMBDA(
A,
L(
A
)
)
)
)
)
)
)
)
)Excel solution 7 for Digit Rearranged Multiplication Match, proposed by Aditya Kumar Darak 🇮🇳:
=FILTER(
A2:A11,
MAP(
A2:A11,
LAMBDA(
a,
ISNUMBER(
MODE(
MAP(
a * SEQUENCE(
MIN(
9,
LEN(
a
)
)
),
LAMBDA(
x,
--CONCAT(
SORT(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
)
)
)
)
)
)
)
)Excel solution 8 for Digit Rearranged Multiplication Match, proposed by Timothée BLIOT:
=FILTER(A2:A11,
MAP(A2:A11,
LAMBDA(z,
LET(A,
LEN(
z
),
B,
SEQUENCE(
A,
,
2
)*z,
SUM(MAP(B,
LAMBDA(x,
--(CONCAT(
SORT(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
) =CONCAT(
SORT(
MID(
z,
SEQUENCE(
A
),
1
)
)
)))))>0))))Excel solution 9 for Digit Rearranged Multiplication Match, proposed by Hussein SATOUR:
=FILTER(
A2:A11,
NOT(
MAP(
A2:A11,
LAMBDA(
y,
LET(
a,
LEN(
y
),
b,
MID(
y,
SEQUENCE(
a
),
1
),
c,
y * SEQUENCE(
a-1,
,
2
),
IFERROR(
PRODUCT(
MAP(
FILTER(
c,
LEN(
c
) = a
),
LAMBDA(
x,
ISERR(
SUM(
FIND(
b,
x
)
)
)*1
)
)
),
1
)
)
)
)
)
)Excel solution 10 for Digit Rearranged Multiplication Match, proposed by Sunny Baggu:
=FILTER(
A2:A11,
MAP(
A2:A11,
LAMBDA(n,
LET(
_e1, LAMBDA(x, CONCAT(SORT(MID(x, SEQUENCE(LEN(x)), 1)))),
_val, MAP(n * SEQUENCE(LEN(n) - 1, , 2), LAMBDA(a, _e1(a))),
OR(_val = _e1(n))
)
)
)
)Excel solution 11 for Digit Rearranged Multiplication Match, proposed by LEONARD OCHEA 🇷🇴:
=LET(
n,
A2:A11,
FILTER(
n,
MAP(
n,
LAMBDA(
a,
LET(
S,
LAMBDA(
x,
CONCAT(
SORT(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
)
& ),
OR(
S(
a
)=MAP(
a*SEQUENCE(
4,
,
2
),
LAMBDA(
b,
S(
b
)
)
)
)
)
)
)
)
)Excel solution 12 for Digit Rearranged Multiplication Match, proposed by JvdV –:
=LET(r,A2:A11,s,ROW(1:15),FILTER(r,MAP(r,LAMBDA(x,OR(MAP(TAKE(s+1,LEN(x)),LAMBDA(y,AND(SORT(MID(x,s,1))=SORT(MID(x*y,s,1))))))))))Excel solution 13 for Digit Rearranged Multiplication Match, proposed by Pieter de Bruijn:
=LET(
x,
A2:A11,
FILTER(
x,
MAP(
x,
LAMBDA(
a,
LET(
b,
SEQUENCE(
LEN(
a
)
),
c,
a*DROP(
b,
1
),
OR(
CONCAT(
SORT(
MID(
a,
b,
1
)
)
)=MAP(
c,
LAMBDA(
d,
CONCAT(
SORT(
MID(
d,
b,
1
)
)
)
)
)
)
)
)
)
)
)Excel solution 14 for Digit Rearranged Multiplication Match, proposed by Daniel Garzia:
=FILTER(
A2:A11,
MAP(
A2:A11,
LAMBDA(
n,
LET(
f,
LAMBDA(
l,
CONCAT(
SORT(
MID(
l,
SEQUENCE(
LEN(
l
)
),
1
)
)
)
),
OR(
MAP(
n*SEQUENCE(
LEN(
n
)-1,
,
2
),
LAMBDA(
x,
f(
n
)=f(
x
)
)
)
)
)
)
)
)Excel solution 15 for Digit Rearranged Multiplication Match, proposed by Md Ismail Hosen:
=LAMBDA(
Numbers,
LET(
_fx_ForOne,
LAMBDA(
Number,
LET(
_Length,
LEN(
Number
),
_ToChars,
LAMBDA(
InputText,
IF(
InputText = "",
"",
MID(
InputText,
SEQUENCE(
LEN(
InputText
)
),
1
)
)
),
_Seq,
SEQUENCE(
_Length - 2 + 1,
1,
2
) * Number,
_Result,
OR(
IFNA(
MAP(
_Seq,
LAMBDA(
Curr,
AND(
SORT(
_ToChars(
Number
)
) = SORT(
_ToChars(
Curr
)
)
)
)
),
FALSE
)
),
_Result
)
),
_Result,
FILTER(
Numbers,
MAP(
Numbers,
_fx_ForOne
)
),
_Result
)
)(A2:A11)Excel solution 16 for Digit Rearranged Multiplication Match, proposed by Rayan S.:
=FILTER(A2:A11,
MAP(A2:A11,
LAMBDA(arr,
LET(s,
TEXTJOIN(
"",
,
SORT(
MID(
arr,
SEQUENCE(
LEN(
arr
)
),
1
)
)
),
x,
MAP(
arr * SEQUENCE(
LEN(
arr
),
,
2
),
LAMBDA(
x,
TEXTJOIN(
"",
,
SORT(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
)
)
),
IF(TEXTJOIN("",
,
IF(--(x = s) = 1,
arr,
"")) = "",
0,
arr))))<>0)&&