# Encryption Concepts

I’m up to about March 15th in my backlog of emails. Tonight I read two emails about encryption: one from Wolfgang and one from Keith. I’m waiting to hear back from Wolfgang on what I think may be an interesting idea, but Kieth has a challenge that I know you’ll like. He explains it very well, so I’ll shut up and let him do the talking:

I have a 5 x 5 range that is empty, let’s say A1:E5
I also have a similar 5 x 5 range (assume G1:K5) with formulas in each cell
such that each cell displays a separate letter of the alphabet (skipping J),
as below:

A B C D E
F G H I K
L M N O P
Q R S T U
V W X Y Z

Now for the tough part… I want to be able to type letters into the first
range of cells (left to right, beginning on line one) and have those letters
displayed in the corresponding cells of the second range, HOWEVER!!!!, I
want the remaining letters of the alphabet to be displaced accordingly so
that no letter is repeated… here’s an example for the word “BARK”:

In the first range, I type:

B A R K

The second range displays:

B A R K C
D E F G H
I L M N O
P Q S T U
V W X Y Z

Notice that the letters for bark have simply been moved to the top row and
the remaining letters have been shuffled accordingly. All the remaining
letters of the alphabet remain in alphabetical order. Obviously, the user
would need to ignore repeated letters in his word, so the word CHEATERS
would be typed as:

C H E A T
R S

(Not typing the second E) and the result would be:

C H E A T
R S B D F
G I K L M
N O P Q U
V W X Y Z

For the life of me, I cannot figure out what formula(s) will give this
result in the second range of cells. In case you’re wondering, this little
matrix is used in the field of cryptology (enciphering and deciphering
messages). Specifically, the enciphering method that uses this matrix is
called the “Playfair” method (and also the “Double Playfair” method) and was
used by both the Axis and the Allies in WWII. Any solutions (or partial
solutions) you could provide would be greatly appreciated.

I have a solution that uses two helper ranges. I don’t know if helper ranges are allowed, though. I’ll post mine tomorrow.

Posted in Uncategorized

## 18 thoughts on “Encryption Concepts”

1. Boomer says:

Seems to me the easiest way to do this is via a UDF using a Collection containing all the letters in G1:K5 in order.

Delete the input letters from the Collection and add them to the array. You can error trap here to handle repeated letters – if you can’t delete a letter from the Collection it is already gone so must be a repeat.

Then add the remaining letters from the Collection to the end of the array. You can then display this via a 5×5 array as you wish.

2. Helen says:

Nice challenge, I liked this!

I don’t think I can post an attachment here, so this might a tad confusing… but it works for me. :)
This uses no helper ranges, only a couple of named formulas.

Named ranges / formulas:
Five_by_five
=ROW(INDIRECT(“A1:E5?))*5+COLUMN(INDIRECT(“A1:E5?))-5
(a 5×5 matrix of the numbers 1 to 25)

Alphabet_codes
=Five_by_five+IF(Five_by_five””,0,SMALL((1-COUNTIF(Given,Alphabet))*Five_by_five,Five_by_five))
(A 5×5 matrix that has 0 for already-used letters and the offset for all unused letters, sorted in ascending order. I.e. if you have BARK as the keyword, the first 4 positions will be 0,0,0,0, then 3 for C as the first unused letter, all the way up to 25 for Z.)

And finally the formula itself:
=IF(Given””,Given,INDEX(Alphabet,INT((Unused-1)/5)+1,MOD(Unused-1,5)+1))
(Uses the given letters first, then gets the list of unused letters, and uses the offsets there to get the appropriate letter of the alphabet.)

3. Helen says:

Hmm… the site software ate half my post! It doesn’t seem to like less-than and greater-than signs much. I’ll try again.

Named ranges / formulas:
Five_by_five
=ROW(INDIRECT(“A1:E5?))*5+COLUMN(INDIRECT(“A1:E5?))-5
(a 5×5 matrix of the numbers 1 to 25)

