Vernam Cipher Encryption

Vernam Cipher (also known as One time pad cipher) 1. For a given message, a key is chosen having the same length. 2. Assign a=0, b=1,……y=24, z=25 to English alphabets. This is applicable for both the message as well as the key. 3. Add both the numbers for corresponding English alphabet numbers for both the message as well as the key 4. If result > 25, then subtract 26 from it otherwise leave as it is. 5. For the resultant number, assign the alphabets as per step 2. Example: Message = war and Key = tom w=22, a=0, r=17 t=19, o=14, m=12 Addition of these = 41, 14, 29 41 and 29 are > 25, hence reduce 26. Hence final numbers are 15, 14, 3 English alphabets corresponding to this – p, o, d Hence encrypted text will become pod.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 162
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Vernam Cipher Encrypt with Power Query

Power Query solution 1 for Vernam Cipher Encrypt, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Comb = List.Transform(
    Table.ToRows(Source), 
    each Text.Combine(
      List.Transform(
        {0 .. Text.Length(_{0}) - 1}, 
        (n) =>
          {"a" .. "z", "a" .. "z"}{
            List.Sum(List.Transform(_, each Character.ToNumber(Text.ToList(Text.Lower(_)){n})))
              - 194
          }
      )
    )
  )
in
  Comb

Power Query solution 2 for Vernam Cipher Encrypt, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  Assignment = List.Zip({{"a" .. "z"}, {0 .. 25}}), 
  ReverseAssignment = List.Transform(Assignment, each List.Reverse(_)), 
  MessageToList = Table.AddColumn(
    Source, 
    "Message To List", 
    each List.ReplaceMatchingItems(Text.ToList([Message]), Assignment)
  ), 
  KeyToList = Table.AddColumn(
    MessageToList, 
    "Key To List", 
    each List.ReplaceMatchingItems(Text.ToList([Key]), Assignment)
  ), 
  AddedEncrpytedMessage = Table.AddColumn(
    KeyToList, 
    "Encrypted Message", 
    each List.Accumulate(
      List.Positions([Message To List]), 
      "", 
      (s, d) =>
        s
          & List.ReplaceMatchingItems(
            {Number.Mod([Message To List]{d} + [Key To List]{d}, 26)}, 
            ReverseAssignment
          ){0}
    )
  ), 
  Solution = AddedEncrpytedMessage[[Encrypted Message]]
in
  Solution

Power Query solution 3 for Vernam Cipher Encrypt, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Replace = List.Zip({{"a" .. "z"}, {0 .. 25}}), 
  Return = Table.AddColumn(
    Source, 
    "Answer", 
    each [
      S1 = Text.ToList([Message]), 
      S2 = Text.ToList([Key]), 
      R1 = List.ReplaceMatchingItems(S1, Replace), 
      R2 = List.ReplaceMatchingItems(S2, Replace), 
      Z  = List.Zip({R1, R2}), 
      T  = List.Transform(Z, (f) => Number.Mod(List.Sum(f), 26)), 
      F  = List.ReplaceMatchingItems(T, List.Transform(Replace, List.Reverse)), 
      C  = Text.Combine(F)
    ][C]
  )
in
  Return

Power Query solution 4 for Vernam Cipher Encrypt, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Data = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.AddColumn(
    Data, 
    "Encrypted", 
    each 
      let
        Zip  = List.Zip({{"a" .. "z"}, {0 .. 25}}), 
        Zip2 = List.Zip({{0 .. 25}, {"a" .. "z"}}), 
        a    = Record.ToList(_), 
        b    = List.Transform(a, each Text.ToList(_)), 
        c    = List.Transform(b, each List.ReplaceMatchingItems(_, Zip)), 
        d    = List.Zip({c{0}, c{1}}), 
        e    = List.Transform(d, each Number.Mod(List.Sum(_), 26)), 
        f    = List.Transform(e, each List.ReplaceMatchingItems({_}, Zip2))
      in
        Text.Combine(List.Combine(f))
  )[[Encrypted]]
in
  Sol

