AUTOKEY CIPHER – Key is written beneath Plaintext and if key is shorter than Plaintext, then remaining slots beneath Plaintext are filled by letters from Plaintext starting from beginning. a through z are denoted by 0..25 which are assigned to letters in both rows and values are summed. Then number value of Ciphertext is calculated by Sum Calculated MOD 26 and character representation of this is Ciphertext. Ex. Plaintext – attack, Key – how attack howatt 019190210 7142201919 7334102129 hhpavd Ans – hhpavd
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 393
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Encrypt with Autokey Cipher with Power Query
Power Query solution 1 for Encrypt with Autokey Cipher, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
A = Text.Length,
B = Text.ToList,
C = List.ReplaceMatchingItems,
D = Text.Range,
E = List.Transform,
F = List.Zip,
a = A([Plain Text]),
b = A([Keyword]),
c = if a > b then [Keyword] & D([Plain Text], 0, a - b) else D([Keyword], 0, a),
d = F({{"a" .. "z"}, {0 .. 25}}),
e = F(E({B([Plain Text]), B(c)}, each C(_, d))),
f = E({0 .. List.Count(e) - 1}, each List.Sum(e{_})),
g = E(f, each Number.Mod(_, 26)),
h = Text.Combine(C(g, E(d, List.Reverse)))
in
h
)[[Answer]]
in
SolPower Query solution 2 for Encrypt with Autokey Cipher, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each
let
a = List.Zip({{"a" .. "z"}, {0 .. 25}}),
pt = [Plain Text],
kw = [Keyword] & [Plain Text],
b = List.Transform(
{0 .. List.Count(Record.FieldValues(_)) - 1},
each if _ = 0 then Text.ToList(pt) else List.FirstN(Text.ToList(kw), Text.Length(pt))
),
c = List.Transform(b, each List.ReplaceMatchingItems(_, a)),
d = List.Transform(List.Zip(c), each Number.Mod(List.Sum(_), 26)),
e = Text.Combine(List.ReplaceMatchingItems(d, List.Transform(a, List.Reverse)))
in
e
)
in
resPower Query solution 3 for Encrypt with Autokey Cipher, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x, y) =>
let
a = Text.ToList(x),
b = Text.ToList(y),
c = List.Count(a),
d = List.Count(b),
e = if c <= d then List.FirstN(b, c) else b & List.FirstN(a, c - d),
f = List.Zip({{"a" .. "z"}, {0 .. 25}}),
g = List.ReplaceMatchingItems(a, f),
h = List.ReplaceMatchingItems(e, f),
i = List.Transform({0 .. List.Count(e) - 1}, each g{_} + h{_}),
j = List.Transform(i, each if _ >= 26 then Number.Mod(_, 26) else _),
k = List.Transform(f, each List.Reverse(_))
in
Text.Combine(List.ReplaceMatchingItems(j, k)),
Sol = Table.AddColumn(S, "Answer Expected", each Fx([Plain Text], [Keyword]))
in
SolSolving the challenge of Encrypt with Autokey Cipher with Excel
Excel solution 1 for Encrypt with Autokey Cipher, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
s,
SEQUENCE(
LEN(
a
)
),
CONCAT(
CHAR(
MOD(
CODE(
MID(
b&a,
s,
1
)
)+CODE(
MID(
a,
s,
1
)
)+14,
26
)+97
)
)
)
)
)Excel solution 2 for Encrypt with Autokey Cipher, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
s,
SEQUENCE(
LEN(
a
)
),
LOWER(
CONCAT(
INDEX(
T3:AS28,
CODE(
MID(
a,
s,
1
)
)-96,
CODE(
MID(
b&a,
s,
1
)
)-96
)
)
)
)
)
)Excel solution 3 for Encrypt with Autokey Cipher, proposed by Rick Rothstein:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
y,
z,
LET(
f,
LAMBDA(
x,
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)-97
),
CONCAT(
MAP(
f(
y
),
f(
LEFT(
z&y,
LEN(
y
)
)
),
LAMBDA(
a,
b,
CHAR(
MOD(
a+b,
26
)+97
)
)
)
)
)
)
)Excel solution 4 for Encrypt with Autokey Cipher, proposed by محمد حلمي:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
s,
SEQUENCE(
LEN(
a
)
),
CONCAT(
CHAR(
MOD(
CODE(
MID(
a,
s,
1
)
)+CODE(
MID(
b&a,
s,
1
)
)-194,
26
)+97
)
)
)
)
)Excel solution 5 for Encrypt with Autokey Cipher, proposed by محمد حلمي:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
j,
LEN(
a
),
d,
MID(
a,
SEQUENCE(
j
),
1
),
CONCAT(
CHAR(
MOD(
CODE(
d
)+CODE(
TAKE(
VSTACK(
MID(
b,
SEQUENCE(
LEN(
b
)
),
1
),
d
),
j
)
)-194,
26
)+97
)
)
)
)
)Excel solution 6 for Encrypt with Autokey Cipher, proposed by Julian Poeltl:
=MAP(A2:A10,
B2:B10,
LAMBDA(A,
B,
LOWER(CONCAT(LET(T,
A,
K,
B,
L,
LEN(
T
),
KK,
LEFT(
K&T,
L
),
INDEX(E_AtoZHorizontal(),
1,
1+MOD((XMATCH(
E_SplitTextintoLetter_Array(
KK
),
E_AtoZHorizontal()
)-1+XMATCH(
E_SplitTextintoLetter_Array(
T
),
E_AtoZHorizontal()
)-1),
26)))))))
E_AtoZHorizontal(): =LAMBDA(
CHAR(
64+SEQUENCE(
1;26
)
)
)
E_SplitTextintoLetter_Array(): =LAMBDA(
Text;MID(
Text;SEQUENCE(
1;LEN(
Text
)
);1
)
)Excel solution 7 for Encrypt with Autokey Cipher, proposed by Julian Poeltl:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
A,
B,
CONCAT(
LET(
L,
LEN(
A
),
CHAR(
MOD(
CODE(
MID(
A,
SEQUENCE(
L
),
1
)
)+CODE(
MID(
B&A,
SEQUENCE(
L
),
1
)
)-CODE(
"a"
)*2,
26
)+CODE(
"a"
)
)
)
)
)
)Excel solution 8 for Encrypt with Autokey Cipher, proposed by Timothée BLIOT:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
v,
w,
LET(
A,
LEN(
v
),
B,
SEQUENCE(
A
),
C,
MID(
v,
B,
1
),
D,
MAP(
B,
LAMBDA(
x,
IF(
x>LEN(
w
),
MID(
v,
x-LEN(
w
),
1
),
MID(
w,
x,
1
)
)
)
),
CONCAT(
CHAR(
MOD(
CODE(
C
)+CODE(
D
)-194,
26
)+97
)
)
)
)
)Excel solution 9 for Encrypt with Autokey Cipher, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
_t,
LEN(
a
),
CONCAT(
CHAR(
MOD(
MMULT(
IFERROR(
CODE(
MID(
HSTACK(
a,
IF(
LEN(
b
)>_t,
b,
b&a
)
),
SEQUENCE(
_t
),
1
)
)-97,
),
{1;1}
),
26
)+97
)
)
)
)
)Excel solution 10 for Encrypt with Autokey Cipher, proposed by Sunny Baggu:
=MAP(
A2:A10,
B2:B10,
LAMBDA(x,
y,
LET(
_e1,
LAMBDA(
t,
MID(
t,
SEQUENCE(
,
LEN(
t
)
),
1
)
),
CONCAT(
CHAR(
97 +
MOD(
BYCOL(
CODE(
VSTACK(_e1(
x
),
_e1(IFERROR(
y & LEFT(
x,
LEN(
x
) - LEN(
y
)
),
LEFT(
y,
LEN(
x
)
)
)))
) - 97,
LAMBDA(
a,
SUM(
a
)
)
),
26
)
)
)
)
)
)Excel solution 11 for Encrypt with Autokey Cipher, proposed by 🇵🇪 Ned Navarrete C.:
=LET(
s,
SEQUENCE(
26
),
m,
CHAR(
s+96
),
x,
s-1,
t,
A2:A10,
k,
LEFT(
B2:B10&t,
LEN(
t
)
),
MAP(
t,
k,
LAMBDA(
a,
b,
LET(
i,
SEQUENCE(
LEN(
a
)
),
e,
LAMBDA(
j,
XLOOKUP(
MID(
j,
i,
1
),
m,
x
)
),
CONCAT(
XLOOKUP(
MOD(
e(
a
)+e(
b
),
26
),
x,
m
)
)
)
)
)
)Excel solution 12 for Encrypt with Autokey Cipher, proposed by Asheesh Pahwa:
=LET(pt,
A2:A10,
ky,
B2:B10,
MAP(pt,
ky,
LAMBDA(x,
y,
LET(a,
SEQUENCE(
26,
,
0
),
b,
CHAR(
SEQUENCE(
26
)+96
),
I,
LEN(
x
),
k,
LEN(
y
),
n,
IF(1>k,
y&CONCAT(TAKE (MID(
X,
SEQUENCE(
1
),
1
),
I-k)),
y),
e,
MID(
x,
& SEQUENCE(
LEN(
x
)
),
1
),
f,
MID(
n,
SEQUENCE(
LEN(
n
)
),
1
),
g,
XLOOKUP(
e,
b,
a
),
h,
XLOOKUP(
f,
b,
a
),
CONCAT(
XLOOKUP(
MOD(
g+h,
26
),
a,
b
)
)))))Excel solution 13 for Encrypt with Autokey Cipher, proposed by Charles Roldan:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
s,
SEQUENCE(
LEN(
a
)
),
CONCAT(
CHAR(
MOD(
CODE(
MID(
a,
s,
1
)
) + CODE(
MID(
b & a,
s,
1
)
) - 2 * CODE(
"a"
),
26
) + CODE(
"a"
)
)
)
)
)
)Excel solution 14 for Encrypt with Autokey Cipher, proposed by Edwin Tisnado:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
x,
y,
LET(
s,
SEQUENCE(
50
),
CONCAT(
TOCOL(
CHAR(
MOD(
CODE(
MID(
x,
s,
1
)
)-97+CODE(
MID(
y&x,
s,
1
)
)-97,
26
)+97
),
2
)
)
)
)
)Excel solution 15 for Encrypt with Autokey Cipher, proposed by James Mott:
=MAP(A2:A10,
B2:B10,
LAMBDA(a,
b,
LET(
n,
SEQUENCE(
26,
,
0
),
c,
CHAR(
SEQUENCE(
26,
,
97
)
),
p,
MID(
a,
SEQUENCE(
,
LEN(
a
)
),
1
),
k,
MID(
b,
SEQUENCE(
,
LEN(
b
)
),
1
),
d,
IF(LEN(
a
)Solving the challenge of Encrypt with Autokey Cipher with R
R solution 1 for Encrypt with Autokey Cipher, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/393 Autokey Cipher.xlsx", range = "A1:B10")
test = read_excel("Excel/393 Autokey Cipher.xlsx", range = "C1:C10")
recode = function(string, keyword) {
alphabet = data.frame(letters = letters, value = 0:25)
string_length = nchar(string)
keyword_length = nchar(keyword)
str_chars = str_split(string, "")[[1]]
key_chars = str_split(keyword, "")[[1]]
if (keyword_length > string_length) {
full_key = key_chars[1:string_length]
} else if (keyword_length < string_length) {
nchars_to_fill = string_length - keyword_length
chars_to_fill = str_chars[1:nchars_to_fill]
full_key = c(key_chars, chars_to_fill)
} else {
full_key = key_chars
}
code_table = data.frame(string = str_chars, key = full_key)
result = code_table %>%
left_join(alphabet, by = c("string" = "letters")) %>%
left_join(alphabet, by = c("key" = "letters")) %>%
mutate(value = value.x + value.y) %>%
select(string, key, value) %>%
mutate(value_mod = value %% 26) %>%
left_join(alphabet, by = c("value_mod" = "value")) %>%
pull(letters) %>%
paste(collapse = "")
return(result)
}
result = input %>%
mutate(answer = map2_chr(`Plain Text`, `Keyword`, recode))
&&