combination problem

[muppet77]

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?

 

[northwolves]

How about pulling them into cells instead of rows?

 

[dcardno]

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]

Use 2-D array to save your results and write them to activesheet cells only once to speed up.

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]

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

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]

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]

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]

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