Microsoft Excel

Sort by date Sort by votes
Ruddles said:
I make it 26^6 or 308,915,776.

My laptop is telling me it'll take about five and a half hours to generate them to a worksheet.
Click to expand...
Okay, I have thought about it some more and you are right... 308,915,776 it is. My mistake in selecting permutations was that I forgot the letters can repeat.
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
shg said:
Rather than list 308M 6-letter passwords from AAAAAA to ZZZZZZ (what a boring parade!), how about a function?

Code: 
Function Nick(ByVal d As Double, _
              Optional iLen As Long = 0) As String
    Dim avs As Variant
 
    avs = Split("A B C D E F G H I J K L M N O P Q R S T U V W X Y Z", " ")
 
    Do
        Nick = avs(d - Int(d / 26) * 26) & Nick
        d = Int(d / 26)
    Loop While d > 0#
 
    If Len(Nick) < iLen Then Nick = String(iLen - Len(Nick), avs(0)) & Nick
End Function
Click to expand...
Since Nick will have to maintain a file of passwords or, in your case, numbers that were used for the first argument, so he can test or check to make sure the generated password has not been used before (as well as test to make sure a claimed password is legitimate), I think he might as well just store the passwords and randomly generate a potential password when needed, check it and, if it is a repeat, geneate another one. With over 308 million possibilities, the chance of duplicating a password once or twice is pretty remote, so the Do..Loop he uses, it will not iterate too many times. Here is a function he can use to randomly generate a password...

Code: 
Function Nick2()
  Dim X As Long
  For X = 1 To 6
    Nick2 = Nick2 & Chr(Int(26 * Rnd + 65))
  Next
End Function
 
Upvote 0
Hey Ruddles,
The combination would defiantly be drafted on an Excel Worksheet; if you can administer some formulas I’ll give them a shoot and give feedback how they work.
 
Upvote 0
Hey Ruddles,
The combination would defiantly be drafted on an Excel Worksheet; if you can administer some formulas I’ll give them a shoot and give feedback how they work.
Sir Rick,
I’m currently using a similar method of the one mention; by simply looking for duplicates in the sheet. Indeed it is a simplified method; however I am faced with the challenge of Finding Actual Words in a timely manner and a few more.
I must thank you experts for your time and cooperation once again.
 
