Decrypt the Encrypted Text using Keyword Cipher. Encryption logic is given below, so to decrypt you would need to reverse the logic. Keyword Cipher – Write down A through Z in a row and write down the given keyword (without any duplicate letter) below that in second row. Remaining space in second row will be occupied by unused letters from A through Z. This mapping will be used to encrypt the plain text. Ex – Plain text – enemy coming, keyword – cannon abcdefghijklmnopqrstuvwxyz canobdefghijklmpqrstuvwxyz (n is used once only) Hence answer would be blbky nmkgle
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 471
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Decrypt Keyword Cipher Text with Power Query
Power Query solution 1 for Decrypt Keyword Cipher Text, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Alphabet = {"a" .. "z"},
Return = Table.AddColumn(
Source,
"Answer",
each [
KL = List.Distinct(Text.ToList([Keyword])),
RL = KL & List.Difference(Alphabet, KL),
ET = Text.ToList([Encrypted Text]),
R = Text.Combine(List.ReplaceMatchingItems(ET, List.Zip({RL, Alphabet})))
][R]
)
in
ReturnPower Query solution 2 for Decrypt Keyword Cipher Text, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
(x) =>
let
a = {"a" .. "z"},
b = List.Distinct(Text.ToList(x[Keyword])),
c = b & List.RemoveMatchingItems(a, b),
d = List.Zip({c, a}),
e = Text.Combine(List.ReplaceMatchingItems(Text.ToList(x[Encrypted Text]), d))
in
e
)[[Answer]]
in
SolPower Query solution 3 for Decrypt Keyword Cipher Text, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
(x) =>
let
a = Text.Combine({"a" .. "z"}),
b = List.Distinct(Text.ToList(x[Keyword])),
c = Text.Combine(b) & Text.Remove(a, b),
d = List.Transform(
Text.ToList(x[Encrypted Text]),
each List.PositionOf(Text.ToList(c) & {" "}, _)
),
e = Text.Combine(List.Transform(d, each (Text.ToList(a) & {" "}){_}))
in
e
)[[Answer]]
in
SolPower Query solution 4 for Decrypt Keyword Cipher Text, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
S,
"Answer Expected",
each
let
A = Text.ToList([Encrypted Text]),
B = Text.ToList([Keyword]),
C = {"a" .. "z"},
D = List.Distinct(B),
E = D & List.Difference(C, D),
F = Text.Combine(List.ReplaceMatchingItems(A, List.Zip({E, C})))
in
F
)
in
SolSolving the challenge of Decrypt Keyword Cipher Text with Excel
Excel solution 1 for Decrypt Keyword Cipher Text, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
r,
LAMBDA(
x,
REGEXEXTRACT(
x,
".",
1
)
),
CONCAT(
IFNA(
CHAR(
XMATCH(
r(
a
),
UNIQUE(
VSTACK(
r(
b
),
CHAR(
SEQUENCE(
26
)+96
)
)
)
)+96
),
r(
a
)
)
)
)
)
)Excel solution 2 for Decrypt Keyword Cipher Text, proposed by Rick Rothstein:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
f,
g,
LET(
c,
VSTACK(
CHAR(
SEQUENCE(
26,
,
97
)
),
" "
),
e,
UNIQUE(
MID(
g&CONCAT(
c
)&" ",
SEQUENCE(
27+LEN(
g
)
),
1
)
),
REDUCE(
"",
MID(
f,
SEQUENCE(
LEN(
f
)
),
1
),
LAMBDA(
a,
x,
a&XLOOKUP(
x,
e,
c
)
)
)
)
)
)Excel solution 3 for Decrypt Keyword Cipher Text, proposed by John V.:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
f,
LAMBDA(
t,
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
)
),
c,
CHAR(
ROW(
97:122
)
),
CONCAT(
XLOOKUP(
f(
a
),
UNIQUE(
VSTACK(
f(
b
),
c
)
),
c,
" "
)
)
)
)
)Excel solution 4 for Decrypt Keyword Cipher Text, proposed by محمد حلمي:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
r,
LAMBDA(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
x,
CHAR(
SEQUENCE(
26
)+96
),
CONCAT(
XLOOKUP(
r(
a
),
UNIQUE(
VSTACK(
r(
b
),
x
)
),
x,
" "
)
)
)
)
)Excel solution 5 for Decrypt Keyword Cipher Text, proposed by Kris Jaganah:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
x,
y,
LET(
a,
CHAR(
SEQUENCE(
26,
,
97
)
),
CONCAT(
XLOOKUP(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
UNIQUE(
VSTACK(
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
a
)
),
a,
" "
)
)
)
)
)Excel solution 6 for Decrypt Keyword Cipher Text, proposed by Julian Poeltl:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
T,
K,
LET(
C,
CHAR(
96+SEQUENCE(
26
)
),
V,
UNIQUE(
VSTACK(
MID(
K,
SEQUENCE(
LEN(
K
)
),
1
),
C
)
),
CONCAT(
XLOOKUP(
MID(
T,
SEQUENCE(
LEN(
T
)
),
1
),
V,
C,
" "
)
)
)
)
)Excel solution 7 for Decrypt Keyword Cipher Text, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
kw,
UNIQUE(
MID(
b,
SEQUENCE(
LEN(
b
)
),
1
)
),
c,
CHAR(
SEQUENCE(
26,
,
97
)
),
rl,
VSTACK(
kw,
UNIQUE(
VSTACK(
kw,
c
),
,
1
)
),
et,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
f,
IFNA(
XLOOKUP(
et,
rl,
c
),
et
),
r,
CONCAT(
f
),
r
)
)
)Excel solution 8 for Decrypt Keyword Cipher Text, proposed by Timothée BLIOT:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
v,
w,
LET(
F,
LAMBDA(
n,
MID(
n,
SEQUENCE(
LEN(
n
)
),
1
)
),
A,
UNIQUE(
F(
w
)
),
B,
CHAR(
SEQUENCE(
26
)+96
),
CONCAT(
MAP(
F(
v
),
LAMBDA(
x,
XLOOKUP(
x,
VSTACK(
A,
TOCOL(
MAP(
B,
LAMBDA(
x,
IF(
ISNA(
XMATCH(
x,
A
)
),
x,
1/0
)
)
),
3
)
),
B,
" "
)
)
)
)
)
)
)Excel solution 9 for Decrypt Keyword Cipher Text, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
F,
LAMBDA(
i,
MID(
i,
SEQUENCE(
LEN(
i
)
),
1
)
),
s,
CHAR(
96+SEQUENCE(
26
)
),
CONCAT(
XLOOKUP(
F(
a
),
UNIQUE(
VSTACK(
F(
b
),
s
)
),
s,
" "
)
)
)
)
)Excel solution 10 for Decrypt Keyword Cipher Text, proposed by Sunny Baggu:
=MAP(
A2:A10,
B2:B10,
LAMBDA(a,
b,
LET(
_s,
CHAR(
SEQUENCE(
,
26,
CODE(
"a"
)
)
),
_e1,
LAMBDA(
x,
MID(
x,
SEQUENCE(
,
LEN(
x
)
),
1
)
),
_k,
_e1(b),
_uk,
UNIQUE(
_k,
1,
),
_c,
HSTACK(
_uk,
_s
),
_uc,
UNIQUE(
_c,
1
),
_e,
_e1(a),
CONCAT(
XLOOKUP(
_e,
_uc,
_s,
" "
)
)
)
)
)Excel solution 11 for Decrypt Keyword Cipher Text, proposed by LEONARD OCHEA 🇷🇴:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
e,
k,
LET(
F,
LAMBDA(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
m,
CHAR(
SEQUENCE(
26,
,
97
)
),
n,
UNIQUE(
VSTACK(
F(
k
),
m
)
),
CONCAT(
IFNA(
XLOOKUP(
F(
e
),
n,
m
),
F(
e
)
)
)
)
)
)Excel solution 12 for Decrypt Keyword Cipher Text, proposed by Asheesh Pahwa:
=LET(
et,
A2:A10,
k,
B2:B10,
MAP(
et,
k,
LAMBDA(
x,
y,
LET(
alp,
CHAR(
SEQUENCE(
26
)+96
),
l,
LAMBDA(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
ky,
UNIQUE(
l(
y
)
),
v,
UNIQUE(
VSTACK(
ky,
alp
)
),
md,
l(
x
),
CONCAT(
XLOOKUP(
md,
v,
alp,
" "
)
)
)
)
)
)Excel solution 13 for Decrypt Keyword Cipher Text, proposed by Nicolas Micot:
=LET(
_alphabet;
CAR(
SEQUENCE(
26;
;
CODE(
"a"
)
)
);
_key;
UNIQUE(
STXT(
B2;
SEQUENCE(
NBCAR(
B2
)
);
1
)
);
_cipher;
UNIQUE(
ASSEMB.V(
_key;
_alphabet
)
);
_encryptedText;
STXT(
A2;
SEQUENCE(
NBCAR(
A2
)
);
1
);
CONCAT(
MAP(
_encryptedText;
LAMBDA(
l_car;
RECHERCHEX(
l_car;
_cipher;
_alphabet;
l_car
)
)
)
)
)Excel solution 14 for Decrypt Keyword Cipher Text, proposed by Ziad A.:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
x,
y,
SORT(
LET(
S,
LAMBDA(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
a,
CHAR(
ROW(
97:122
)
),
b,
UNIQUE(
S(
y
)
),
JOIN(
,
XLOOKUP(
S(
x
),
{b;FILTER(
a,
0=COUNTIF(
b,
a
)
)},
a,
" "
)
)
)
)
)
)Excel solution 15 for Decrypt Keyword Cipher Text, proposed by Hussain Ali Nasser:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
e,
k,
LET(
_enc,
MID(
e,
SEQUENCE(
LEN(
e
)
),
1
),
_key,
UNIQUE(
MID(
k,
SEQUENCE(
LEN(
k
)
),
1
)
),
_let,
CHAR(
SEQUENCE(
26,
,
97
)
),
_rem,
FILTER(
_let,
NOT(
ISNUMBER(
XMATCH(
_let,
_key
)
)
)
),
CONCAT(
XLOOKUP(
_enc,
VSTACK(
_key,
_rem
),
_let,
" "
)
)
)
)
)Excel solution 16 for Decrypt Keyword Cipher Text, proposed by Hussain Ali Nasser:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
e,
k,
LET(
s,
LAMBDA(
a,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
),
_enc,
s(
e
),
_key,
UNIQUE(
s(
k
)
),
_let,
CHAR(
SEQUENCE(
26,
,
97
)
),
CONCAT(
XLOOKUP(
_enc,
UNIQUE(
VSTACK(
_key,
_let
)
),
_let,
" "
)
)
)
)
)Excel solution 17 for Decrypt Keyword Cipher Text, proposed by Tyler Cameron:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
x,
y,
CONCAT(
IFNA(
CHAR(
XMATCH(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
UNIQUE(
VSTACK(
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
CHAR(
SEQUENCE(
26
)+96
)
)
)
)+96
),
" "
)
)
)
)Excel solution 18 for Decrypt Keyword Cipher Text, proposed by Caroline Blake:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
x,
y,
LET(
_a,
UNICHAR(
SEQUENCE(
,
25,
97
)
),
_b,
UNIQUE(
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
)
),
_v,
CONCAT(
_b,
IF(
ISERROR(
FIND(
_a,
y
)
),
_a,
""
)
),
_c,
MID(
x,
SEQUENCE(
,
LEN(
x
),
1
),
1
),
_d,
FIND(
_c,
_v
),
CONCAT(
IFERROR(
UNICHAR(
_d+96
),
" "
)
)
)
)
)Solving the challenge of Decrypt Keyword Cipher Text with Python
Python solution 1 for Decrypt Keyword Cipher Text, proposed by Konrad Gryczan, PhD:
import pandas as pd
import string
def prepare_keycode(keyword):
alphabet = string.ascii_lowercase
keycode = list(keyword) + [char for char in alphabet if char not in keyword]
return keycode
def decode(sentence, keyword):
keycode = prepare_keycode(keyword)
code = dict(zip(keycode, string.ascii_lowercase))
decoded_sentence = " ".join(decoded_words)
return decoded_sentence
result = input.copy()
result["Answer Expected"] = result.apply(lambda row: decode(row["Encrypted Text"], row["Keyword"]), axis=1)
print(result["Answer Expected"].equals(test["Answer Expected"])) # True
Solving the challenge of Decrypt Keyword Cipher Text with Python in Excel
Python in Excel solution 1 for Decrypt Keyword Cipher Text, proposed by Abdallah Ally:
import pandas as pd
from string import ascii_lowercase
def decrypter(text1, text2):
string1 = ascii_lowercase
string2 = ''.join(list(dict.fromkeys(text2 + ascii_lowercase)))
text = ''
for char in text1:
if char == ' ': text += ' '
else: text += string1[string2.find(char)]
return text
df = pd.read_excel(file_path)
# Perform data wrangling
df['My Answer'] = df.apply(lambda x: decrypter(x[0], x[1]), axis=1)
df['Check'] = df['My Answer'] == df['Answer Expected']
df
Solving the challenge of Decrypt Keyword Cipher Text with R
R solution 1 for Decrypt Keyword Cipher Text, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
prepare_keycode = function(keyword) {
unique()
alphabet = letters
keycode = c(keyword, alphabet[!alphabet %in% keyword])
return(keycode)
}
keycode = prepare_keycode(keyword)
map(str_split, "") %>%
map(function(x) {
x = x %>%
unlist() %>%
code[.] %>%
paste(., collapse = "")
return(x)
})
sentence = paste(words, collapse = " ")
return(sentence)
}
result = input %>%
identical(result$`Answer Expected`, test$`Answer Expected`)
# [1] TRUE
&&