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. 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. 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. 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. And another mistake…
    Unused:
    =IF(Given=””,SMALL((1-COUNTIF(Given,Alphabet))*Five_by_five,Five_by_five),0)

  5. 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. 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. 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.

  10. 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

  11. 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))

  12. 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”.

  13. 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”}), “”))))

  14. 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

  15. 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

  16. 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

Leave a Reply

Your email address will not be published. Required fields are marked *