List of combinations

Topic

New Reply

I suppose this is a VB-Excel solutions (don’t know VB), but I’m wondering if someone has a solution in any form to generating all of the combinations of n things k at a time?

For example, if I wanted all of the 5-card hands from a deck of 52 cards, there would be more than 2.5 million of them…so, this would have to be an Excel sheet with multiple sets of columns — 5 cols, thousands of rows; then 5 more columns, thousands of rows, etc.

Or, is this something that’s outside of the scope of what should be done in Excel?

Reply | Quote

Replies

I am not sure why you want a list like this in multiple columns. But this will generate it. The code checks the rows, the number of combos, etc and displays the percent completed in the status bar. It took about 4 mins to run on my computer.

There are multiple groups of 5 cards. The cards are displayed in the form: “2 of Clubs” to “Ace of Diamonds”. The font is red for Diamonds and Hearts. If you want to use shorthand you can change the text in vRank and vSuit Arrays and the sSep (the separator: ” of “).

Steve

Code:

Option Explicit
Sub CardCombos()
  Dim vRanks, vSuits
  Dim iRanks As Integer
  Dim iSuits As Integer
  Dim sSuit As String
  Dim sRank As String
  Dim sSep As String
  Dim iCont As Integer
  Dim iItems As Integer
  Dim lRow As Long
  Dim lHand As Long
  Dim lCombos As Long
  Dim iGroup As Integer
  Dim iGroups As Integer
  Dim iCol As Integer
  Dim i As Integer
  Dim iCard1 As Integer
  Dim iCard2 As Integer
  Dim iCard3 As Integer
  Dim iCard4 As Integer
  Dim iCard5 As Integer
  Dim iCards(1 To 5) As Integer
  Dim lRows As Long
  Dim bOldStatusbar As Boolean
 
  vRanks = Array("2", "3", "4", "5", "6", "7", "8", "9", "10", "Jack", "Queen", "King", "Ace")
  vSuits = Array("Spades", "Clubs", "Hearts", "Diamonds")
  sSep = " of "
 
  On Error GoTo ErrHandler
  iRanks = UBound(vRanks) + 1
  iSuits = UBound(vSuits) + 1
  iItems = iRanks * iSuits
  With Application
    .ScreenUpdating = False
    bOldStatusbar = .DisplayStatusBar
    .DisplayStatusBar = True
    .StatusBar = "Checking to continue.."
    lCombos = .WorksheetFunction.Combin(iItems, 5)
  End With
  lRows = ActiveSheet.Rows.Count
  iGroups = Int(lCombos / (lRows - 2) + 0.9999999999)
 
  iCont = MsgBox("There are " & Format(lCombos, "#,##0") & " Combinations." _
    & vbCrLf & "This will require " & 6 * iGroups & " Columns of " _
    & Format(lRows, "#,##0") & " Rows." & vbCrLf & "Do you want to proceed?", _
    vbYesNo + vbQuestion)
  If iCont = vbNo Then    ' Doesn't want to continue
    MsgBox "Cancelled by user."
    GoTo ExitHandler     ' Quit the macro
  End If
'add new sheet
  Worksheets.Add
 
'Set up Headers
  For iGroup = 1 To iGroups
    iCol = (iGroup - 1) * 6
    'Header rows
    Cells(1, iCol + 1) = "Group" & iGroup
    For i = 1 To 5
      Cells(2, iCol + i) = "Card" & i
    Next i
  Next iGroup
    'Loop through items
  iCol = 0
  lRow = 2
  lHand = 0
  For iCard1 = 1 To iItems - 4
    For iCard2 = iCard1 + 1 To iItems - 3
      For iCard3 = iCard2 + 1 To iItems - 2
        For iCard4 = iCard3 + 1 To iItems - 1
          For iCard5 = iCard4 + 1 To iItems
            lRow = lRow + 1
            If lRow > lRows Then
              lRow = 3
              iCol = iCol + 6
            End If
            lHand = lHand + 1
 
            Application.StatusBar = _
              Format(lHand / lCombos, "0.00%") & " complete"
            iCards(1) = iCard1
            iCards(2) = iCard2
            iCards(3) = iCard3
            iCards(4) = iCard4
            iCards(5) = iCard5
            For i = 1 To 5
              sSuit = vSuits(Int((iCards(i) - 1) / iRanks))
              sRank = vRanks((iCards(i) - 1) Mod iRanks)
              Cells(lRow, iCol + i) = sRank & sSep & sSuit
              If Int((iCards(i) - 1) / iRanks) > 1 Then
                Cells(lRow, iCol + i).Font.Color = vbRed
              End If
            Next i
          Next iCard5
        Next iCard4
      Next iCard3
    Next iCard2
  Next iCard1
  MsgBox "Done"
ExitHandler:
  'put things back in order
  With Application
    .ScreenUpdating = True
    .StatusBar = False
    .DisplayStatusBar = bOldStatusbar
  End With
  Exit Sub
 
ErrHandler:
    MsgBox "Error number: '" & Err.Number & " '" _
      & vbCrLf & Err.Description
    Resume ExitHandler
End Sub

Reply | Quote

Thanks. I thought I’d need sets of columns because of the large number of resulting combinations (more rows than Excel permits).
I hope I can read through this and try something like it — for the mega millions combinations…first 5 numbers from 1-56 and the 6th from 1-46.

Any further thoughts/comments would be appreciated.

Thanks again.

Reply | Quote

Not sure I know how to modify the VB for something like generating 10,000 mega millions numbers…your help would be appreciated.

Reply | Quote

I hope I can read through this and try something like it — for the mega millions combinations…first 5 numbers from 1-56 and the 6th from 1-46….Not sure I know how to modify the VB for something like generating 10,000 mega millions numbers…

You seem a little confused. There are over 175 million mega million combinations, not 10,000. You have 2.6 Million card hands (5 cards from 52) with 5 numbers from 56 you get over 3.8 million combinations and with the 6th number with 46 possibilities it takes it to over 175 million.

In XL 2007 this would be close to 1200 columns. With pre-XL2007 this would take about 74 separate worksheets. If it took about 4 mins to run the card combos it will take over 4 HOURS to run the mega million combos.

The changes to create the list is simple (the code is actually simplified: no ranks and suits required, just numbers). The issue is with creating new worksheets when the columns run out.

Before I invest any calories in doing this, what do you plan on doing with a list of over 1 billion numbers. I can’t imagine that excel will be able to do too much them…

If you are looking for a spreadsheet to generate random samples there are much easier ways…

Steve

Reply | Quote

I just wanted to generate (only) 10,000 of the possibilities. I think one of these solutions will work. Thanks for your comments.

Reply | Quote

Kweaver:
To get possible winning numbers for the MegaMillions game or six other national/regional lotteries, my “Lottery Numbers” Excel workbook free download is here…
http://excelusergroup.org/media/p/5941.aspx

Steve:
I ran John Walkenbach’s VBA code speed test in 5 seconds, but your Card code was only 20% complete at the 3 minute mark. I lost interest at that point.
Are you using a government computer? (grin)

Reply | Quote

Thanks for this, Jim. Is there a (simple) modification that would enable me to generate 10,000 unique combinations rather than a max of 5?

Reply | Quote

Here is a file that random generates values and will create a list of as many as you want (with the limitation of the number of XL rows!). It has 10,000 generated. When the button is pressed it asks how many to generate. It took about 15 secs to generate the 10,000.

Steve

Reply | Quote

PERFECT. Thanks.

Reply | Quote

I just noticed that sometimes within the first 5 choices, there were repeats. I thought the numbers in the first 5 were always unique — no duplicates?!?!

Reply | Quote

There shouldn’t be. It should be extremely rare for there to be repeating random numbers.

Steve

Reply | Quote