Upvote 0
Nick Vaughn said:
Hey Ruddles,
The combination would defiantly be drafted on an Excel Worksheet; if you can administer some formulas I’ll give them a shoot and give feedback how they work.
Click to expand...
First of all try the 4-character version of the code. Watch Excel's status bar for progress information. This takes approx 45 seconds to go through the 456,976 permutations.
Code: 
[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Sub CombineAtoZx4()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim ws As Worksheet
  Dim n1 As Integer, n2 As Integer, n3 As Integer, n4 As Integer
  Dim iRow As Long, iColumn As Long, iCombinations As Long
  Dim dStart As Date
  
  Application.StatusBar = ""
  Application.ScreenUpdating = False
  
  dStart = Now()
  
  Set ws = ThisWorkbook.Sheets(1)
  ws.UsedRange.ClearContents
  
  iCombinations = 0
  iRow = 0
  iColumn = 1[/FONT]

[FONT=Fixedsys]  For n1 = Asc("A") To Asc("Z")
    For n2 = Asc("A") To Asc("Z")
      For n3 = Asc("A") To Asc("Z")
        For n4 = Asc("A") To Asc("Z")
          iRow = iRow + 1
          If iRow > 100000 Then
            ws.Columns(iColumn).EntireColumn.AutoFit
            iColumn = iColumn + 1
            iRow = 1
            Application.StatusBar = Format(iCombinations, "#,###") & " combinations found in " _
                & Format(Now() - dStart, "hh:nn:ss")
            Application.ScreenUpdating = False
            Application.ScreenUpdating = True
         End If
          ws.Cells(iRow, iColumn) = Chr(n1) & Chr(n2) & Chr(n3) & Chr(n4)
          iCombinations = iCombinations + 1
        Next n4
      Next n3
    Next n2
  Next n1
  
  ws.Columns(iColumn).EntireColumn.AutoFit
  
  Application.ScreenUpdating = True
  
  MsgBox Format(iCombinations, "#,###") & " combinations found" & Space(10) & vbCrLf & vbCrLf _
       & "Run time: " & Format(Now() - dStart, "hh:nn:ss"), vbOKOnly + vbInformation[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]End Sub
[/FONT]
The 5-character version (11,881,376 permutations) takes approx 21 minutes.
Code: 
[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]Sub CombineAtoZx5()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim ws As Worksheet
  Dim n1 As Integer, n2 As Integer, n3 As Integer, n4 As Integer, n5 As Integer
  Dim iRow As Long, iColumn As Long, iCombinations As Long
  Dim dStart As Date
  
  Application.StatusBar = ""
  Application.ScreenUpdating = False
  
  dStart = Now()
  
  Set ws = ThisWorkbook.Sheets(1)
  ws.UsedRange.ClearContents
  
  iCombinations = 0
  iRow = 0
  iColumn = 1[/FONT]
[FONT=Fixedsys]
  For n1 = Asc("A") To Asc("Z")
    For n2 = Asc("A") To Asc("Z")
      For n3 = Asc("A") To Asc("Z")
        For n4 = Asc("A") To Asc("Z")
          For n5 = Asc("A") To Asc("Z")
            iRow = iRow + 1
            If iRow > 250000 Then
              ws.Columns(iColumn).EntireColumn.AutoFit
              iColumn = iColumn + 1
              iRow = 1
              Application.StatusBar = Format(iCombinations, "#,###") & " combinations found in " _
                  & Format(Now() - dStart, "hh:nn:ss")
              Application.ScreenUpdating = False
              Application.ScreenUpdating = True
            End If
            ws.Cells(iRow, iColumn) = Chr(n1) & Chr(n2) & Chr(n3) & Chr(n4) & Chr(n5)
            iCombinations = iCombinations + 1
          Next n5
        Next n4
      Next n3
    Next n2
  Next n1
  
  ws.Columns(iColumn).EntireColumn.AutoFit
  
  Application.ScreenUpdating = True
  
  MsgBox Format(iCombinations, "#,###") & " combinations found" & Space(10) & vbCrLf & vbCrLf _
       & "Run time: " & Format(Now() - dStart, "hh:nn:ss"), vbOKOnly + vbInformation[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]End Sub[/FONT]
The 6-character version which will presumably take approx 26 times as long as the 5-character version:-
Code: 
[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]Sub CombineAtoZx6()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim ws As Worksheet
  Dim n1 As Integer, n2 As Integer, n3 As Integer, n4 As Integer, n5 As Integer, n6 As Integer
  Dim iRow As Long, iColumn As Long, iCombinations As Long
  Dim dStart As Date
  
  Application.StatusBar = ""
  Application.ScreenUpdating = False
  
  dStart = Now()
  
  Set ws = ThisWorkbook.Sheets(1)
  ws.UsedRange.ClearContents
  
  iCombinations = 0
  iRow = 0
  iColumn = 1[/FONT]
[FONT=Fixedsys]
  For n1 = Asc("A") To Asc("Z")
    For n2 = Asc("A") To Asc("Z")
      For n3 = Asc("A") To Asc("Z")
        For n4 = Asc("A") To Asc("Z")
          For n5 = Asc("A") To Asc("Z")
            For n6 = Asc("A") To Asc("Z")
              iRow = iRow + 1
              If iRow > 1000000 Then
                ws.Columns(iColumn).EntireColumn.AutoFit
                iColumn = iColumn + 1
                iRow = 1
                Application.StatusBar = Format(iCombinations, "#,###") & " combinations found in " _
                    & Format(Now() - dStart, "hh:nn:ss")
                Application.ScreenUpdating = False
                Application.ScreenUpdating = True
              End If
              ws.Cells(iRow, iColumn) = Chr(n1) & Chr(n2) & Chr(n3) & Chr(n4) & Chr(n5) & Chr(n6)
              iCombinations = iCombinations + 1
            Next n6
          Next n5
        Next n4
      Next n3
    Next n2
  Next n1
  
  ws.Columns(iColumn).EntireColumn.AutoFit
  
  Application.ScreenUpdating = True
  
  MsgBox Format(iCombinations, "#,###") & " combinations found" & Space(10) & vbCrLf & vbCrLf _
       & "Run time: " & Format(Now() - dStart, "hh:nn:ss"), vbOKOnly + vbInformation[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]End Sub[/FONT]
 
Upvote 0
hey guys was messing around with excel and run into a few problems on my own, I'll like to know:
1:would the formulas you guys provided work on excel 2003?

2:is there someone who's willing to help me with the correct the flaws I'm having, someone to work with me side by side via chat etc?

3:I'm new to the whole VBA stuff, should i have more practice in it since the formulas seems a bit advanced?
 
Upvote 0
As far as my VBA code is concerned it should work unchanged in Excel 2003 except for one thing: the number of rows in a 2003 worksheet is 65,536 rather than the 1,048,576 of 2007. Any runs which produce more than 65,536 permutations will abend when they get to the 65,537th row, so you'd have to change the bit of code which reads If iRow > 100000 to If iRow > 65536 as this is the bit which starts a new column when the old one is filled. Note that 2003 has an overall limit of 16,777,216 cells, so the 5-character version of the code would work but the 6-character version would not.

I'm happy to continue monitoring this thread, however I can't do the chat thing - it's blocked by my organisation's firewall.

VBA is a continual learning experience. The code I've provided is actually fairly basic - you should be able to do things like this yourself with very little practice. I'll post a stripped-down version of the 2-character version of my code: try to follow it through and understand how it works. The larger versions are just extended version of the same code.
 
Upvote 0
Code: 
[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Sub CombineAtoZx2()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim ws As Worksheet
  Dim n1 As Integer
  Dim n2 As Integer
  Dim iRow As Long
  
  Set ws = ThisWorkbook.Sheets(1)
  ws.UsedRange.ClearContents
  
  iRow = 0
  For n1 = Asc("A") To Asc("Z")
    For n2 = Asc("A") To Asc("Z")
      iRow = iRow + 1
      ws.Cells(iRow, "A") = Chr(n1) & Chr(n2)
    Next n2
  Next n1
  
  ws.Columns("A").EntireColumn.AutoFit
  
  MsgBox "Done: " & iRow & " combinations generated." & Space(10), vbOKOnly + vbInformation[/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]
 
Upvote 0
Mr. Ruddles,

The 2-character version served very usefully; I was able to pull 676 sets, after which I was able to pull a 4 character version too. Hence I am making progress.

It is exactly what I was looking for except for one thing.

Example: (AB & BA), (CD & DC), (XY & YX).

The above characters are the same. I was looking for the possible sets without the exact matching characters.

Example: 4 character - A, B, C, D, E

= (A, B, C) (A,B,D),(A,B,E), (A,C,D),(A,C,E),(A,D,E)
= (B,C,D),( B,C,E),(B,D,E)
= (C,D,E)

Awaiting your favorable response!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Custom View Fails with "Can't push objects off the sheet", but there are no objects!
Replies
5
Views
206
BoredTrevor
B
G
  • Solved
Highlight text in column 1 based on a range of words* in column 2
Replies
6
Views
224
Fluff
Fluff
S
  • Solved
Standard deviation of a subset in VBA
Replies
7
Views
858
SHW2022
S
franklin_m
  • Solved
Formula Q: Should be simple but got me stuck
Replies
9
Views
161
Fluff
Fluff
D
  • Question Question
Creation of backup on open when file is stored in Microsoft one drive
Replies
7
Views
78
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,224,603
Messages
6,179,853
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top