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 ZNow 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 ZNotice 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 ZFor 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.

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.

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

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

And another mistake…

Unused:

=IF(Given=””,SMALL((1-COUNTIF(Given,Alphabet))*Five_by_five,Five_by_five),0)

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

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.

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:

the above line was typed as

<code>If 1 < 0 Then</code>

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

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.

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

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.

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

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

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

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

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

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

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