# combination problem

#### muppet77

##### Board Regular
i have around 2.5 million rows of 5 numbers in a text file.

i would like to transfer them to an excel document. as the sheet is only 65536 rows down, it would take up more than one sheet (or more columns on the same sheet).

i tried a macro to cut the lines and paste them from notepad to excel, putting them in a new worksheet tab when one filled up. however i calculated it would take 5-6 hours to do every line.

each line contains 5 numbers. each number represents a playing card 1-52. i am trying to generate all 2.5 million possible hands of 5 card poker.

my question is, could i come up with a formula to do this without cutting from a txt file?

eg row one = 1,2,3,4,5
row two = 1,2,3,4,6
row three = 1,2,3,4,7
etc

i have tried 5 formulae, one for each cell across, but it keeps going wrong.

any ideas?

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Code:
``````Sub MakePoker()

Dim a          As Integer
Dim b          As Integer
Dim c          As Integer
Dim d          As Integer
Dim e          As Integer

Dim lPaste     As Long
Dim stHand     As String

Const iCards   As Integer = 52

lPaste = 1

For a = 1 To iCards - 4
For b = a + 1 To iCards - 3
For c = b + 1 To iCards - 2
For d = c + 1 To iCards - 1
For e = d + 1 To iCards - 0
stHand = a & ", " & b & ", " & c & ", " & d & ", " & e
ActiveSheet.Cells(lPaste) = stHand
lPaste = lPaste + 1
Next e
Next d
Next c
Next b
Next a

End Sub``````

pastes each "hand" into a new cell. It takes a while to run, although a "screenupdating = false" might help that...

Use 2-D array to save your results and write them to activesheet cells only once to speed up.
Code:
``````Sub xxxx()
Application.ScreenUpdating = False
Dim arr(1 To 65536, 1 To 40), a As Byte, b As Byte, c As Byte, d As Byte, e As Byte, col As Byte, row As Long
col = 1
For a = 1 To 48
For b = a + 1 To 49
For c = b + 1 To 50
For d = c + 1 To 51
For e = d + 1 To 52
row = row + 1
arr(row, col) = a & "," & b & "," & c & "," & d & "," & e
If row = 65536 Then row = 0: col = col + 1
Next
Next
Next
Next
Next
[a:an] = arr
[a:an].Columns.AutoFit
Application.ScreenUpdating = True
End Sub``````

Best regards

Northwolves

"i am trying to generate all 2.5 million possible hands of 5 card poker. "

why? whatever the ultimate problem you're trying to solve, I'd be surprised if sticking raw data into 38 separate sheets then trying to perform some further operations on the data is the best way to approach it.

Hi muppet77

I'm almost sure your objective is not to have a list with 2.5 million combinations. It's surely an intermediate step towards the solution of your problem. Would you care to share with us your whole problem. Maybe we can contribute with ideas to the solution that do not involve writing the 2.5 million combinations in one or several worksheets.

Best regards
PGC

basically, i am trying to design a 5 hand poker calculator, which gives the odds of each hand - i am aware there are ones out there, but i thought that i would try to build my own.

there are 2 ways to do it (as far as i can see)
1. once given the 2 hole cards for each player, calculate how many of the two and a half million hands would win/draw for each player.
2. randomly generate a number of hands and see what % win for player A and B and also tie.

my gut reaction is that excel won't cope, or my standard computer is too slow.

hope this helps to give someone an idea.....

This is also what i'm looking for, is this possible in Excel to calculate the odds in poker?
Must look like all the 52 cards and compare it with your hand and the flop and then with the turn and river. So 2 cards in your hand an totally 5 on table. Does somebody know more about this?

Tnx for now.

I am looking to do something in Excel along these lines. Instead of having all of the card hands listed it would be much better to do things via formula. The number of dependant and independant variables for calculating the odds is a bit daunting.

At this point I am getting some information together to prepare my approach to this. If anyone is interested in being part of these efforts PM me and I will include you.

Regards,
Matt

Replies
2
Views
208
Replies
1
Views
190
Replies
12
Views
571
Replies
1
Views
302
Replies
2
Views
103

1,218,812
Messages
6,144,618
Members
450,560
Latest member
afcmRamos

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

### Which adblocker are you using?

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

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