Columnar Transposition Cipher Write the keyword in a row Rank the letters of keyword as per their position in English language in descending order. Hence a = 1, b = 2 If letters are repeated, first letter will have higher rank among repeated letters and then second one and so on. Write the message without space beneath the keyword where message will be wrapped at equal to length of keyword. Now, read message column wise to create the encrypted sentence. First rank 1 column will be read, then rank 2 column and so on Plain text = carry firearms Keyword – xono x o n o 4 2 1 3 c a r r y f i r e a r m s Answer : rir afa rrm cyes
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 442
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Encrypt Using Columnar Cipher with Power Query
Power Query solution 1 for Encrypt Using Columnar Cipher, proposed by Bo Rydobon 🇹🇭:
let
Source = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]),
Ans = List.Transform(
Source,
(p) =>
let
k = List.Zip(
{Text.ToList(p{1})}
& List.Split(Text.ToList(Text.Replace(p{0}, " ", "")), Text.Length(p{1}))
)
in
Text.Combine(
List.Transform(
List.Sort(k, each _{0} & Text.From(List.PositionOf(k, _))),
each Text.Combine(List.Skip(_))
),
" "
)
)
in
AnsPower Query solution 2 for Encrypt Using Columnar Cipher, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
PT = Text.ToList(Text.Remove([Plain Text], " ")),
K = Text.ToList([Keyword]),
L = List.Count(K),
N = {1 .. L},
S = List.Split(PT, L),
T = List.Transform(
S,
(f) =>
[
z = List.Zip({N, K, f}),
s = List.Sort(z, {(x) => x{1}, (x) => x{0}}),
t = List.Transform(s, (x) => x{2})
][t]
),
Z = List.Zip(T),
C = List.Transform(Z, Text.Combine),
R = Text.Combine(C, " ")
][R]
)
in
ReturnSolving the challenge of Encrypt Using Columnar Cipher with Excel
Excel solution 1 for Encrypt Using Columnar Cipher, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
p,
k,
LET(
c,
LEN(
k
),
TEXTJOIN(
" ",
,
BYCOL(
SORTBY(
MID(
SUBSTITUTE(
p,
" ",
),
SEQUENCE(
9,
c
),
1
),
MID(
k,
SEQUENCE(
,
c
),
1
)
),
CONCAT
)
)
)
)
)Excel solution 2 for Encrypt Using Columnar Cipher, proposed by John V.:
=MAP(
SUBSTITUTE(
A2:A10,
" ",
),
B2:B10,
LAMBDA(
p,
k,
LET(
n,
LEN(
k
),
TEXTJOIN(
" ",
,
BYROW(
SORTBY(
WRAPCOLS(
MID(
p,
ROW(
1:20
),
1
),
n,
""
),
MID(
k,
SEQUENCE(
n
),
1
)
),
CONCAT
)
)
)
)
)Excel solution 3 for Encrypt Using Columnar Cipher, proposed by محمد حلمي:
=MAP(
SUBSTITUTE(
A2:A10,
" ",
),
B2:B10,
LAMBDA(
a,
b,
LET(
j,
LEN(
b
),
s,
SEQUENCE(
,
j
),
TEXTJOIN(
" ",
,
BYCOL(
s,
LAMBDA(
u,
CONCAT(
REPT(
MID(
a,
SEQUENCE(
50,
j
),
1
),
SORTBY(
s,
SORTBY(
s,
MID(
b,
s,
1
)
)
)=u
)
)
)
)
)
)
)
)Excel solution 4 for Encrypt Using Columnar Cipher, proposed by Kris Jaganah:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
x,
y,
LET(
a,
SEQUENCE(
,
LEN(
y
)
),
b,
MID(
y,
a,
1
),
c,
XLOOKUP(
b&a,
SORT(
b&a,
,
,
1
),
a
),
d,
SUBSTITUTE(
x,
" ",
""
),
e,
WRAPROWS(
MID(
d,
SEQUENCE(
LEN(
d
)
),
1
),
LEN(
y
),
""
),
TEXTJOIN(
" ",
,
MAP(
a,
LAMBDA(
x,
CONCAT(
FILTER(
e,
c=x
)
)
)
)
)
)
)
)Excel solution 5 for Encrypt Using Columnar Cipher, proposed by Julian Poeltl:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
P,
K,
LET(
SPi,
LAMBDA(
T,
MID(
T,
SEQUENCE(
,
LEN(
T
)
),
1
)
),
SP,
SPi(
K
),
Sort,
SORT(
SP,
,
,
TRUE
),
X,
XMATCH(
SP,
Sort
),
Su,
SUBSTITUTE(
P,
" ",
""
),
CS,
BYCOL(
WRAPROWS(
SPi(
Su
),
LEN(
K
),
""
),
LAMBDA(
A,
CONCAT(
A
)
)
),
TEXTJOIN(
" ",
,
MAP(
SEQUENCE(
,
LEN(
K
)
),
LAMBDA(
D,
TEXTJOIN(
" ",
,
IFERROR(
FILTER(
CS,
X=D
),
""
)
)
)
)
)
)
)
)Excel solution 6 for Encrypt Using Columnar Cipher, proposed by Timothée BLIOT:
=MAP(A2:A10,
B2:B10,
LAMBDA(y,
z,
LET(A,
SUBSTITUTE(
y,
" ",
""
),
L,
LEN(
z
),
S,
SEQUENCE(
L
),
B,
IFNA(
WRAPROWS(
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
),
L
),
""
),
K,
MID(
z,
S,
1
),
C,
MAP(
K,
LAMBDA(
x,
XMATCH(
x,
SORT(
K
)
)
)
),
O,
SCAN(0,
S,
LAMBDA(w,
v,
INDEX(
C,
v
)+SUM(--(TAKE(
C,
v
)=INDEX(
C,
v
)))-1)),
TEXTJOIN(
" ",
,
BYCOL(
CHOOSECOLS(
B,
SORTBY(
S,
O
)
),
LAMBDA(
y,
CONCAT(
y
)
)
)
))))Excel solution 7 for Encrypt Using Columnar Cipher, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
F,
LAMBDA(
i,
SEQUENCE(
LEN(
i
)
)
),
s,
F(
b
),
n,
CODE(
MID(
b,
s,
1
)
)+s%,
m,
MAP(
n,
LAMBDA(
a,
SUM(
N(
n<=a
)
)
)
),
TEXTJOIN(
" ",
,
BYCOL(
CHOOSECOLS(
WRAPROWS(
MID(
SUBSTITUTE(
a,
" ",
""
),
F(
a
),
1
),
MAX(
m
),
""
),
XMATCH(
s,
m
)
),
LAMBDA(
c,
CONCAT(
c
)
)
)
)
)
)
)Excel solution 8 for Encrypt Using Columnar Cipher, proposed by Sunny Baggu:
=MAP(
A2:A10,
B2:B10,
LAMBDA(p,
k,
LET(
_e1,
LAMBDA(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
_a,
(CODE(
_e1(
k
)
) & SEQUENCE(
LEN(
k
)
)) + 0,
_b,
TOROW(
XMATCH(
_a,
SORT(
_a,
,
)
)
),
_c,
SORTBY(WRAPROWS(_e1(SUBSTITUTE(
p,
" ",
""
)),
LEN(
k
),
""),
_b),
TEXTJOIN(
" ",
,
BYCOL(
_c,
LAMBDA(
a,
CONCAT(
a
)
)
)
)
)
)
)Excel solution 9 for Encrypt Using Columnar Cipher, proposed by Abdallah Ally:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
u,
v,
LET(
s,
SEQUENCE(
LEN(
u
)
),
t ,
SEQUENCE(
LEN(
v
)
),
a,
MID(
u,
s,
1
),
b,
MID(
v,
t,
1
),
c,
WRAPROWS(
FILTER(
a,
a>" "
),
LEN(
v
),
""
),
d,
SORTBY(
t,
b
),
REDUCE(
"",
d,
LAMBDA(
x,
y,
TEXTJOIN(
" ",
,
x,
CONCAT(
CHOOSECOLS(
c,
y
)
)
)
)
)
)
)
)Excel solution 10 for Encrypt Using Columnar Cipher, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
l,
LEN(
b
),
p,
SUBSTITUTE(
a,
" ",
),
TEXTJOIN(
" ",
,
BYCOL(
SORTBY(
MID(
p,
SEQUENCE(
LEN(
p
),
l
),
1
),
CODE(
MID(
b,
SEQUENCE(
,
l
),
& 1
)
)
),
LAMBDA(
c,
CONCAT(
c
)
)
)
)
)
)
)Excel solution 11 for Encrypt Using Columnar Cipher, proposed by Andy Heybruch:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
_plaintext,
_kw,
LET(
_kwnums,
UNICODE(
MID(
_kw,
SEQUENCE(
,
LEN(
_kw
)
),
1
)
),
_txt,
SUBSTITUTE(
_plaintext,
" ",
""
),
_txtletters,
MID(
_txt,
SEQUENCE(
LEN(
_txt
)
),
1
),
_order,
SORT(
VSTACK(
_kwnums,
WRAPROWS(
_txtletters,
LEN(
_kw
),
""
)
),
,
,
1
),
TEXTJOIN(
" ",
,
BYCOL(
DROP(
_order,
1
),
LAMBDA(
a,
CONCAT(
a
)
)
)
)
)
)
)Excel solution 12 for Encrypt Using Columnar Cipher, proposed by Sandeep Marwal:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
PT,
a,
KW,
b,
PTwithoutspace,
SUBSTITUTE(
PT,
" ",
""
),
kwbreaked,
TRANSPOSE(
MID(
KW,
SEQUENCE(
LEN(
KW
)
),
1
)
),
ptbreaked,
WRAPROWS(
TRANSPOSE(
MID(
PTwithoutspace,
SEQUENCE(
LEN(
PTwithoutspace
)
),
1
)
),
LEN(
KW
),
""
),
sorted,
SORTBY(
ptbreaked,
kwbreaked,
1
),
sortedwithspace,
VSTACK(
sorted,
MAKEARRAY(
1,
COLUMNS(
kwbreaked
),
LAMBDA(
r,
c,
" "
)
)
),
output,
TRIM(
TEXTJOIN(
"",
,
TOCOL(
sortedwithspace,
1,
TRUE
)
)
),
output
)
)
)Excel solution 13 for Encrypt Using Columnar Cipher, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
l,
LEN(
b
),
o,
MID(
b,
SEQUENCE(
,
l
),
1
),
t,
SUBSTITUTE(
a,
" ",
""
),
TEXTJOIN(
" ",
,
BYCOL(
SORTBY(
WRAPROWS(
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
),
l,
""
),
o
),
CONCAT
)
)
)
)
)Excel solution 14 for Encrypt Using Columnar Cipher, proposed by Josh Brodrick:
=MAP(A2:A10,B2:B10,LAMBDA(p,k,LET(a,CODE(MID(k,SEQUENCE(1,LEN(k)),1)),b,MID(SUBSTITUTE(p," ",""),SEQUENCE(1,LEN(p)),1),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCAT(TRANSPOSE(EXPAND(DROP(SORT(VSTACK(a,WRAPROWS(b,COUNT(a)," ")),,,TRUE),1),COUNT(a)+2,," ")))," "," ")," "," ")," "," "))))Excel solution 15 for Encrypt Using Columnar Cipher, proposed by Tyler Cameron:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
t,
u,
LET(
a,
SUBSTITUTE(
t,
" ",
""
),
b,
LEN(
u
),
c,
DROP(
SORT(
VSTACK(
TOROW(
MID(
u,
SEQUENCE(
b
),
1
)
),
WRAPROWS(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
b,
""
)
),
,
,
TRUE
),
1
),
TEXTJOIN(
" ",
,
BYCOL(
c,
LAMBDA(
x,
CONCAT(
x
)
)
)
)
)
)
)Solving the challenge of Encrypt Using Columnar Cipher with Python in Excel
Python in Excel solution 1 for Encrypt Using Columnar Cipher, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_442 - Columnar Transposition Cipher.xlsx'
df = pd.read_excel(file_path)
# Perform data transformation and cleansing
def columnar_transposition_cipher(col1, col2):
length = len(col2)
col1 = col1.replace(' ', '')
letters = list(col1) + [''] * (length - len(col1) % length)
positions = [(x, y) for x, y in enumerate(col2, start=1)]
positions.sort(key=lambda x: x[1])
sort_values = [x[0] for x in positions]
chars = []
for ind, char in enumerate(letters, start=1):
if not ind % length: chars.append([length, char])
else: chars.append([ind % length, char])
results = []
for n in sort_values: results.append(''.join([s[1] for s in chars if s[0] == n]))
return ' '.join(results)
df['My Answer'] = df.apply(lambda x: columnar_transposition_cipher(x['Plain Text'], x['Keyword']), axis=1)
df['Correct'] = df['Answer Expected'] == df['My Answer']
df
Solving the challenge of Encrypt Using Columnar Cipher with R
R solution 1 for Encrypt Using Columnar Cipher, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/442 Columnar Transposition Cipher.xlsx", range = "A1:B10")
test = read_excel("Excel/442 Columnar Transposition Cipher.xlsx", range = "C1:C10")
encode = function(text, keyword){
rank(ties.method = "first")
l_key = length(keyword)
text = str_extract_all(text, "[a-z]")[[1]]
text_filled = c(text, rep("", l_key - length(text) %% l_key))
matrix_text = matrix(text_filled, ncol = l_key, byrow = TRUE)
matrix_text = matrix_text[, order(keyword)] %>% t()
matrix_text = matrix_text %>%
apply(1, paste, collapse = "") %>%
paste(collapse = " ")
return(matrix_text)
}
result = input %>%
mutate(`Answer Expected` = map2_chr(`Plain Text`, Keyword, encode))
R solution 2 for Encrypt Using Columnar Cipher, proposed by Anil Kumar Goyal:
library(tidyverse)
cipher <- function(key, MSG){
key_rank <- key %>%
str_split("") %>%
unlist() %>%
rank(ties.method = "first")
msg <- MSG %>%
str_replace_all(" ", "") %>%
str_split("") %>%
unlist()
if(length(msg) %% length(key_rank) != 0){
msg <- c(msg, rep(NA, length(key_rank) - (length(msg) %% length(key_rank))))
}
matrix(msg, ncol = length(key_rank), byrow = TRUE)[, order(key_rank)] %>%
apply(2, function(.x) str_c(.x[!is.na(.x)], collapse = "")) %>%
str_c(collapse = " ")
}
&&