Multiply first digit with second digit, then result of this should be multiplied with 3rd digit and so on. If you encounter 0 anywhere as a product of two numbers, replace that with 1. Once you exhaust all digits, then above logic needs to be re-run again for the result. Keep doing it till result is reduced to a single digit. Example = 8562 = 8*5 = 40 (it has a 0, replace it with 1). Hence, it would become 41. Next 41*6 = 246. Next = 246*2 = 492. This is the result of first round of multiplication. Now, run the logic for this result. for 492: 4*9=36 Next 36*2 = 72 for 72: 14 for 14: 1*4 = 4 Explanation for 78982 7*8 = 56 56*9 = 504 Now, this 0 need to be replaced with 1. Hence, this is 514. 514*8 = 4112 4112*2 = 8224 Now for 8224 8*2*2*4 = 128 For 128 1*2*8 = 16 For 16 1*6 =6
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 249
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Multiply Digits to Single Digit with Power Query
Power Query solution 1 for Multiply Digits to Single Digit, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each List.Accumulate(
{1 .. 9},
[Number],
(n, b) =>
List.Accumulate(
Text.ToList(Text.From(n)),
1,
(s, l) => Number.From(Text.Replace(Text.From(s * Number.From(l)), "0", "1"))
)
)
)
in
AnsPower Query solution 2 for Multiply Digits to Single Digit, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
z = (n) => Number.From(Text.Replace(Text.From(n), "0", "1")),
S = Table.TransformRows(
Source,
each List.Last(
List.Generate(
() => [t = Text.From(z([Number])), r = [Number]],
each Text.Length([t]) > 1,
each [
t = Text.From(z([r])),
r = List.Accumulate(List.Transform(Text.ToList(t), Number.From), 1, (s, c) => z(s) * c)
],
each [r]
)
)
)
in
SPower Query solution 3 for Multiply Digits to Single Digit, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
g = (f)=> let
a = List.Accumulate(List.Transform(Text.ToList(Text.From(f)), Number.From), 1 ,
(s,c)=> Number.From(Text.Replace(Text.From(s*c), "0", "1")))
in if Text.Length(Text.From(a)) = 1 then a else @g(a),
Sol = Table.TransformRows(Source, each g([Number]))
in
Sol
Aquí utilizé la técnica que explicó Zoran en el reto de co-prime numbers.
Power Query solution 4 for Multiply Digits to Single Digit, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.TransformRows(
Source,
each List.Last(
List.Generate(
() => [x = [Number], y = 2],
each [y] > 1,
each [
x = List.Accumulate(
List.Transform(Text.ToList(Text.From([x])), Number.From),
1,
(s, c) => Number.From(Text.Replace(Text.From(s * c), "0", "1"))
),
y = Text.Length(Text.From([x]))
],
each [x]
)
)
)
in
SolPower Query solution 5 for Multiply Digits to Single Digit, proposed by Sergei Baklan:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
tbl = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
fnProduct = (a as number, v as text) =>
Number.From(Text.Replace(Text.From(a * Number.From(v)), "0", "1")),
fnMultiply = (n as number) =>
if n < 10 then n else @fnMultiply(List.Accumulate(Text.ToList(Text.From(n)), 1, fnProduct))
in
Table.FromColumns({List.Transform(tbl[Number], fnMultiply)}, type table [Answer = Int64.Type])Solving the challenge of Multiply Digits to Single Digit with Excel
Excel solution 1 for Multiply Digits to Single Digit, proposed by Bo Rydobon 🇹🇭:
=REDUCE(
A2:A9,
SEQUENCE(
9
),
LAMBDA(
a,
x,
REDUCE(
1,
SEQUENCE(
15
),
LAMBDA(
b,
v,
--SUBSTITUTE(
b*RIGHT(
LEFT(
a&1,
v
)
),
0,
1
)
)
)
)
)Excel solution 2 for Multiply Digits to Single Digit, proposed by John V.:
=MAP(
A2:A9,
LAMBDA(
x,
REDUCE(
x,
ROW(
1:9
),
LAMBDA(
b,
w,
REDUCE(
1,
SEQUENCE(
LEN(
b
)
),
LAMBDA(
a,
v,
--SUBSTITUTE(
a*MID(
b,
v,
1
),
0,
1
)
)
)
)
)
)
)Excel solution 3 for Multiply Digits to Single Digit, proposed by محمد حلمي:
=MAP(
A2:A9,
LAMBDA(
x,
LET(
r,
LAMBDA(
r,
a,
IF(
a+0<10,
a,
r(
r,
REDUCE(
,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
LAMBDA(
a,
d,
SUBSTITUTE(
a*d,
0,
1
)
)
)
)
)
),
r(
r,
x
)+0
)
)
)Excel solution 4 for Multiply Digits to Single Digit, proposed by Kris Jaganah:
=MAP(
A2:A9,
LAMBDA(
z,
REDUCE(
z,
SEQUENCE(
9
),
LAMBDA(
v,
w,
REDUCE(
1,
SUBSTITUTE(
MID(
v,
SEQUENCE(
LEN(
v
)
),
1
),
0,
1
),
LAMBDA(
x,
y,
SUBSTITUTE(
x,
0,
1
)*y
)
)
)
)
)
)Excel solution 5 for Multiply Digits to Single Digit, proposed by Julian Poeltl:
=MAP(
A2:A9,
LAMBDA(
A,
TAKE(
SCAN(
A,
SEQUENCE(
10
),
LAMBDA(
A,
B,
TAKE(
SCAN(
1,
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
),
LAMBDA(
A,
B,
SUBSTITUTE(
A*B,
0,
1
)
)
),
-1
)
)
),
-1
)
)
)Excel solution 6 for Multiply Digits to Single Digit, proposed by Timothée BLIOT:
=MAP(
A2:A9,
LAMBDA(
z,
LET(
F,
LAMBDA(
me,
n,
IF(
LEN(
n
)=1,
n,
me(
me,
LET(
A,
SUBSTITUTE(
n,
"0",
"1"
),
REDUCE(
LEFT(
A
),
MID(
A,
SEQUENCE(
LEN(
A
)-1,
,
2
),
1
),
LAMBDA(
ac,
v,
SUBSTITUTE(
ac,
"0",
"1"
)*v
)
)
)
)
)
),
F(
F,
z
)
)
)
)Excel solution 7 for Multiply Digits to Single Digit, proposed by Hussein SATOUR:
= 1,
y,
ME(
ME,
a(
y
)
))),
F(
F,
w
))))Excel solution 8 for Multiply Digits to Single Digit, proposed by Sunny Baggu:
=MAP(
A2:A9,
LAMBDA(num,
REDUCE(
num,
SEQUENCE(10),
LAMBDA(x, y,
REDUCE(
1,
MID(SUBSTITUTE(x, 0, 1), SEQUENCE(LEN(x)), 1),
LAMBDA(a, v, SUBSTITUTE(a, 0, 1) * v)
)
)
)
)
)Excel solution 9 for Multiply Digits to Single Digit, proposed by Sunny Baggu:
=MAP(
A2:A9,
LAMBDA(
x,
LET(
_sum,
LAMBDA(
num,
rfn,
IF(
LEN(
num
) = 1,
num,
rfn(
REDUCE(
1,
MID(
SUBSTITUTE(
num,
0,
1
),
SEQUENCE(
LEN(
num
)
),
1
),
LAMBDA(
a,
v,
SUBSTITUTE(
a,
0,
1
) * v
)
),
rfn
)
)
),
_sum(
x,
_sum
)
)
)
)Excel solution 10 for Multiply Digits to Single Digit, proposed by LEONARD OCHEA 🇷🇴:
=MAP(
A2:A9,
LAMBDA(
x,
LET(
f,
LAMBDA(
f,
a,
LET(
s,
SEQUENCE(
LEN(
a
)
),
e,
MID(
a,
s,
1
),
r,
REDUCE(
1,
e,
LAMBDA(
a,
b,
SUBSTITUTE(
a*b,
0,
1
)
)
),
IF(
LEN(
r
)=1,
r,
f(
f,
r
)
)
)
),
--f(
f,
x
)
)
)
)Excel solution 11 for Multiply Digits to Single Digit, proposed by Abdallah Ally:
=MAP(
A2:A9,
LAMBDA(
v,
LET(
SINGLEDIGITREC,
LAMBDA(
num,
SINGLEDIGIT,
IF(
LEN(
num
)=1,
num,
SINGLEDIGIT(
REDUCE(
1,
MID(
num,
SEQUENCE(
LEN(
num
& )
),
1
),
LAMBDA(
x,
y,
SUBSTITUTE(
--x*--y,
0,
1
)
)
),
SINGLEDIGIT
)
)
),
--SINGLEDIGITREC(
v,
SINGLEDIGITREC
)
)
)
)Excel solution 12 for Multiply Digits to Single Digit, proposed by Charles Roldan:
=LET(M, LAMBDA(h, h(h)),
f, M(LAMBDA(h, LAMBDA(x,y,
IF(LEN(x), h(h)(REPLACE(x, 1, 1, ), SUBSTITUTE(LEFT(x) * y, 0, 1)), y)))),
g, M(LAMBDA(h, LAMBDA(x, IF(LEN(x) - 1, h(h)(f(x, 1)), --x)))),
MAP(A2:A9, g))Excel solution 13 for Multiply Digits to Single Digit, proposed by JvdV -:
=LET(
a,
ROW(
1:99
),
REDUCE(
A2:A9,
a,
LAMBDA(
b,
c,
REDUCE(
1,
a,
LAMBDA(
x,
y,
SUBSTITUTE(
x*LEFT(
MID(
b,
y,
1
)&1
),
0,
1
)
)
)
)
)
)Excel solution 14 for Multiply Digits to Single Digit, proposed by Julien Lacaze:
=LET(
data,
A2:A9,
fsplit,
LAMBDA(
value,
--MID(
value,
SEQUENCE(
LEN(
value
)
),
1
)
),
mulLambda,
LAMBDA(
value,
REDUCE(
1,
fsplit(
value
),
LAMBDA(
a,
v,
LET(
mul,
a*v,
--CONCAT(
IF(
fsplit(
mul
)=0,
1,
fsplit(
mul
)
)
)
)
)
)
),
recLamb,
LAMBDA(
value,
f,
LET(
mul,
mulLambda(
value
),
IF(
LEN(
mul
)=1,
mul,
f(
mul,
f
)
)
)
),
MAP(
data,
LAMBDA(
d,
recLamb(
d,
recLamb
)
)
)
)
fsplit,
Lambda to extract each char of a number,
mulLambda handle the multiplication from 2 numbers,
replacing the 0 encountered,
recLambda is a recursive Lambda to launch mulLambda until 1 digit is reached.
a MAP()Excel solution 15 for Multiply Digits to Single Digit, proposed by Peter Bartholomew:
=LET(n, LEN(s), k, SEQUENCE(n), MID(s, k, 1))
2. To perform a product step and convert 0 to 1
ProductNonZeroλ(arr)
= REDUCE(1, arr, LAMBDA(p, v, SUBSTITUTE(VALUE(p) * v, 0, 1)))
3. Perform a single step of the recursive calculation
Stepλ(val)
= ProductNonZeroλ(Explodeλ(val))
4. Implement a recursive calculation based upon Stepλ
Recurλ(val, Fnλ)
= LET(
step, Fnλ(val),
IF(LEN(step) > 1, Recurλ(step, Fnλ), step)
)
5. Worksheet formula
= Recurλ(@number, Stepλ)Excel solution 16 for Multiply Digits to Single Digit, proposed by Pieter de Bruijn:
=LET(
a,
A2:A9,
s,
SEQUENCE(
MAX(
LEN(
a
)
)
),
REDUCE(
a,
s,
LAMBDA(
b,
c,
REDUCE(
1,
s,
LAMBDA(
x,
y,
SUBSTITUTE(
x*LEFT(
MID(
b,
y,
1
)&1
),
0,
1
)
)
)
)
)
)Excel solution 17 for Multiply Digits to Single Digit, proposed by Md Ismail Hosen:
=LAMBDA(Numbers,
LET(
ToChars, LAMBDA(InputText, IF(InputText = "", "", MID(InputText, SEQUENCE(LEN(InputText)), 1))),
fx_rec, LAMBDA(Number, fx_placeholder,
LET(
Chars, ToChars(Number),
OneTimeScan, REDUCE(INDEX(Chars, 1, 1), DROP(Chars, 1), LAMBDA(Acc, Curr, SUBSTITUTE(Acc * Curr, 0, 1))),
Result, IF(LEN(Number) = 1, Number, fx_placeholder(OneTimeScan, fx_placeholder)),
Result
)
),
OuterResult, MAP(Numbers, LAMBDA(Curr, fx_rec(Curr, fx_rec))),
OuterResult
)
)(Sheet1!A2:A9)&&