combination problem

muppet77

Board Regular
Joined
Jan 24, 2004
Messages
223
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
544
Office Version
  1. 2013
Platform
  1. Windows
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...
 

northwolves

Well-known Member
Joined
Jun 21, 2006
Messages
1,122
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
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234

ADVERTISEMENT

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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
 

muppet77

Board Regular
Joined
Jan 24, 2004
Messages
223

ADVERTISEMENT

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

Steffmeister

Board Regular
Joined
Nov 7, 2005
Messages
195
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.
 

Bixby

New Member
Joined
May 14, 2007
Messages
3
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
 

Forum statistics

Threads
1,141,734
Messages
5,708,171
Members
421,550
Latest member
Dtcfire

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
Top