Generate the given grid. This grid is called Tabula Recta. In Cryptology, this is also known as Vigenere Cipher. Yellow and bold has been done just to emphasize, you need not apply any conditional formatting for this. Note – First row and first column need to be part of the answer.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 88
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Tabula Recta Grid with Power Query
Power Query solution 1 for Generate Tabula Recta Grid, proposed by Bo Rydobon 🇹🇭:
let
tab = Table.FromColumns(
List.Transform({0, 0 .. 25}, each List.FirstN(List.Skip({"A" .. "Z", "A" .. "Z"}, _), 26)),
{"", "A" .. "Z"}
)
in
tabPower Query solution 2 for Generate Tabula Recta Grid, proposed by Bhavya Gupta:
let
Source = {1 .. 26},
CharacterList = List.Transform(
Source,
each List.Transform(List.Numbers(_, 26), each Character.FromNumber(Number.Mod(_ - 1, 26) + 65))
),
ExpectedOutput = Table.FromColumns({{"A" .. "Z"}} & CharacterList, {""} & {"A" .. "Z"})
in
ExpectedOutputPower Query solution 3 for Generate Tabula Recta Grid, proposed by Matthias Friedmann:
letters.
Shorter with a list repetition like Victor Wang => this is a déjà vu!
Very compact with List.Range plus duplicated list.✔
let
Source = Table.FromColumns(
{{"A" .. "Z"}} & List.Transform({0 .. 25}, each [
a = _,
b = List.Transform(
{"A" .. "Z"},
each {"A" .. "Z"}{Number.Mod(List.PositionOf({"A" .. "Z"}, _) + a, 26)}
)][b]
),
{" ", "A" .. "Z"}
)
in
Source
b. with List.Range and duplicated list:
let
Source = Table.FromColumns(
{{"A".."Z"}} & List.Transform({0..25}, each List.Range({"A".."Z", "A".."Z"}, _, 26)),
{" ", "A".."Z"}
)
in
Source
Power Query solution 4 for Generate Tabula Recta Grid, proposed by Victor Wang:
let
Source = Table.FromColumns(
{{"A" .. "Z"}}
& List.Transform({0 .. 25}, each List.FirstN(List.Skip(List.Repeat({"A" .. "Z"}, 2), _), 26)),
{" "} & {"A" .. "Z"}
)
in
SourcePower Query solution 5 for Generate Tabula Recta Grid, proposed by Krzysztof Kominiak:
let
Source = {"A" .. "Z"},
Result = Table.FromColumns(
{Source}
& List.Split(
List.Accumulate(
{1 .. 25},
{"A" .. "Z"},
(S, C) =>
S
& List.Transform(
List.Transform(List.Positions(Source), each Number.Mod(_ + C, 26)),
each Source{_}
)
),
26
),
List.Combine({{"*"}, Source})
)
in
ResultSolving the challenge of Generate Tabula Recta Grid with Excel
Excel solution 1 for Generate Tabula Recta Grid, proposed by Bo Rydobon 🇹🇭:
=MAKEARRAY(27,
27,
LAMBDA(r,
c,
IF(r+c=2,
"",
CHAR(MOD(r+c-4+(r=1)+(c=1),
26)+65))))
=LET(s,
SEQUENCE(
27
),
t,
TOROW(
s
),
IF(s+t=2,
"",
CHAR(MOD(s+t-4+(s=1)+(t=1),
26)+65)))Excel solution 2 for Generate Tabula Recta Grid, proposed by Bo Rydobon 🇹🇭:
=CHAR(
MAKEARRAY(
26,
26,
LAMBDA(
r,
c,
MOD(
r+c-2,
26
)+65
)
)
)
=LET(
s,
SEQUENCE(
26
),
CHAR(
MOD(
s+TOROW(
s
)-2,
26
)+65
)
)
=LET(
s,
ROW(
1:26
),
CHAR(
MOD(
s+TOROW(
s
)-2,
26
)+65
)
)
=CHAR(
MOD(
ROW(
1:26
)+COLUMN(
A:Z
)-2,
26
)+65
)Excel solution 3 for Generate Tabula Recta Grid, proposed by Rick Rothstein:
=HSTACK(INDEX(CHAR(65+MOD(ROW(1:52)-1,26)),COLUMN(A:Z)+ROW(1:26)-1))Excel solution 4 for Generate Tabula Recta Grid, proposed by Rick Rothstein:
=CHAR(65+MOD(INDEX(SEQUENCE(26,26),ROW(1:26),COLUMN(A:Z))+ROW(1:26)-2,26))Excel solution 5 for Generate Tabula Recta Grid, proposed by John V.:
=LET(r,ROW(1:27),c,TOROW(r),IF(r+c=2,"",CHAR(65+MOD(IF(r<3,2,r)+IF(c<3,2,c)-4,26))))
✅ With Bo's idea, avoiding internal IF's:
=LET(r,ROW(1:27),c,TOROW(r),IF(r+c=2,"",CHAR(65+MOD(r+c+(r=1)+(c=1)-4,26))))
✅ Change IF to REPT:
=LET(r,ROW(1:27),c,TOROW(r),REPT(CHAR(65+MOD(r+c+(r=1)+(c=1)-4,26)),r+c>2))Excel solution 6 for Generate Tabula Recta Grid, proposed by محمد حلمي:
=CHAR(MOD(ROW(65:90)+
SEQUENCE(,26,0)-65,26)+65)Excel solution 7 for Generate Tabula Recta Grid, proposed by محمد حلمي:
=LET(
r,ROW(65:90),
HSTACK(VSTACK("",CHAR(r)),
VSTACK(CHAR(SEQUENCE(,26,65)),
CHAR(MOD(r+SEQUENCE(,26,0)-65,26)+65))))
without Headers
=CHAR(MOD(ROW(65:90)+
SEQUENCE(,26,0)-65,26)+65)Excel solution 8 for Generate Tabula Recta Grid, proposed by 🇰🇷 Taeyong Shin:
=LET(
code, MAKEARRAY(26, 26, LAMBDA(r,c, MOD(r + c - 2, 26))) + 65,
CHAR(HSTACK(VSTACK(32, TAKE(code, , 1)), VSTACK(TAKE(code, 1), code)))
)Excel solution 9 for Generate Tabula Recta Grid, proposed by Julian Poeltl:
=LET(
A,
CHAR(
SEQUENCE(
26
)+64
),
M,
MAKEARRAY(
26,
26,
LAMBDA(
A,
B,
CHAR(
MOD(
SUM(
A,
B
)-2,
26
)+65
)
)
),
VSTACK(
HSTACK(
"",
TOROW(
A
)
),
HSTACK(
A,
M
)
)
)Excel solution 10 for Generate Tabula Recta Grid, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_a,
MAKEARRAY(
26,
26,
LAMBDA(
r,
c,
CHAR(
MOD(
r + c - 2,
26
) + 65
)
)
),
VSTACK(
HSTACK(
"",
TAKE(
_a,
1
)
),
HSTACK(
TAKE(
_a,
,
1
),
_a
)
)
)
Way 2:
=LET(
_s1,
SEQUENCE(
26
),
_s2,
TOROW(
_s1
),
_a,
CHAR(
MOD(
_s1 + _s2 - 2,
26
) + 65
),
_r,
VSTACK(
HSTACK(
"",
TAKE(
_a,
1
)
),
HSTACK(
TAKE(
_a,
,
1
),
_a
)
),
_r
)Excel solution 11 for Generate Tabula Recta Grid, proposed by Timothée BLIOT:
=CHAR(65+MOD(SEQUENCE(
26,
26,
0
)+(SEQUENCE(
26,
,
0
)),
26))Excel solution 12 for Generate Tabula Recta Grid, proposed by Charles Roldan:
=IFERROR(CHAR(64+1/(1/MAKEARRAY(27,27,
LAMBDA(a,b,-3+a+b+(MIN(a,b)=1))))),"")Excel solution 13 for Generate Tabula Recta Grid, proposed by Charles Roldan:
=LET(
x,
SEQUENCE(
26
)-1,
y,
TOROW(
x
),
z,
HSTACK(
VSTACK(
"",
x
),
VSTACK(
y,
MOD(
x+y,
26
)
)
),
IFERROR(
CHAR(
z+65
),
""
)
)Excel solution 14 for Generate Tabula Recta Grid, proposed by Stefan Olsson:
=SCAN(1,
SCAN("ABCDEFGHIJKLMNOPQRSTUVWXYZ",
SEQUENCE(27),
LAMBDA(a, rr,
IFS(rr<3,a,TRUE, REGEXREPLACE(a,"(.)(.*)","$2$1"))
)
),
LAMBDA(i,z,
SPLIT(
REGEXREPLACE(
REGEXREPLACE(z, "(.)(.*)", IF(i=1," $1$2","$1$1$2"))
, "(.)", "$1,")
,",",TRUE,TRUE)
)
)Excel solution 15 for Generate Tabula Recta Grid, proposed by Victor Momoh (MVP, MOS, R.Eng):
=MAKEARRAY(26,26,LAMBDA(r,c,CHAR(65+MOD(24+r+c,26))))
With headers
=LET(a,MAKEARRAY(26,26,LAMBDA(r,c,CHAR(65+MOD(24+r+c,26)))),
HSTACK(VSTACK("",TAKE(a,,1)),VSTACK(TAKE(a,1),a)))Excel solution 16 for Generate Tabula Recta Grid, proposed by Viswanathan M B:
=LET(
Main,
CHAR(
65+MOD(
SEQUENCE(
26,
1,
0
)+SEQUENCE(
1,
26,
0
),
26
)
),
TpRw,
CHAR(
SEQUENCE(
1,
26,
65
)
),
FirstCol,
VSTACK(
" ",
CHAR(
SEQUENCE(
26,
1,
65
)
)
),
HSTACK(
FirstCol,
VSTACK(
TpRw,
Main
)
)
)Excel solution 17 for Generate Tabula Recta Grid, proposed by Morteza Rahmani:
=CONCAT(
"t",
BASE(
ROW(
10:35
),
36
) & "t",
"n"&BASE(
ROW(
10:35
),
36
)
)
Or
=CONCAT(
"t",
CHAR(
ROW(
65:90
)
) & "t",
"n"&CHAR(
ROW(
65:90
)
)
)
And for the Tabula Recta :
=LEFT(
CONCAT(
CHAR(
ROW(
INDIRECT(
CODE(
A2
)&":"&90
)
)
)&"g",
CHAR(
ROW(
INDIRECT(
65& ":" & CODE(
A2
)
)
)
)&"g"
),
76
)Excel solution 18 for Generate Tabula Recta Grid, proposed by Murat Hasanoglu:
=LEFT(
ADDRESS(
1;
MOD(
COLUMN()+ROW()-4;
26
)+1;
4;
1
)
)Solving the challenge of Generate Tabula Recta Grid with Python
Python solution 1 for Generate Tabula Recta Grid, proposed by Igor Perković:
import pandas as pd
l = [chr(c) for c in range(65, 91)]
acc = []
for r in range(0,len(l)):
res = [l[(i + r) % len(l)] for i, x in enumerate(l)]
acc.append(res)
df = pd.DataFrame(acc)
df.columns = df.index = l
df.to_excel('Result.xlsx')
Solving the challenge of Generate Tabula Recta Grid with SQL
SQL solution 1 for Generate Tabula Recta Grid, proposed by Zoran Milokanović:
WITH -- Microsoft SQL Server 2019
ALPHABET
AS
(
SELECT CHAR(65) AS LETTER
UNION ALL
SELECT CHAR(ASCII(A.LETTER) + 1)
FROM ALPHABET A
WHERE
A.LETTER <> 'Z'
),
HELPER
AS
(
SELECT
' ' + STRING_AGG(A.LETTER, '') AS HEADER
,LEN(STRING_AGG(A.LETTER, '')) AS LENGTHX
,STRING_AGG(A.LETTER, '') + STRING_AGG(A.LETTER, '') AS CONVERTER
FROM ALPHABET A
),
TABLEX
AS
(
SELECT
1 AS ORDINAL_NUMBER, A.CONVERTER, A.LENGTHX
FROM HELPER A
UNION ALL
SELECT
T.ORDINAL_NUMBER + 1 AS ORDINAL_NUMBER, T.CONVERTER, T.LENGTHX
FROM TABLEX T
WHERE
T.ORDINAL_NUMBER < 26
)
SELECT
H.HEADER AS ANSWER_EXPECTED
FROM HELPER H
UNION ALL
SELECT
SUBSTRING(T.CONVERTER, T.ORDINAL_NUMBER, 1) +
SUBSTRING(T.CONVERTER, T.ORDINAL_NUMBER, T.LENGTHX) AS ANSWER_EXPECTED
FROM TABLEX T
;