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. 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. Jason, in your formula, what does $G$1:$K$5 contain?

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