Power Query solution 5 for Vernam Cipher Encrypt, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  lits = List.Zip({{"a" .. "z"}, {0 .. 25}}), 
  lits2 = List.Zip({{0 .. 25}, {"a" .. "z"}}), 
  res = Table.AddColumn(
    Fonte, 
    "Personalizar", 
    each [
      a = List.ReplaceMatchingItems(Text.ToList([Message]), lits), 
      b = List.ReplaceMatchingItems(Text.ToList([Key]), lits), 
      c = List.Transform(List.Zip({a, b}), List.Sum), 
      d = List.Transform(c, (x) => if x > 25 then x - 26 else x), 
      e = List.Zip({b, a}), 
      f = Text.Combine(List.ReplaceMatchingItems(d, lits2))
    ][f]
  )
in
  res

Power Query solution 6 for Vernam Cipher Encrypt, proposed by Matthias Friedmann:

let
  Source = Excel.CurrentWorkbook(){[Name = "Encryption"]}[Content], 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Encrypted", 
    each Text.Combine(
      List.Transform(
        List.Zip(
          {
            List.Transform(Text.ToList([Message]), each List.PositionOf({"a" .. "z"}, _)), 
            List.Transform(Text.ToList([Key]), each List.PositionOf({"a" .. "z"}, _))
          }
        ), 
        each {"a" .. "z"}{if List.Sum(_) > 25 then List.Sum(_) - 26 else List.Sum(_)}
      )
    )
  )
in
  #"Added Custom"

Power Query solution 7 for Vernam Cipher Encrypt, proposed by Udit Chatterjee:

let
  fxVernamCipherEncoder = (message as text, key as text) =>
    let
      messageTextList = Text.ToList(Text.Lower(Text.Trim(message))), 
      keyTextList     = Text.ToList(Text.Lower(Text.Trim(key))), 
      mappingMsgToKey = List.Zip({{"a" .. "z"}, List.Positions({"a" .. "z"})}), 
      mappingKeyToMsg = List.Zip({List.Positions({"a" .. "z"}), {"a" .. "z"}}), 
      mappedMsg       = List.ReplaceMatchingItems(messageTextList, mappingMsgToKey), 
      mappedKey       = List.ReplaceMatchingItems(keyTextList, mappingMsgToKey), 
      addedMsgKey     = List.Transform(List.Zip({mappedMsg, mappedKey}), each List.Sum(_)), 
      encryptedKeys   = List.Transform(addedMsgKey, each if _ > 25 then _ - 26 else _), 
      encyptedMsgList = List.ReplaceMatchingItems(encryptedKeys, mappingKeyToMsg), 
      encryptedText   = Text.Combine(encyptedMsgList)
    in
      encryptedText, 
  Source = xlProblem162, 
  addFunctionCol = Table.AddColumn(
    Source, 
    "Encrypted Message", 
    each fxVernamCipherEncoder([Message], [Key]), 
    type text
  ), 
  keepRequiredCol = Table.SelectColumns(addFunctionCol, {"Encrypted Message"})
in
  keepRequiredCol

Solving the challenge of Vernam Cipher Encrypt with Excel

Excel solution 1 for Vernam Cipher Encrypt, proposed by Bo Rydobon 🇹🇭:

=BYROW(A2:B7,LAMBDA(a,CONCAT(CHAR(MOD(MMULT(CODE(MID(a,SEQUENCE(MIN(LEN(a))),1))-97,{1;1}),26)+97))))

=BYROW(A2:B7,LAMBDA(a,CONCAT(CHAR(MOD(MMULT(CODE(MID(LOWER(a),SEQUENCE(MIN(LEN(a))),1))-97,{1;1}),26)+97))))

Excel solution 2 for Vernam Cipher Encrypt, proposed by Rick Rothstein:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(f,LAMBDA(x,CODE(MID(x,SEQUENCE(LEN(x)),1))-97),CONCAT(CHAR(97+MOD(f(a)+f(b),26))))))

Excel solution 3 for Vernam Cipher Encrypt, proposed by John V.:

=BYROW(A2:B7,LAMBDA(r,CONCAT(CHAR(97+MOD(MMULT(CODE(MID(r,SEQUENCE(MAX(LEN(r))),1))-97,{1;1}),26)))))

Excel solution 4 for Vernam Cipher Encrypt, proposed by محمد حلمي:

=MAP(A2:A7,B2:B7,LAMBDA(C,V,LET(
H,SEQUENCE(26,,0),
R,CHAR(ROW(97:122)),
F,LAMBDA(X,
XLOOKUP(MID(X,SEQUENCE(LEN(X)),1),R,H)),
CONCAT(XLOOKUP(MOD(F(C)+F(V),26),H,R)))))

Excel solution 5 for Vernam Cipher Encrypt, proposed by 🇰🇷 Taeyong Shin:

=LET(
 num, SEQUENCE(26) - 1,
 c, CHAR(num + 97),
 LEFT(REDUCE("", SEQUENCE(MAX(LEN(A2:B7))), LAMBDA(a,n,
 LET(
 t, XLOOKUP(MID(A2:A7, n, 1), c, num, 0) +
 XLOOKUP(MID(B2:B7, n, 1), c, num, 0),
 a & XLOOKUP(IF(t > 25, t - 26, t), num, c)
 )
 )), LEN(A2:A7))
)

Excel solution 6 for Vernam Cipher Encrypt, proposed by Kris Jaganah:

=MAP(A2:A7,B2:B7,LAMBDA(x,y,LET(a,BYROW(HSTACK(CODE(MID(x,SEQUENCE(LEN(x)),1))-97,CODE(MID(y,SEQUENCE(LEN(y)),1))-97),LAMBDA(w,SUM(w))),CONCAT(CHAR(IF(a>25,a-26,a)+97)))))

Excel solution 7 for Vernam Cipher Encrypt, proposed by Timothée BLIOT:

=LET(A,A2:A7, B,B2:B7, MAP(A,B, LAMBDA(a,b, CONCAT( MAP(SEQUENCE(LEN(a)), LAMBDA(x, LET( V, CODE(MID(a,x,1))+CODE(MID(b,x,1))-194, CHAR(IF(V>25,V-26,V)+97))))))) )

Excel solution 8 for Vernam Cipher Encrypt, proposed by Hussein SATOUR:

=MAP(A2:A7, B2:B7, LAMBDA(x,y, LET(a, CODE(MID(x, SEQUENCE(LEN(x)),1)) + CODE(MID(y, SEQUENCE(LEN(y)),1)) - 97*2, CONCAT(CHAR(97 + IF(a>25, a-26, a))))))

Excel solution 9 for Vernam Cipher Encrypt, proposed by Oscar Mendez Roca Farell:

=REDUCE("Encryoted Message", A2:A7&"|"&B2:B7, LAMBDA(i, x, LET(_a, TEXTSPLIT(x,"|"),VSTACK(i, CONCAT( TOROW( BYROW( CODE( MID(_a, SEQUENCE(15),1))-97, LAMBDA(r, CHAR( MOD( SUM(r), 26)+97))),2))))))

Excel solution 10 for Vernam Cipher Encrypt, proposed by Sunny Baggu:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,
LET(_let,CHAR(SEQUENCE(26,,97)),
_no,SEQUENCE(26)-1,
_e1,LAMBDA(x,REDUCE(MID(x,SEQUENCE(LEN(x)),1),SEQUENCE(26),LAMBDA(a,v,SUBSTITUTE(a,INDEX(_let,v,1),INDEX(_no,v,1))))),
_sum,_e1(a)+_e1(b),
CONCAT(XLOOKUP(IF(_sum>25,_sum-26,_sum),_no,_let)))))

Excel solution 11 for Vernam Cipher Encrypt, proposed by Sunny Baggu:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,
LET(_lettr,CHAR(SEQUENCE(26,,97)),_num,SEQUENCE(26)-1,
_e1,LAMBDA(x,MID(x,SEQUENCE(LEN(x)),1)),
_e2,LAMBDA(y,XLOOKUP(y,_lettr,_num)),
_e3,LAMBDA(y,XLOOKUP(y,_num,_lettr)),
_mes,_e1(a),
_key,_e1(b),
_sum,_e2(_mes)+_e2(_key),
CONCAT(_e3(IF(_sum>25,_sum-26,_sum))))))

Excel solution 12 for Vernam Cipher Encrypt, proposed by Md. Zohurul Islam:

=LET(u,A2:A7,v,B2:B7,
w,CHAR(SEQUENCE(26,,97)),n,SEQUENCE(26,,0),
f,LAMBDA(p,XLOOKUP(MID(p,SEQUENCE(LEN(p)),1),w,n)),
z,MAP(u,v,LAMBDA(x,y,LET(
 a,BYROW(HSTACK(f(x),f(y)),SUM),
 b,IF(a>25,a-26,a),
 d,CONCAT(XLOOKUP(b,n,w)),
 d))),
z)

Excel solution 13 for Vernam Cipher Encrypt, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:

=MAP(A2:A7;B2:B7;LAMBDA(x;y;LET(a;XLOOKUP(MID(x;SEQUENCE(LEN(x));1);CHAR(ROW($65:$90));VSTACK(0;ROW($A$1:$A$25)));s;XLOOKUP(MID(y;SEQUENCE(LEN(y));1);CHAR(ROW($65:$90));VSTACK(0;ROW($A$1:$A$25)));TEXTJOIN(;;LOWER(XLOOKUP(IF(a+s>25;a+s-26;a+s);VSTACK(0;ROW($A$1:$A$25));CHAR(ROW($65:$90))))))))

Excel solution 14 for Vernam Cipher Encrypt, proposed by Guillermo Arroyo:

=MAP(A2:A7;B2:B7;LAMBDA(a;b;(LET(g;LAMBDA(u;CODE(MID(u;SEQUENCE(LEN(u));1))-97);p;g(a)+g(b);CONCAT(CHAR(IF(p>25;p-26;p)+97))))))

Excel solution 15 for Vernam Cipher Encrypt, proposed by Daniel Garzia:

=MAP(A2:A7,B2:B7,LAMBDA(a,b,LET(fx,LAMBDA(x,CODE(MID(x,SEQUENCE(LEN(x)),1))),
_c,fx(a)+fx(b)-194,
CONCAT(XLOOKUP(IF(_c>25,_c-26,_c),SEQUENCE(26,,0),CHAR(SEQUENCE(26,,97)))))))

Excel solution 16 for Vernam Cipher Encrypt, proposed by Gabriel Raigosa:

=MAP(A2:A7,B2:B7,LAMBDA(m,k,CONCAT(LET(az,CARACTER(SECUENCIA(26,,97)),dif,COINCIDIR(EXTRAE(m,SECUENCIA(,LARGO(m)),1),az)-1+COINCIDIR(EXTRAE(k,SECUENCIA(,LARGO(k)),1),az)-1,INDICE(az,SI(dif>25,dif-26,dif)+1))))) 

▶️EN:
 =MAP(A2:A7,B2:B7,LAMBDA(m,k,CONCAT(LET(az,CHAR(SEQUENCE(26,,97)),dif,MATCH(MID(m,SEQUENCE(,LEN(m)),1),az)-1+MATCH(MID(k,SEQUENCE(,LEN(k)),1),az)-1,INDEX(az,IF(dif>25,dif-26,dif)+1)))))

Excel solution 17 for Vernam Cipher Encrypt, proposed by Ricardo Alexis Domínguez Hernández:

=MAP(A2:A7,B2:B7,
LAMBDA(A,B,CONCAT(
LET(Alphabet,CHAR(SEQUENCE(26,,97)),
Numbers,SEQUENCE(26,,0),
XLOOKUP(LET(x,
XLOOKUP(MID(A,SEQUENCE(,LEN(A)),1),Alphabet,Numbers)
+XLOOKUP(MID(B,SEQUENCE(,LEN(B)),1),Alphabet,Numbers),
IF(x>25,x-26,x)),
Numbers,Alphabet)))))

Excel solution 18 for Vernam Cipher Encrypt, proposed by roberto mensa:

=LET(s,"abcdefghijklmnopqrstuvwxyz",
em,C2:C7,
k,B2:B7,
n,99,
y,SEQUENCE(,n),
TRIM(MID(CONCAT(IFERROR(MID(s&s,CODE(MID(em,y,1))-CODE(MID(k,y,1))+27,1)," ")),SEQUENCE(ROWS(k),,1,n),n)))

Excel solution 19 for Vernam Cipher Encrypt, proposed by roberto mensa:

=LET(s,"abcdefghijklmnopqrstuvwxyz",
em,A2:A7,
k,B2:B7,
n,99,
y,SEQUENCE(,n),
TRIM(MID(CONCAT(IFERROR(MID(s&s,CODE(MID(em,y,1))+CODE(MID(k,y,1))-96*2-1,1)," ")),SEQUENCE(ROWS(k),,1,n),n)))

&&&

Solving the challenge of Vernam Cipher Encrypt with Power Query

Solving the challenge of Vernam Cipher Encrypt with Excel

Leave a Reply Cancel reply