Alphabet_codes
=Five_by_five+IF(Five_by_five < 10,0,1)+64
(the numbers 65 to 90, excluding 74, in a 5×5 matrix – i.e. the ASCII codes for all letters except J)

Alphabet
=CHAR(Alphabet_codes)
(obvious)

Given
=Sheet1!\$A\$1:\$E\$5
(the range where you enter the keyword such as “BARK”)

Unused
=IF(Given=””,SMALL((1-COUNTIF(Given,Alphabet),0)*Five_by_five,Five_by_five))
(A 5×5 matrix that has 0 for already-used letters and the offset for all unused letters, sorted in ascending order. I.e. if you have BARK as the keyword, the first 4 positions will be 0,0,0,0, then 3 for C as the first unused letter, all the way up to 25 for Z.)

And finally the formula itself:
=IF(Given=””,INDEX(Alphabet,INT((Unused-1)/5)+1,MOD(Unused-1,5)+1),Given)
(Uses the given letters first, then gets the list of unused letters, and uses the offsets there to get the appropriate letter of the alphabet.)

4. Helen says:

And another mistake…
Unused:
=IF(Given=””,SMALL((1-COUNTIF(Given,Alphabet))*Five_by_five,Five_by_five),0)

5. Jason Morin says:

Assuming G8:K12 is the data entry range (e.g., in the first example B-A-R-K would be entered into G8:J8 and the remaining cells would be empty), place this in A1, press ctrl + shift + enter, and copy across to E1 and down to row 5:

=IF(G8?”,G8,CHAR(SMALL(IF(COUNTIF(\$G\$8:\$K\$12,\$G\$1:\$K\$5)=0,CODE(\$G\$1:\$K\$5)),IF(ROW()=1,COLUMN(),ROW()*5+COLUMN()-5)-COUNTA(\$G\$8:\$K\$12))))

Jason

6. Jason Morin says:

Ugh. That first part of the formula should be:

