Bifid Cipher with Keyword Suppose we want to encrypt the message “BATTLE” and keyword is “ROTORS”. We prepare 5×5 grid as shown where unique alphabets from keyword are written in the sequence in which they appear followed by remaining alphabets of English Language (J will not be present as J is interchanged with I) Now, message is written at top and row and column positions are written in next 2 rows BATTLE 211132 153354 Now, concat row 2 and 3. 211132153354 Extract first 2 letters, then next 2 letters and so on 21 11 32 15 33 54 Now, if it is ij, it means lookup intersection of row i and column j. Hence, we retrieve following alphabets which the encrypted text. BRHAIY
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 437
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Encrypt Using Bifid Cipher with Power Query
Power Query solution 1 for Encrypt Using Bifid Cipher, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.AddColumn(
Source,
"Ans",
each
let
L = (x) => List.ReplaceMatchingItems(x, {{"j", "i"}}),
d = List.Distinct(L(Text.ToList([Keywords]) & {"a" .. "z"})),
c = List.Transform(
List.Split(
List.Combine(
List.Zip(
List.Transform(
L(Text.ToList([Plain Text])),
each
let
m = List.PositionOf(d, _)
in
{Number.IntegerDivide(m, 5), Number.Mod(m, 5)}
)
)
),
2
),
each d{_{0} * 5 + _{1}}
)
in
Text.Combine(c)
)
in
AnsPower Query solution 2 for Encrypt Using Bifid Cipher, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
List = {"a" .. "i", "k" .. "z"},
Return = Table.AddColumn(
Source,
"Answer",
each [
S = Text.ToList(Text.Replace([Plain Text], "j", "i")),
L = List.Distinct(Text.ToList([Keywords])),
NL = L & List.Difference(List, L),
P = List.ReplaceMatchingItems(S, List.Zip({NL, {0 .. 25}})),
I = List.Transform(P, (f) => Number.IntegerDivide(f, 5)),
J = List.Transform(P, (f) => Number.Mod(f, 5)),
C = List.Split(I & J, 2),
O = List.Transform(C, (f) => NL{f{0} * 5 + f{1}}),
R = Text.Combine(O)
][R]
)
in
ReturnPower Query solution 3 for Encrypt Using Bifid Cipher, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(
Source,
"Answer",
each
let
a = List.Distinct(Text.ToList(Text.Replace([Keywords], "j", "i"))),
b = List.Split(a & List.RemoveItems({"a" .. "z"}, a & {"j"}), 5),
d = Table.ToColumns(Table.FromRows(b)),
e = List.Transform({b, d}, (x) => List.Transform({1 .. 5}, each {_} & x{_ - 1})),
f = List.Transform(
List.Split(
List.Combine(
List.Transform(
e,
(y) =>
List.Transform(
Text.ToList([Plain Text]),
each List.RemoveNulls(
List.Transform(
y,
(x) => if List.Contains(x, _) then Text.From(List.First(x)) else null
)
){0}
)
)
),
2
),
each Number.From(Text.Combine(_))
),
g = Table.FromRows(e{0}, {"A", "1" .. "5"}),
h = Table.TransformColumns(
Table.UnpivotOtherColumns(g, {"A"}, "At", "Va"),
{"A", Text.From}
),
i = Table.ToRows(Table.AddColumn(h, "N", each Number.From([A] & [At]))[[N], [Va]]),
j = Text.Combine(List.ReplaceMatchingItems(f, i))
in
j
)[[Answer]]
in
APower Query solution 4 for Encrypt Using Bifid Cipher, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x, y) =>
let
A = Table.AddColumn,
T = Table.FromColumns,
F = Text.From,
P = List.PositionOf,
K = Text.ToList(Text.Upper(y)),
L = List.ReplaceValue(K, "J", "I", Replacer.ReplaceText),
N = List.Distinct(L) & List.Difference({"A" .. "I", "K" .. "Z"}, L),
R = A(T({N, {0 .. 24}}, {"T", "N"}), "R", each Number.IntegerDivide([N], 5) + 1),
C = A(R, "C", each Number.Mod([N], 5) + 1),
M = A(C, "M", each F([R]) & F([C])),
a = T({Text.ToList(Text.Upper(x))}, {"P"}),
b = Table.ReplaceValue(a, "J", "I", Replacer.ReplaceText, {"P"}),
c = A(b, "R", each M[R]{P(M[T], [P])}),
d = A(c, "C", each M[C]{P(M[T], [P])}),
e = T({List.Transform(List.Split(d[R] & d[C], 2), each Text.Combine(List.Transform(_, F)))}),
f = A(e, "T", each M[T]{P(M[M], [Column1])})[T],
g = Text.Lower(Text.Combine(f))
in
g,
Sol = Table.AddColumn(S, "Answer Expected", each Fx([Plain Text], [Keywords]))
in
SolPower Query solution 5 for Encrypt Using Bifid Cipher, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Result = Table.AddColumn(Source, "Answer", each
let
PT = [Plain Text], Kw = [Keywords], LC = List.Combine, LT = List.Transform,
TL = Text.ToList, LP = List.PositionOf, NR = Number.RoundUp,
Grill =
[
a = List.Distinct(TL(Text.Replace(Kw, "j", "i"))),
b = {"a".."i","k".."z"},
c = List.Difference(b,a),
d = List.Split(a & c,5),
Rows = LC(d),
Cols = LC(List.Zip(d)),
Gr = Table.FromRows(d, {"1".."5"})
],
Desc =
let
e = TL(PT),
f = LT(e, each NR((LP(Grill[Rows], _) + 1) / 5)),
g = LT(e, each NR((LP(Grill[Cols], _) + 1) / 5)),
h = List.Split(f & g, 2),
i = LT(h, each Table.Column(Grill[Gr], Text.From(_{1})){_{0} - 1})
in
Text.Combine(i,"")
in
Desc
)[[Answer]]
in
Result
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Solving the challenge of Encrypt Using Bifid Cipher with Excel
Excel solution 1 for Encrypt Using Bifid Cipher, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
L,
LAMBDA(
x,
MID(
SUBSTITUTE(
x,
"j",
"i"
),
SEQUENCE(
LEN(
x
)
),
1
)
),
d,
UNIQUE(
L(
b&CONCAT(
CHAR(
SEQUENCE(
26,
,
97
)
)
)
)
),
m,
XMATCH(
L(
a
),
d
)-1,
CONCAT(
INDEX(
d,
MMULT(
WRAPROWS(
VSTACK(
INT(
m/5
),
MOD(
m,
5
)
),
2
),
{5;1}
)+1
)
)
)
)
)Excel solution 2 for Encrypt Using Bifid Cipher, proposed by John V.:
=MAP(A2:A10,
SUBSTITUTE(
B2:B10,
"j",
"i"
),
LAMBDA(p,
k,
LET(n,
ROW(
1:26
),
s,
SEQUENCE(
LEN(
p
)
),
c,
UNIQUE(
VSTACK(
CODE(
MID(
k,
SEQUENCE(
LEN(
k
)
),
1
)
),
96+n
)
),
b,
11+BASE(n-(n>XMATCH(
106,
c
)-1)-1,
5),
e,
XLOOKUP(
CODE(
MID(
p,
s,
1
)
),
c,
b
),
CONCAT(
CHAR(
XLOOKUP(
--MID(
CONCAT(
LEFT(
e
),
RIGHT(
e
)
),
2*s-1,
2
),
b,
c
)
)
))))Excel solution 3 for Encrypt Using Bifid Cipher, proposed by محمد حلمي:
=MAP(A2:A10,
SUBSTITUTE(
B2:B10,
"j",
"i"
),
LAMBDA(a,
b,
LET(
x,
CHAR(
SEQUENCE(
26
)+96
),
i,
WRAPROWS(
UNIQUE(
VSTACK(
MID(
b,
SEQUENCE(
LEN(
b
)
),
1
),
FILTER(
x,
x<>"j"
)
)
),
5
),
r,
LAMBDA(x,
v,
MAP(MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
LAMBDA(a,
SUM((a=i)*SEQUENCE(
5^x,
5^v
))))),
v,
WRAPROWS(
VSTACK(
r(
1,
0
),
r(
0,
1
)
),
2
),
CONCAT(
INDEX(
i,
TAKE(
v,
,
1
),
DROP(
v,
,
1
)
)
))))Excel solution 4 for Encrypt Using Bifid Cipher, proposed by Kris Jaganah:
=MAP(A2:A10,
B2:B10,
LAMBDA(x,
y,
LET(a,
SEQUENCE,
b,
a(
LEN(
y
)
),
c,
a(
25
),
d,
UNIQUE(
VSTACK(
SUBSTITUTE(
MID(
y,
b,
1
),
"j",
"i"
),
CHAR(
IF(
c>9,
c+1,
c
)+96
)
)
),
e,
INT(
a(
5,
5,
,
1/5
)
),
f,
TOCOL(
e
),
g,
TOCOL(
e,
,
1
),
h,
a(
,
LEN(
x
)
),
i,
MID(x,
(h),
1),
j,
XLOOKUP,
k,
j(
i,
d,
f
),
l,
j(
i,
d,
g
),
CONCAT(
XLOOKUP(
BYROW(
WRAPROWS(
HSTACK(
k,
l
),
2
),
CONCAT
),
f&g,
d
)
))))Excel solution 5 for Encrypt Using Bifid Cipher, proposed by Julian Poeltl:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
T,
K,
LET(
JT,
SUBSTITUTE(
T,
"j",
"i"
),
JK,
SUBSTITUTE(
K,
"j",
"i"
),
UJK,
UNIQUE(
MID(
JK,
SEQUENCE(
LEN(
JK
)
),
1
)
),
CS,
SUBSTITUTE(
CHAR(
SEQUENCE(
26
)+96
),
"j",
""
),
S,
IF(
ISNUMBER(
XMATCH(
CS,
UJK
)
),
"",
CS
),
F,
FILTER(
S,
S<>""
),
KS,
VSTACK(
UJK,
F
),
SF,
SEQUENCE(
25
),
SC,
MOD(
SF-1,
5
)+1,
SR,
ROUNDUP(
SF/5,
0
),
SP,
MID(
T,
SEQUENCE(
LEN(
T
)
),
1
),
X,
CONCAT(
VSTACK(
XLOOKUP(
SP,
KS,
SR
),
XLOOKUP(
SP,
KS,
SC
)
)
),
P,
MID(
X,
SEQUENCE(
LEN(
X
)/2,
,
,
2
),
2
),
CONCAT(
XLOOKUP(
LEFT(
P,
1
)&RIGHT(
P,
1
),
SR&SC,
KS
)
)
)
)
)Excel solution 6 for Encrypt Using Bifid Cipher, proposed by Timothée BLIOT:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
v,
w,
LET(
A,
SEQUENCE(
5,
5
),
B,
CHAR(
IF(
A>9,
A+1,
A
)+96
),
D,
WRAPROWS(
UNIQUE(
VSTACK(
MID(
SUBSTITUTE(
w,
"j",
"i"
),
SEQUENCE(
LEN(
w
)
),
1
),
TOCOL(
B
)
)
),
5
),
E,
LAMBDA(
n,
MAP(
MID(
v,
SEQUENCE(
LEN(
v
)
),
1
),
LAMBDA(
x,
TOCOL(
IF(
x=D,
n,
1/0
),
3
)
)
)
),
G,
WRAPROWS(
VSTACK(
E(
ROW(
1:5
)
),
E(
COLUMN(
1:5
&)
)
),
2
),
CONCAT(
MAP(
TAKE(
G,
,
1
),
TAKE(
G,
,
-1
),
LAMBDA(
x,
y,
INDEX(
D,
x,
y
)
)
)
)
)
)
)Excel solution 7 for Encrypt Using Bifid Cipher, proposed by Hussein SATOUR:
=MAP(SUBSTITUTE(
A2:A10,
"j",
"i"
),
SUBSTITUTE(
B2:B10,
"j",
"i"
),
LAMBDA(x,
y,
LET(j,
LAMBDA(
w,
SEQUENCE(
LEN(
w
)
)
),
h,
CHAR(
SEQUENCE(
26
)+96
),
f,
UNIQUE(
VSTACK(
MID(
y,
j(
y
),
1
),
FILTER(
h,
h<>"j"
)
)
),
e,
XMATCH(
MID(
x,
j(
x
),
1
),
f
),
c,
MOD(
e,
5
),
a,
MID(
CONCAT(
ROUNDUP(
e/5,
0
),
IF(
c,
c,
5
)
),
j(
x
)*2-1,
2
),
CONCAT(INDEX(f,
(LEFT(
a
)-1)*5+RIGHT(
a
))))))Excel solution 8 for Encrypt Using Bifid Cipher, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
k,
LET(
F,
LAMBDA(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
R,
ROW(
1:5
),
W,
WRAPROWS(
VSTACK(
UNIQUE(
SUBSTITUTE(
F(
k
),
"j",
"i"
)
),
UNIQUE(
VSTACK(
F(
k
),
"j",
CHAR(
ROW(
1:26
)+96
)
),
,
1
)
),
5
),
P,
WRAPROWS(
TOCOL(
TEXTSPLIT(
CONCAT(
MAP(
F(
a
),
LAMBDA(
i,
CONCAT(
REPT(
R&"|"&TOROW(
R
),
W=i
)
)
)
)&"-"
),
"|",
"-",
1
),
,
1
),
2
),
CONCAT(
INDEX(
W,
DROP(
P,
,
-1
),
DROP(
P,
,
1
)
)
)
)
)
)Excel solution 9 for Encrypt Using Bifid Cipher, proposed by Sunny Baggu:
=MAP(
A2:A10,
B2:B10,
LAMBDA(a,
b,
LET(
_e1,
LAMBDA(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
_tsp,
_e1(a),
_cu,
CODE(UNIQUE(SUBSTITUTE(_e1(b),
"j",
"i"))),
_c,
SEQUENCE(
26,
,
CODE(
"a"
)
),
_num,
VSTACK(
_cu,
TOCOL(
IF(
1 - ISNUMBER(
XMATCH(
_c,
VSTACK(
106,
_cu
)
)
),
_c,
x
),
3
)
),
_ch,
CHAR(
_num
),
_s,
SEQUENCE(
5
) & SEQUENCE(
,
5
),
_v,
TOCOL(
_s
),
_c1,
XLOOKUP(
_tsp,
_ch,
_v
),
_c2,
WRAPROWS(
VSTACK(
LEFT(
_c1
),
RIGHT(
_c1
)
),
2
),
_c3,
INDEX(
_s,
TAKE(
_c2,
,
1
),
TAKE(
_c2,
,
-1
)
),
CONCAT(
XLOOKUP(
_c3,
_v,
_ch
)
)
)
)
)Excel solution 10 for Encrypt Using Bifid Cipher, proposed by Narayanan J 🇮🇳:
=LET(
arr,
LAMBDA(
txt,
MID(
txt,
SEQUENCE(
LEN(
txt
)
),
1
)
),
c,
UNIQUE(
VSTACK(
arr(
UPPER(
C1
)
),
CHAR(
SEQUENCE(
26,
1,
65
)
)
)
),
code,
WRAPROWS(
TOCOL(
IF(
c="J",
NA(),
c
),
2
),
5
),
pos,
TAKE(
SEQUENCE(
10,
10,
11
),
5,
5
),
fnd,
LAMBDA(
a,
TOCOL(
IF(
code=a,
pos,
NA()
),
2
)
),
mp,
MAP(
arr(
UPPER(
SUBSTITUTE(
D1,
"J",
"I"
)
)
),
LAMBDA(
I,
fnd(
I
)
)
),
cpr,
CONCAT(
LEFT(
mp
),
RIGHT(
mp
)
),
cPos,
WRAPROWS(
MID(
cpr,
SEQUENCE(
LEN(
cpr
)
),
1
),
2
),
CONCAT(
MAP(
INDEX(
cPos,
,
1
),
INDEX(
cPos,
,
2
),
LAMBDA(
r,
c,
INDEX(
code,
r,
c
)
)
)
)
)Excel solution 11 for Encrypt Using Bifid Cipher, proposed by Talia Cao, CPA:
=MAP(A2:A10,
B2:B10,
LAMBDA(Txt,
Key,
LET(
ABC,
CHAR(
SEQUENCE(
26,
,
97
)
),
kChars,
UNIQUE(
MID(
Key,
SEQUENCE(
LEN(
Key
)
),
1
)
),
Matrix,
TAKE(
WRAPROWS(
UNIQUE(
SUBSTITUTE(
VSTACK(
kChars,
ABC
),
"j",
"i"
)
),
5
),
5
),
tChars,
MID(
Txt,
SEQUENCE(
,
LEN(
Txt
)
),
1
),
tRow,
MAP(tChars,
LAMBDA(t,
SUM((Matrix = t) * SEQUENCE(
5
)))),
tCol,
MAP(tChars,
LAMBDA(t,
SUM((Matrix = t) * SEQUENCE(
,
5
)))),
Pos,
MID(
CONCAT(
tRow,
tCol
),
SEQUENCE(
LEN(
Txt
),
,
,
2
),
2
),
CONCAT(
INDEX(
Matrix,
LEFT(
Pos
),
RIGHT(
Pos
)
)
)
)))Solving the challenge of Encrypt Using Bifid Cipher with Python in Excel
Python in Excel solution 1 for Encrypt Using Bifid Cipher, proposed by Abdallah Ally:
import pandas as pd
from string import ascii_lowercase
from math import ceil
def bifid_cipher2(col1, col2):
characters = ''
text = col2.replace('j', 'i') + ascii_lowercase.replace('j', '')
for char in text:
if char not in characters:
characters += char
values = []
for index, char in enumerate(characters, start=1):
i = ceil(index / 5)
j = index - (index // 5) * 5
values.append((char, str(i), str(5 if j == 0 else j)))
text1 = [x[1] for char in col1.replace('j', 'i') for x in values if char == x[0]]
text2 = [x[2] for char in col1.replace('j', 'i') for x in values if char == x[0]]
text = ''.join(text1 + text2)
encrypted_text = ''
for i in range(0, len(text), 2):
sample = text[i: i + 2]
for value in values:
if sample[0] == value[1] and sample[1] == value[2]:
encrypted_text += value[0]
return encrypted_text
file_path = 'Excel_Challenge_437 - Bifid Cipher_Part 2.xlsx'
df = pd.read_excel(file_path)
df['My Answer'] = df.apply(lambda x: bifid_cipher2(x['Plain Text'], x['Keywords']), axis=1)
df
Solving the challenge of Encrypt Using Bifid Cipher with R
R solution 1 for Encrypt Using Bifid Cipher, proposed by Anil Kumar Goyal:
bifid <- function(WORD, KEY = "ROSE"){
mat <- KEY %>%
toupper() %>%
str_split("") %>%
unlist() %>%
unique() %>%
{c(., subset(LETTERS, !LETTERS %in% c(., "J")))} %>%
matrix(nrow=5, byrow = TRUE)
str_split(WORD, "") %>%
unlist() %>%
map(~ which(mat == .x, arr.ind = TRUE) %>% as.vector()) %>%
unlist() %>%
matrix(ncol = 2, byrow = TRUE) %>%
as.vector() %>%
split.default(., ((seq_along(.) - 1) %/% 2) + 1 ) %>%
map_chr(~mat[.x[1], .x[2]]) %>%
str_c(collapse = "")
}
&&