# Thread: List of combinations

1. ## 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. 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. 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. Not sure I know how to modify the VB for something like generating 10,000 mega millions numbers...your help would be appreciated.

5. 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. 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. I just wanted to generate (only) 10,000 of the possibilities. I think one of these solutions will work. Thanks for your comments.

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

10. PERFECT. Thanks.

11. 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. 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
•