=IF(G8 does not equal “”,G8,…

I know absolutely nothing about web programming, Dick. How do I get it to recognize the “less than” and “greater than” symbols as part of my comment?

Thanks.

7. Less than and greater than enclose html tags and that’s how they’re read. You can use the escape characters, such as ampersand+ell+tee+semi-colon for a less than sign.

I wonder if you can enclose it code tags:

If 1

the above line was typed as

&lt;code&gt;If 1 &lt; 0 Then&lt;/code&gt;

8. I’m not sure whether the answers so far include the bit about “ignoring J” and I think in this case, as simple UDF is probably going to be easier to understand than a complex formula, so here’s mine (entered as a 5×5 array formula), where:

GT = Greater Than
NE = Not Equal To

Public Function Encrypt(rngSource As Range) As Variant

Dim vaResult As Variant
Dim colUnused As New Collection
Dim i As Integer, j As Integer

‘A collection of A-Z, ignoring J
For i = 65 To 89
colUnused.Add Chr\$(i – (i GT 73)), Chr\$(i – (i GT 73))
Next

‘The original array
vaResult = rngSource.Value

‘Loop through rows and cols
For i = 1 To 5
For j = 1 To 5
If vaResult(i, j) NE “” Then
‘Remove the prefilled stuff as we encounter it
colUnused.Remove vaResult(i, j)
Else
‘Populate the array with the next unused char
vaResult(i, j) = colUnused(1)
colUnused.Remove 1
End If
Next
Next

Encrypt = vaResult

End Function

9. Nope, that doesn’t work. I suppose I’ll have to get off my lazy *** and fix this so you guys can post code in the comments.

10. Helen says:

Jason, in your formula, what does \$G\$1:\$K\$5 contain?

11. Michael Markov says:

Here is a simple UDF to create the cypher alphabet:

Function cypheralphabet(key As String) As String
Dim keylen As Long, i As Long, j As Long
Dim ISUNIQUE As Boolean
Dim cleankey As String
key = key & “ABCDEFGHIKLMNOPQRSTUVWXYZ”
keylen = Len(key)
cleankey = Left(key, 1)
For i = 2 To keylen
ISUNIQUE = True
For j = i – 1 To 1 Step -1
If Mid(key, j, 1) = Mid(key, i, 1) Then
ISUNIQUE = False
End If
Next j
If ISUNIQUE Then
cleankey = cleankey & Mid(key, i, 1)
End If
Next i
cypheralphabet = cleankey
End Function

putting this alphabet into a 5×5 matrix should be easy enough.

The idea is to hopelessly scramble the cypher text letters to make it more difficult for analysts to recover the original text.. so it might be worth something to load the table following non-standard routes.

12. David Wasserman says:

Here is my stab at this brainteaser:

NE = Not Equal to

Function Sifer(Istr As String) As String
Dim coll As New Collection, cyfer As String
Dim i As Integer

On Error Resume Next
For i = 1 To Len(Istr)
If UCase(Mid(Istr, i, 1)) NE “J” Then _
coll.Add UCase(Mid(Istr, i, 1)), UCase(Mid(Istr, i, 1))
Next i
For i = 1 To 26
If i NE 10 Then coll.Add Chr(64 + i), Chr(64 + i)
Next i
On Error GoTo 0

For Each c In coll
cyfer = cyfer & c
Next c

Sifer = Left(cyfer, 5) & vbLf & Mid(cyfer, 6, 5) & vbLf & _
Mid(cyfer, 11, 5) & vbLf & Mid(cyfer, 16, 5) & vbLf & _
Mid(cyfer, 21, 5)
End Function

13. Prasanna says:

Keep the same ranges (A1:E5) as input range and G1:K5 as the answer range.

1. Starting from A10 downwards type the alphabet. Copy same to C10 downwards. Starting B10 have numbers 1 to 25 downwards.

It will look like:

a 1 a
b 2 b
c 3 c

2. Create a working array G10: K14; G10 caontaining formula
=IF(ISERROR(FIND(A10,CONCATENATE(\$A\$1,\$B\$1,\$C\$1,\$D\$1,\$E\$1,\$A\$2,\$B\$2,\$C\$2,\$D\$2,\$E\$2,\$A\$3,\$B\$3,\$C\$3,\$D\$3,\$E\$3,\$A\$4,\$B\$4,\$C\$4,\$D\$4,\$E\$4,\$A\$5,\$B\$5,\$C\$5,\$D\$5,\$E\$5))),VLOOKUP(A10,\$A\$10:\$B\$34,2,0),0)

3. Answers will be in G1:K5; with G1 containing
=IF(A1>0,A1,VLOOKUP(LARGE(\$G\$10:\$K\$14,25),\$B\$10:\$C\$34,2))

14. Prasanna says:

Sorry I missed an explanation.

In my last formula when copying the 25 should be in decending order 24,23,22,21,……….

So H1 will have
=IF(B1>0,B1,VLOOKUP(LARGE(\$G\$10:\$K\$14,24),\$B\$10:\$C\$34,2))

Obviously the alphabet range excludes “J”.

15. I thought I would have a go. I’m pretty weak at these – especially when it’s not a single column/row.
Complicated by a missing letter J!

I’ve put a different formula into each cell of G1:K5 – I just dont know enough to go the single array formula – no helpers though…

I’m not sure how the comment formatter is going to interpret the formula. I’ve saved a textfile to my website just in case:
DDOE_EncryptionConcepts.txt

G1: =IF(A1=””,”A”, A1)
H1: =IF(B1<>””, B1, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
I1: =IF(C1<>””, C1, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
J1: =IF(D1<>””, D1, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
K1: =IF(E1<>””, E1, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
G2: =IF(A2<>””, A2, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
H2: =IF(B2<>””, B2, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
I2: =IF(C2<>””, C2, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
J2: =IF(D2<>””, D2, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
K2: =IF(E2<>””, E2, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
G3: =IF(A3<>””, A3, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
H3: =IF(B3<>””, B3, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
I3: =IF(C3<>””, C3, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3, H3))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
J3: =IF(D3<>””, D3, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3, H3, I3))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
K3: =IF(E3<>””, E3, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3, H3, I3, J3))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
G4: =IF(A4<>””, A4, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3, H3, I3, J3, K3))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
H4: =IF(B4<>””, B4, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3, H3, I3, J3, K3, G4))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
I4: =IF(C4<>””, C4, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3, H3, I3, J3, K3, G4, H4))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
J4: =IF(D4<>””, D4, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3, H3, I3, J3, K3, G4, H4, I4))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
K4: =IF(E4<>””, E4, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3, H3, I3, J3, K3, G4, H4, I4, J4))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
G5: =IF(A5<>””, A5, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3, H3, I3, J3, K3, G4, H4, I4, J4, K4))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
H5: =IF(B5<>””, B5, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3, H3, I3, J3, K3, G4, H4, I4, J4, K4, G5))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
I5: =IF(C5<>””, C5, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3, H3, I3, J3, K3, G4, H4, I4, J4, K4, G5, H5))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
J5: =IF(D5<>””, D5, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3, H3, I3, J3, K3, G4, H4, I4, J4, K4, G5, H5, I5))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))
K5: =IF(E5<>””, E5, CHAR(MIN(IF(ISERR(FIND({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}, CONCATENATE(G1, H1, I1, J1, K1, G2, H2, I2, J2, K2, G3, H3, I3, J3, K3, G4, H4, I4, J4, K4, G5, H5, I5, J5))), CODE({“A”,”B”,”C”,”D”,”E”,”F”,”G”,”H”,”I”,”K”,”L”,”M”,”N”,”O”,”P”,”Q”,”R”,”S”,”T”,”U”,”V”,”W”,”X”,”Y”,”Z”}), “”))))

16. Michael Markov says:

Here is a revised UDF to load the grid. I have modified the UDF to ignore J in in the keyword, and to convert lowercase characters in the keyword to uppercase, eliminating a number of potential problems
Finally, this function retuns a 5×5 array, as desired.

Function cypheralph(key As String) As Variant
Dim keylen As Long, i As Long, j As Long
Dim ISUNIQUE As Boolean
Dim cleankey As String
Dim arcyph(4, 4) As String
key = UCase(key) & “ABCDEFGHIKLMNOPQRSTUVWXYZ”
keylen = Len(key)
If Left(key, 1) “J” Then
cleankey = Left(key, 1)
Else
cleankey = “”
End If
For i = 2 To keylen
ISUNIQUE = True
For j = i – 1 To 1 Step -1
If Mid(key, j, 1) = Mid(key, i, 1) Then
ISUNIQUE = False
End If
Next j
If ISUNIQUE Then
If Mid(key, i, 1) “J” Then
cleankey = cleankey & Mid(key, i, 1)
End If
End If
Next i
For i = 0 To 4
For j = 0 To 4
arcyph(i, j) = Mid(cleankey, i * 5 + j + 1, 1)
Next j
Next i
cypheralph = arcyph
End Function

17. Keith says:

I’m Keith. I’m the guy who sent the original email to Dick. Sorry for the late response, but I was outta pocket for two weeks because of the arrival of my daughter (May 24th, 9 lbs-1 oz., healthy).

Man, I’m blown away by your responses. You guys and gals rock! I was totally stumped by that one and I always find it interesting to see the different approaches everyone takes. My nickname on some forums is excelguru, but I may have to change that!

Regards,

Keith

18. Mar says:

Hey I was wondering if anyone could help
I am trying to tally a number of different responses to questions in a speadsheet where the answers are for example
a,b, c, d, e, etc.
Now i want to tally down a column for all (reponses)values of “a” and then in a seperate cell for all (reponses)values of “b” etc
Would i use an =sum or =IF or a combination.
Note; I am only a basic excel user and would appreciate any help.
Regards
Mar

Posting code? Use <pre> tags for VBA and <code> tags for inline.