Ciphertext Analysis with Excel?
 

Hey guys, I have written a quick spreadsheet that does a character
frequency analysis on a given ciphertext string.

I have a question on improving the functionality:

Q: Is there a way to print out the ten most frequent bigrams (two
letter combinations) and trigrams (three letter combinations) with a
formula or VBscript?

I have attached the file for your review.

Thank you in advance for your assistance.

-JJ


+-------------------------------------------------------------------+
|Filename: Cryptanalysis Worksheet.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5083 |
+-------------------------------------------------------------------+

--
jjunginger
------------------------------------------------------------------------
jjunginger's Profile: http://www.excelforum.com/member.php...o&userid=36703
View this thread: http://www.excelforum.com/showthread...hreadid=564366

Ciphertext Analysis with Excel?
 

Write the combinations to a page with their frequency, sort on frequency and
then print the top 10 or use an autofilter on the new data to show the top 10
and print that.

--
Regards,
Tom Ogilvy


"jjunginger" wrote:


Hey guys, I have written a quick spreadsheet that does a character
frequency analysis on a given ciphertext string.

I have a question on improving the functionality:

Q: Is there a way to print out the ten most frequent bigrams (two
letter combinations) and trigrams (three letter combinations) with a
formula or VBscript?

I have attached the file for your review.

Thank you in advance for your assistance.

-JJ


+-------------------------------------------------------------------+
|Filename: Cryptanalysis Worksheet.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5083 |
+-------------------------------------------------------------------+

--
jjunginger
------------------------------------------------------------------------
jjunginger's Profile: http://www.excelforum.com/member.php...o&userid=36703
View this thread: http://www.excelforum.com/showthread...hreadid=564366

Ciphertext Analysis with Excel?
 

Thanks for the reply, Tom. How do I get all of the unique combinations
to a page, so I can sort them? Could you give me a snippet of
code/script or walk me through how to get that done?

Thanks again!

-Jeremy


--
jjunginger
------------------------------------------------------------------------
jjunginger's Profile: http://www.excelforum.com/member.php...o&userid=36703
View this thread: http://www.excelforum.com/showthread...hreadid=564366

Ciphertext Analysis with Excel?
 

Thanks for the reply, Tom. How do I get all of the unique combinations
to a page, so I can sort them? Could you give me a snippet of
code/script or walk me through how to get that done?

Thanks again!

-Jeremy


--
jjunginger
------------------------------------------------------------------------
jjunginger's Profile: http://www.excelforum.com/member.php...o&userid=36703
View this thread: http://www.excelforum.com/showthread...hreadid=564366

Ciphertext Analysis with Excel?
 

Sub ABC()
Dim i As Long, j As Long
Dim rw As Long, s As String
Dim s1 As String, k As Long
Dim kk As Long
s = Range("A2")
rw = 31
For i = 1 To 26
For j = 1 To 26
s1 = Chr(i + 64) & Chr(j + 64)
Cells(rw, 1) = s1

For kk = 1 To Len(s) - 1
If Mid(s, kk, 2) = s1 Then
Cells(rw, 2) = Cells(rw, 2) + 1
End If
Next
rw = rw + 1
Next j
Next i
Range("A31").Resize(rw - 31, 2).Sort Key1:=Range("B31"), _
Order1:=xlDescending, header:=xlNo
Range("A41:A706").EntireRow.Delete

rw = 43
For i = 1 To 26
For j = 1 To 26
For k = 1 To 26
s1 = Chr(i + 64) & Chr(j + 64) & Chr(k + 64)
Cells(rw, 1) = s1

For kk = 1 To Len(s) - 1
If Mid(s, kk, 3) = s1 Then
Cells(rw, 2) = Cells(rw, 2) + 1
End If
Next
rw = rw + 1
Next k
Next j
Next i
Range("A43").Resize(rw - 43, 2).Sort Key1:=Range("B43"), _
Order1:=xlDescending, header:=xlNo
Range("A53:A65536").EntireRow.Delete
End Sub

--
Regards,
Tom Ogilvy


"jjunginger" wrote:


Thanks for the reply, Tom. How do I get all of the unique combinations
to a page, so I can sort them? Could you give me a snippet of
code/script or walk me through how to get that done?

Thanks again!

-Jeremy


--
jjunginger
------------------------------------------------------------------------
jjunginger's Profile: http://www.excelforum.com/member.php...o&userid=36703
View this thread: http://www.excelforum.com/showthread...hreadid=564366

Ciphertext Analysis with Excel?
 

Tom

elegant. you missed the fact that "a2" has mixed case!

change s= Range("A2")
to s= UCase(Range("A2")

and you'll notice different results.


OP,

what are you doing in Column B and C??
those formulas are "hardcoded" and prone to errors.

I've shortened the formula in B by replacing the nested substitute to
upper

in b4:
=LEN($A$2)-LEN(SUBSTITUTE(UPPER($A$2),$A4,""))
in c4:
=$B4/LEN($A$2)*100

then copy down.

pls study Excel HELP
about Absolute and Relative references!

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote in