Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    List of combinations

    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Not sure I know how to modify the VB for something like generating 10,000 mega millions numbers...your help would be appreciated.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  6. #6
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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)

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I just wanted to generate (only) 10,000 of the possibilities. I think one of these solutions will work. Thanks for your comments.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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
    Attached Files Attached Files

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    PERFECT. Thanks.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    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?!?!

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    There shouldn't be. It should be extremely rare for there to be repeating random numbers.


    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •