Generate Vigenere Cipher Grid. The grid shown is called Vigenere Cipher Grid which is used in Cryptography.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 302
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Vigenere Cipher Grid with Power Query
Power Query solution 1 for Generate Vigenere Cipher Grid, proposed by Bo Rydobon 🇹🇭:
let
C = {"A" .. "Z"},
Ans = Table.FromColumns(
List.Transform({0, 0 .. 25}, (n) => List.Skip(C, n) & List.FirstN(C, n)),
{" "} & C
)
in
AnsPower Query solution 2 for Generate Vigenere Cipher Grid, proposed by Zoran Milokanović:
let
Source = {"A" .. "Z"},
S = Table.FromRows(
{{null} & Source}
& List.Transform(
Source,
each
let
s = List.PositionOf(Source, _)
in
{_} & List.Skip(Source, s) & List.FirstN(Source, s)
)
)
in
SPower Query solution 3 for Generate Vigenere Cipher Grid, proposed by Aditya Kumar Darak 🇮🇳:
let
List = List.Transform({0 .. 25}, each List.Range({"A" .. "Z", "A" .. "Z"}, _, 26)),
Return = Table.FromColumns({List{0}} & List, {" "} & List{0})
in
ReturnPower Query solution 4 for Generate Vigenere Cipher Grid, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
AL = {"A".."Z"},
Sol = Table.FromRows({{null}&AL} & List.Transform({0..25}, each {AL{_}} & List.Skip(AL,_) & List.FirstN(AL,_)))
in
Sol
Show translation
Show translation of this commentPower Query solution 5 for Generate Vigenere Cipher Grid, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = {"A" .. "Z"},
Sol = Table.FromColumns(
{{""} & Source}
& Table.ToColumns(
Table.FromRows(
{Source} & List.Transform({0 .. 25}, each List.Range(Source & Source, _, 26))
)
)
)
in
SolPower Query solution 6 for Generate Vigenere Cipher Grid, proposed by Luan Rodrigues:
let
Fonte = [
a = {"A" .. "Z"},
b = Table.FromRows({{null} & a})
& Table.FromColumns(
{a}
& List.Transform(
a,
(x) => [p = List.PositionOf(a, x), s = List.Range(List.Skip(a, p) & a, 0, 26)][s]
)
)
][b]
in
FonteSolving the challenge of Generate Vigenere Cipher Grid with Excel
Excel solution 1 for Generate Vigenere Cipher Grid, proposed by Bo Rydobon 🇹🇭:
=MAKEARRAY(
27,
27,
LAMBDA(
i,
j,
IF(
i+j=2,
"",
CHAR(
MOD(
MAX(
i-2,
0
)+MAX(
j-2,
0
),
26
)+65
)
)
)
)Excel solution 2 for Generate Vigenere Cipher Grid, proposed by Bo Rydobon 🇹🇭:
=LET(
n,
SEQUENCE(
26
),
c,
CHAR(
MOD(
n+TOROW(
n
)-2,
26
)+65
),
HSTACK(
VSTACK(
"",
TAKE(
c,
,
1
)
),
VSTACK(
TAKE(
c,
1
),
c
)
)
)Excel solution 3 for Generate Vigenere Cipher Grid, proposed by Rick Rothstein:
=LET(d,DROP(WRAPROWS(CHAR(65+MOD(SEQUENCE(,1352,0),26)),27),-25,-1),HSTACK(VSTACK("",TAKE(d,,1)),VSTACK(TAKE(d,1),d)))Excel solution 4 for Generate Vigenere Cipher Grid, proposed by John V.:
=LET(v,ROW(1:27),h,TOROW(v),REPT(CHAR(65+MOD(v+h-(v>1)-(h>1)-2,26)),v+h>2))Excel solution 5 for Generate Vigenere Cipher Grid, proposed by محمد حلمي:
=LET(
s,
SEQUENCE(
26
)-1,
e,
TOROW(
s
),
CHAR(
65+VSTACK(
HSTACK(
-33,
e
),
HSTACK(
s,
MOD(
s+e,
26
)
)
)
)
)Excel solution 6 for Generate Vigenere Cipher Grid, proposed by Kris Jaganah:
=LET(
a,
SEQUENCE(
26
),
IFERROR(
CHAR(
HSTACK(
VSTACK(
"",
a
),
REDUCE(
SEQUENCE(
,
26
),
a,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
p,
SEQUENCE(
,
26,
y
),
IF(
p>26,
p-26,
p
)
)
)
)
)
)+64
),
""
)
)Excel solution 7 for Generate Vigenere Cipher Grid, proposed by Timothée BLIOT:
=IFERROR(
CHAR(
HSTACK(
VSTACK(
"",
SEQUENCE(
26
)-1
),
VSTACK(
SEQUENCE(
,
26
)-1,
MOD(
SEQUENCE(
26,
26,
0
)+SEQUENCE(
26
)-1,
26
)
)
)+65
),
""
)Excel solution 8 for Generate Vigenere Cipher Grid, proposed by Oscar Mendez Roca Farell:
=MAKEARRAY(27,
27,
LAMBDA(r,
c,
IF(r+c>2,
CHAR(64+MOD(r+c-(c>=2)-(r>1)-2,
26)+1),
"")))Excel solution 9 for Generate Vigenere Cipher Grid, proposed by Sunny Baggu:
=LET(
_seq,
SEQUENCE(
26,
,
65
),
IFERROR(
CHAR(
VSTACK(
HSTACK(
"",
TOROW(
_seq
)
),
HSTACK(
_seq,
DROP(
REDUCE(
"",
_seq,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
_a,
SEQUENCE(
,
26,
v
),
IF(
_a > 90,
_a - 26,
_a
)
)
)
)
),
1
)
)
)
),
""
)
)Excel solution 10 for Generate Vigenere Cipher Grid, proposed by LEONARD OCHEA 🇷🇴:
=MAKEARRAY(27,27,LAMBDA(a,b,CHAR(IFS(a+b=2,32,a=1,b+63,b=1,a+63,1,MOD(a+b-4,26)+65))))Excel solution 11 for Generate Vigenere Cipher Grid, proposed by Abdallah Ally:
=LET(
a,
SEQUENCE(
26,
,
65
),
REDUCE(
HSTACK(
"",
TOROW(
CHAR(
a
)
)
),
a,
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
CHAR(
y
),
LET(
b,
SEQUENCE(
26,
,
y
),
TOROW(
CHAR(
IF(
b>90,
b-26,
b
)
)
)
)
)
)
)
)
)Excel solution 12 for Generate Vigenere Cipher Grid, proposed by Md. Zohurul Islam:
=LET(a,CHAR(SEQUENCE(,26,65)),sq,SEQUENCE(25,,2),
b,REDUCE(a,sq,LAMBDA(x,y,LET(p,TAKE(a,,y-1),q,DROP(a,,y-1),s,VSTACK(x,HSTACK(q,p)),s))),
c,HSTACK(CHAR(SEQUENCE(26,,65)),b),
d,VSTACK(HSTACK("",a),c),
d)Excel solution 13 for Generate Vigenere Cipher Grid, proposed by Julien Lacaze:
=CHAR(
64+MAKEARRAY(
26,
26,
LAMBDA(
r,
c,
MOD(
r+c-2,
26
)+1
)
)
)
With First Row/First Column :
=HSTACK(
VSTACK(
"",
CHAR(
SEQUENCE(
26,
,
65
)
)
),
VSTACK(
CHAR(
SEQUENCE(
,
26,
65
)
),
CHAR(
64+MAKEARRAY(
26,
26,
LAMBDA(
r,
c,
MOD(
r+c-2,
26
)+1
)
)
)
)
)Excel solution 14 for Generate Vigenere Cipher Grid, proposed by Pieter de Bruijn:
=LET(
r,
ROW(
1:27
)-1,
c,
TOROW(
r
),
m,
MOD(
-1+r+c,
26
),
IF(
r+c,
IF(
r*c,
CHAR(
64+IF(
m,
m,
26
)
),
CHAR(
65+m
)
),
""
)
)Excel solution 15 for Generate Vigenere Cipher Grid, proposed by Pieter de Bruijn:
=LET(
s,
ROW(
1:26
),
a,
CHAR(
64+MOD(
s+TOROW(
s
)-2,
26
)+1
),
HSTACK(
VSTACK(
"",
TAKE(
a,
,
1
)
),
VSTACK(
TAKE(
a,
1
),
a
)
)
)
or
=LET(
a,
CHAR(
ROW(
65:90
)
),
r,
ROWS(
a
),
s,
SEQUENCE(
r
),
HSTACK(
VSTACK(
"",
a
),
VSTACK(
TOROW(
a
),
INDEX(
a,
MOD(
s+TOROW(
s
)-2,
r
)+1
)
)
)
)
or
=LET(
s,
ROW(
1:26
),
a,
CHAR(
64+s
),
HSTACK(
VSTACK(
"",
a
),
VSTACK(
TOROW(
a
),
INDEX(
a,
MOD(
s+TOROW(
s
)-2,
26
)+1
)
)
)
)Excel solution 16 for Generate Vigenere Cipher Grid, proposed by Giorgi Goderdzishvili:
=
LET(
sq,SEQUENCE(,26),
cr,CHAR(63+sq+TRANSPOSE(sq)),
cd,63+sq+TRANSPOSE(sq),
md, MOD(cd-1,90)+1,
cre,CHAR(IF(md<65,64+md,cd)),
stck,HSTACK(VSTACK("",TAKE(cre,,1)), VSTACK(TAKE(cre,1),cre)),
stck)Excel solution 17 for Generate Vigenere Cipher Grid, proposed by Abdelrahman Omer, MBA, PMP:
=LET(
b,
REDUCE(
"",
SEQUENCE(
26
),
LAMBDA(
x,
y,
HSTACK(
VSTACK(
x,
y
),
y
)
)
),
IFERROR(
CHAR(
IFERROR(
b,
MAKEARRAY(
27,
27,
LAMBDA(
I,
j,
IF(
I+j>29,
I+j-29,
I+j-3
)
)
)
)+64
),
""
)
)Excel solution 18 for Generate Vigenere Cipher Grid, proposed by Daniel Garzia:
=LET(
g,
CHAR(
65+MOD(
ROW(
1:26
)+SEQUENCE(
,
26,
0
)-1,
26
)
),
HSTACK(
VSTACK(
"",
TAKE(
g,
,
1
)
),
VSTACK(
TAKE(
g,
1
),
g
)
)
)Excel solution 19 for Generate Vigenere Cipher Grid, proposed by Kriddakorn Pongthanisorn:
=LET(
r,
SEQUENCE(
1,
26,
0
),
c,
SEQUENCE(
26,
1,
0
),
h,
HSTACK(
"",
CHAR(
r+65
)
),
b,
CHAR(
IF(
65+r+c>90,
39+r+c,
65+r+c
)
),
VSTACK(
h,
HSTACK(
CHAR(
c+65
),
b
)
)
)Excel solution 20 for Generate Vigenere Cipher Grid, proposed by Jeff Blakley:
=LET(s,SEQUENCE(,26,10),tbl,s+SEQUENCE(26,,0),IFERROR(BASE(VSTACK(HSTACK("",s),HSTACK(TOCOL(s),tbl-INT(tbl/36)*26)),36),""))Solving the challenge of Generate Vigenere Cipher Grid with Python
_x000D_Python solution 1 for Generate Vigenere Cipher Grid, proposed by Bo Rydobon 🇹🇭:
c = [[chr((i+j)%26+65) for j in range(26)] for i in range(26)]
df= pd.DataFrame(c)
df.columns = c[0]
df.index = c[0]
df
Solving the challenge of Generate Vigenere Cipher Grid with R
_x000D_R solution 1 for Generate Vigenere Cipher Grid, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
test = read_excel("Vigenere Cipher Grid.xlsx", range = "A2:AA28") %>% column_to_rownames('...1')
df <- tibble(base = LETTERS)
vigenere_grid <- bind_cols(df, map_dfc(0:24, ~ {
shifted <- LETTERS[(seq_along(LETTERS) + .x) %% 26 + 1]
tibble(!!LETTERS[.x + 1] := shifted)
})
rownames(vigenere_grid) <- LETTERS
colnames(vigenere_grid) <- LETTERS
vigenere_grid = as.data.frame(vigenere_grid)
identical(test, vigenere_grid)
#> [1] TRUE