Double Accumulate Cipher Step 1. Take a string and and replace its alphabets with a=0….z=25 in an array. Step 2. Perform the accumulative sum of respective elements of the array where one element is equal to this element + sum of previous elements. Step 3. Take modulo 26 of the elements of the array. Step 4. Apply steps 2 & 3 on the resultant array of steps 3. Step 5. Convert the array into respective alphabets where 0=a….25=z Ex. horse Step 1. (7, 14, 17, 18, 4) Step 2. (7, 21, 38, 56, 60) Step 3. (7, 21, 12, 4, 8) Step 4. (7, 28, 40, 44, 52) => apply modulo 26 => (7, 2, 14, 18, 0) Step 5. Answer would be hcosa
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 427
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Double Accumulated Cipher Encoding with Power Query
Power Query solution 1 for Double Accumulated Cipher Encoding, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
List = List.Zip({{"a" .. "z"}, {0 .. 25}}),
Return = Table.AddColumn(
Source,
"Answer",
each [
S = Text.ToList([Plain Text]),
R = List.ReplaceMatchingItems(S, List),
O = List.Generate(
() => [a = 0, b = R{a}, c = b, d = S{a}],
each [a] < List.Count(S),
each [
a = [a] + 1,
b = Number.Mod([b] + R{a}, 26),
c = Number.Mod([c] + b, 26),
d = List{c}{0}
],
each [d]
),
F = Text.Combine(O)
][F]
)
in
ReturnPower Query solution 2 for Double Accumulated Cipher Encoding, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
lista = List.Zip({{"a" .. "z"}, {0 .. 25}}),
res = List.Transform(
Fonte[Plain Text],
each
let
a = List.ReplaceMatchingItems(Text.ToList(_), lista),
b = List.Accumulate({1 .. List.Count(a) - 1}, {a{0}}, (s, c) => s & {a{c} + List.Last(s)}),
c = List.Transform(b, each Number.Mod(_, 26)),
d = List.Accumulate({1 .. List.Count(c) - 1}, {c{0}}, (x, y) => x & {c{y} + List.Last(x)}),
e = List.ReplaceMatchingItems(
List.Transform(d, each Number.Mod(_, 26)),
List.Transform(lista, List.Reverse)
)
in
Text.Combine(e)
)
in
resPower Query solution 3 for Double Accumulated Cipher Encoding, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a_offset = Character.ToNumber("a"), // 97
to_num = (L as list) as list => List.Transform(L, (x) => Character.ToNumber(x) - a_offset),
to_char = (L as list) as list => List.Transform(L, (x) => Character.FromNumber(x + a_offset)),
accum = (L as list) as list => List.Accumulate(L, {}, (s, c) => s & {(List.Last(s) ?? 0) + c}),
mod_26 = (L as list) as list => List.Transform(L, (x) => Number.Mod(x, 26)),
double_accum = (txt as text) as text =>
Text.Combine(to_char(mod_26(accum(mod_26(accum(to_num(Text.ToList(txt)))))))),
AddCustomCol = Table.AddColumn(Source, "Answer", each double_accum([Plain Text]))
in
AddCustomColPower Query solution 4 for Double Accumulated Cipher Encoding, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
S,
"Answer Expected",
each
let
t = Text.ToList([Plain Text]),
a = List.Zip({{"a" .. "z"}, {0 .. 25}}),
b = List.ReplaceMatchingItems(t, a),
c = List.Accumulate(
List.Skip(b, 1) & {0},
[R = {}, C = b{0}],
(s, c) => [C = List.Sum({s[C], c}), R = s[R] & {s[C]}]
)[R],
d = List.Transform(c, each Number.Mod(_, 26)),
e = List.Accumulate(
List.Skip(d, 1) & {0},
[R = {}, C = d{0}],
(s, c) => [C = List.Sum({s[C], c}), R = s[R] & {s[C]}]
)[R],
f = List.Transform(e, each Number.Mod(_, 26)),
g = List.Zip({{0 .. 25}, {"a" .. "z"}}),
h = Text.Combine(List.ReplaceMatchingItems(f, g))
in
h
)
in
SolPower Query solution 5 for Double Accumulated Cipher Encoding, proposed by Glyn Willis:
letters of the last word :(
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Plain Text", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each
let az= {"a".."z"} in
[
t=[Plain Text],tl=Text.Length(t),t2l=Text.ToList(t),
fxlg=
(pass as number, string)=>
List.Generate(()=>
[i=0,l= if pass=1 then List.PositionOf(az,t2l{i}) else if pass=2 then Number.From(string{i}) else null, gp= if pass=3 then az{Number.From(string{i})} else null],
each [i]Solving the challenge of Double Accumulated Cipher Encoding with Excel
Excel solution 1 for Double Accumulated Cipher Encoding, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
LAMBDA(
a,
CONCAT(
CHAR(
MOD(
SCAN(
,
SCAN(
,
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)-97,
SUM
),
SUM
),
26
)+97
)
)
)
)
=MAP(
A2:A10,
LAMBDA(
a,
CONCAT(
CHAR(
REDUCE(
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)-97,
{1,
2},
LAMBDA(
b,
i,
MOD(
SCAN(
,
b,
SUM
),
26
)
)
)+97
)
)
)
)
=MAP(A2:A10,
LAMBDA(x,
LET(s,
SEQUENCE(
LEN(
x
)
),
t,
s-TOROW(
s
)+1,
CONCAT(CHAR(MOD(MMULT((t>0)*t,
CODE(
MID(
x,
s,
1
)
)-97),
26)+97)))))Excel solution 2 for Double Accumulated Cipher Encoding, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
r,
LET(
f,
LAMBDA(
z,
MOD(
SCAN(
,
z,
LAMBDA(
a,
x,
a+MOD(
x,
26
)
)
),
26
)
),
CONCAT(
CHAR(
97+f(
f(
CODE(
MID(
r,
SEQUENCE(
LEN(
r
)
),
1
)
)-97
)
)
)
)
)
)
)Excel solution 3 for Double Accumulated Cipher Encoding, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
CONCAT(
CHAR(
97+REDUCE(
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)-97,
{1;2},
LAMBDA(
a,
v,
MOD(
SCAN(
,
a,
SUM
),
26
)
)
)
)
)
)
)Excel solution 4 for Double Accumulated Cipher Encoding, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
a,
CONCAT(
CHAR(
MOD(
SCAN(
0,
MOD(
SCAN(
0,
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)-97,
LAMBDA(
a,
d,
a+d
)
),
26
),
LAMBDA(
a,
d,
a+d
)
),
26
)+97
)
)
)
)Excel solution 5 for Double Accumulated Cipher Encoding, proposed by Kris Jaganah:
=MAP(
A2:A10,
LAMBDA(
x,
CONCAT(
CHAR(
MOD(
SCAN(
,
MOD(
SCAN(
,
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)-97,
SUM
),
26
),
SUM
),
26
)+97
)
)
)
)Excel solution 6 for Double Accumulated Cipher Encoding, proposed by Julian Poeltl:
=MAP(A2:A10,
LAMBDA(T,
LET(SP,
CODE(
MID(
T,
SEQUENCE(
,
LEN(
T
)
),
1
)
)-97,
R,
LAMBDA(
S,
SCAN(
0,
S,
LAMBDA(
A,
B,
A+B
)
)
),
Mo,
LAMBDA(
A,
MOD(
A,
26
)
),
CONCAT(CHAR(97+(Mo(
R(
Mo(
R(
SP
)
)
)
)))))))Excel solution 7 for Double Accumulated Cipher Encoding, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
F,
LAMBDA(
n,
MOD(
SCAN(
0,
n,
LAMBDA(
w,
v,
w+v
)
),
26
)
),
CONCAT(
CHAR(
F(
F(
CODE(
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
)
)-97
)
)+97
)
)
)
)
)Excel solution 8 for Double Accumulated Cipher Encoding, proposed by Nikola Z Grujicic - Nikola Ž Grujičić:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
e,
MID(
a,
SEQUENCE(
LEN(
a
),
,
,
1
),
1
),
f,
CODE(
e
)-97,
g,
SCAN(
0,
f,
LAMBDA(
ef,
x,
ef+x
)
),
h,
MOD(
g,
26
),
i,
SCAN(
0,
h,
LAMBDA(
ha,
x,
ha+x
)
),
j,
MOD(
i,
26
),
TEXTJOIN(
"",
,
UNICHAR(
j+97
)
)
)
)
)Excel solution 9 for Double Accumulated Cipher Encoding, proposed by Hussein SATOUR:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)-97,
b,
SCAN(
,
a,
SUM
),
c,
MOD(
b,
& 26
),
d,
SCAN(
,
c,
SUM
),
e,
MOD(
d,
26
),
CONCAT(
CHAR(
e+97
)
)
)
)
)Excel solution 10 for Double Accumulated Cipher Encoding, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
F,
LAMBDA(
j,
MOD(
SCAN(
0,
j,
LAMBDA(
i,
x,
i+x
)
),
26
)
),
m,
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)-97,
CONCAT(
CHAR(
97+F(
F(
m
)
)
)
)
)
)
)Excel solution 11 for Double Accumulated Cipher Encoding, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
t,
LET(
_a,
CHAR(
SEQUENCE(
26,
,
CODE(
"a"
),
)
),
_n,
SEQUENCE(
26
) - 1,
_num,
XLOOKUP(
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
),
_a,
_n
),
CONCAT(
XLOOKUP(
MOD(
SCAN(
0,
MOD(
SCAN(
0,
_num,
LAMBDA(
a,
v,
a + v
)
),
26
),
LAMBDA(
x,
y,
x + y
)
),
26
),
_n,
_a
)
)
)
)
)Excel solution 12 for Double Accumulated Cipher Encoding, proposed by LEONARD OCHEA 🇷🇴:
=MAP(
A2:A10,
LAMBDA(
a,
CONCAT(
CHAR(
97+MOD(
SCAN(
,
MOD(
SCAN(
,
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)-97,
SUM
),
26
),
SUM
),
26
)
)
)
)
)
With with assigning names to repeating functions (R;MOD and S;SCAN)
=LET(
R,
LAMBDA(
x,
MOD(
x,
26
)
),
S,
LAMBDA(
x,
SCAN(
,
x,
SUM
)
),
MAP(
A2:A10,
LAMBDA(
a,
CONCAT(
CHAR(
97+R(
S(
R(
S(
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)-97
)
)
)
)
)
)
)
)
)Excel solution 13 for Double Accumulated Cipher Encoding, proposed by Pieter de B.:
=LET(
z,
LAMBDA(
v,
CONCAT(
CHAR(
MOD(
SCAN(
0,
CODE(
MID(
v,
SEQUENCE(
LEN(
v
)
),
1
)
)-97,
LAMBDA(
x,
y,
x+y
)
),
26
)+97
)
)
),
MAP(
A2:A10,
LAMBDA(
m,
z(
z(
m
)
)
)
)
)Excel solution 14 for Double Accumulated Cipher Encoding, proposed by Asheesh Pahwa:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
alp,
CHAR(
SEQUENCE(
26,
,
97
)
),
nm,
SEQUENCE(
26,
,
0
),
md,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
Ikp,
XLOOKUP(
md,
alp,
nm
),
rt,
SCAN(
0,
Ikp,
LAMBDA(
x+y
)
),
d,
MOD(
rt,
26
),
rnt,
SCAN(
0,
d,
LAMBDA(
a,
v,
a+v
)
),
m,
MOD(
rnt,
26
),
CONCAT(
XLOOKUP(
m,
nm,
alp
)
)
)
)
)Excel solution 15 for Double Accumulated Cipher Encoding, proposed by Charles Roldan:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
i,
SEQUENCE(
LEN(
x
)
),
CONCAT(
CHAR(
MOD(
MMULT(
MAP(
i - TOROW(
i
) + 1,
LAMBDA(
x,
MAX(
x,
0
)
)
),
CODE(
MID(
x,
i,
1
)
) - 97
),
26
) + 97
)
)
)
)
)Excel solution 16 for Double Accumulated Cipher Encoding, proposed by Ziad A.:
=LET(_,
LAMBDA(
x,
MOD(
SCAN(
,
x,
LAMBDA(
a,
c,
a+c
)
),
26
)
),
MAP(A2:A10,
LAMBDA(a,
JOIN(,
SORT(CHAR(97+_(_(CODE(
REGEXEXTRACT(
a,
REPT(
"(.)",
LEN(
a
)
)
)
)-97))))))))Excel solution 17 for Double Accumulated Cipher Encoding, proposed by Songglod P.:
=LET(
ac,
LAMBDA(
list,
MOD(
SCAN(
0,
list,
LAMBDA(
curr,
val,
curr+val
)
),
26
)
),
to_code,
LAMBDA(
txt,
UNICODE(
MID(
txt,
SEQUENCE(
1,
LEN(
txt
)
),
1
)
)-97
),
MAP(
A2:A10,
LAMBDA(
el,
CONCAT(
UNICHAR(
ac(
ac(
to_code(
el
)
)
)+97
)
)
)
)
)Excel solution 18 for Double Accumulated Cipher Encoding, proposed by Ernesto Vega Castillo:
=BYROW(A2:A10,
LAMBDA(u,
CONCAT(XLOOKUP(LET(q,
u,
m,
(CODE(
MID(
q,
SEQUENCE(
LEN(
q
)
),
1
)
)-97),
e,
LAMBDA(
acum,
iter,
SUM(
acum,
iter
)
),
w,
SCAN(
0,
m,
e
),
s,
SEQUENCE(
26,
,
0
),
x,
XLOOKUP(
MOD(
w,
26
),
s,
s
),
y,
SCAN(
0,
x,
e
),
XLOOKUP(
MOD(
y,
26
),
s,
s
)),
SEQUENCE(
26,
,
0
),
CHAR(
SEQUENCE(
26,
,
97
)
),
"",
0,
1))))Excel solution 19 for Double Accumulated Cipher Encoding, proposed by Hussain Ali Nasser:
=MAP(
A2:A10,
LAMBDA(
a,
CONCAT(
CHAR(
MOD(
SCAN(
,
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)-97,
LAMBDA(
a,
v,
a+v
)
),
26
)+97
)
)
)
)Excel solution 20 for Double Accumulated Cipher Encoding, proposed by Tyler Cameron:
=MAP(A2:A10,
LAMBDA(t,
LET(i,
LAMBDA(u,
LET(a,
SCAN(
0,
u,
LAMBDA(
x,
y,
x+y
)
),
a-(26*FLOOR.MATH(
a/26,
1
)))),
b,
CODE(
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
)
)-97,
CONCAT(
CHAR(
i(
i(
b
)
)+97
)
))))Excel solution 21 for Double Accumulated Cipher Encoding, proposed by Tyler Cameron:
=MAP(
A2:A10,
LAMBDA(
t,
LET(
i,
LAMBDA(
u,
LET(
a,
SCAN(
0,
u,
LAMBDA(
x,
y,
x+y
)
),
MOD(
a,
26
)
)
),
b,
CODE(
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
)
)-97,
CONCAT(
CHAR(
i(
i(
b
)
)+97
)
)
)
)
)Solving the challenge of Double Accumulated Cipher Encoding with Python
Python solution 1 for Double Accumulated Cipher Encoding, proposed by Konrad Gryczan, PhD:
import pandas as pd
def double_accumulative_cipher(word):
letters = 'abcdefghijklmnopqrstuvwxyz'
result = ''
accumulative_sum = 0
for char in word:
index = letters.index(char.lower())
accumulative_sum = (accumulative_sum + index) % 26
result += letters[accumulative_sum]
return result
test = pd.read_excel("427 Double Accumulative Cipher.xlsx", usecols="B", nrows=9)
input = pd.read_excel("427 Double Accumulative Cipher.xlsx", usecols="A", nrows= 9)
input['Answer Expected'] = input['Plain Text'].apply(double_accumulative_cipher).apply(double_accumulative_cipher)
input = input.drop(columns=['Plain Text'])
print(input.equals(test)) # True
Python solution 2 for Double Accumulated Cipher Encoding, proposed by Cristobal Salcedo Beltran:
import pandas as pd
from pyspark.sql import SparkSession
from pyspark.sql.functions import udf
from pyspark.sql.types import StringType
def double_accumulate_cipher(text):
array = [ord(char.lower()) - 97 for char in text]
accumulated_sum = [sum(array[:i+1]) % 26 for i in range(len(array))]
double_accumulated_sum = [sum(accumulated_sum[:i+1]) % 26 for i in range(len(accumulated_sum))]
result = ''.join(chr(num + 97) for num in double_accumulated_sum)
return result
double_accumulate_cipher_udf=udf(doub&le_accumulate_cipher, StringType())
file_path = "/lakehouse/default/Files/Challenge/Excel_Challenge_427 - Double Accumulative Cipher.xlsx"
pandas_df = pd.read_excel(file_path, nrows=9)
spark = SparkSession.builder.appName("DoubleAccumulateCipher").getOrCreate()
spark_df = spark.createDataFrame(pandas_df)
result_df.show(truncate=False)
Python solution 3 for Double Accumulated Cipher Encoding, proposed by Cristobal Salcedo Beltran:
import pandas as pd
def double_accumulate_cipher(text):
array = [ord(char.lower()) - 97 for char in text]
accumulated_sum = [sum(array[:i+1]) % 26 for i in range(len(array))]
double_accumulated_sum = [sum(accumulated_sum[:i+1]) % 26 for i in range(len(accumulated_sum))]
result = ''.join(chr(num + 97) for num in double_accumulated_sum)
return result
file_path = "/lakehouse/default/Files/Challenge/Excel_Challenge_427 - Double Accumulative Cipher.xlsx"
pandas_df = pd.read_excel(file_path, nrows=9)
pandas_df.head(9)
Solving the challenge of Double Accumulated Cipher Encoding with R
R solution 1 for Double Accumulated Cipher Encoding, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/427 Double Accumulative Cipher.xlsx", range = "A1:A10")
test = read_excel("Excel/427 Double Accumulative Cipher.xlsx", range = "B1:B10")
double_accumulative_cipher = function(word) {
result = strsplit(word, "")[[1]] %>%
map_dbl(~match(., letters) - 1) %>%
accumulate(~(.x + .y) %% 26) %>%
accumulate(~(.x + .y) %% 26) %>%
map_dbl(~. + 1) %>%
map_chr(~letters[.]) %>%
paste(collapse = "")
return(result)
}
result = input %>%
mutate(`Answer Expected` = map_chr(`Plain Text`, double_accumulative_cipher)) %>%
select(-`Plain Text`)
&