Hello all!
How can I get Excel to generate all possible double deck compositions in four-column card totals
Column 1 = aces (total n cards = 0 to 8)
Column 2 = 789 (total n cards = 0 to 24)
Column 3 = TJQK (total n cards = 0 to 32)
Column 4 = 23456s (total n cards = 0 to 40)
Results woud look like...
8/24/32/40
8/23/32/40
8/22/32/40
…etc
Hi Sec,Originally Posted by Secretariat
The ideal approach would be to generate a simple code in any programming language that contains
four nested "for" loops and then import the output of this program into an Excel spreadsheet.
For example:
In total, you will generate about 304,425 rows.Code:#include <stdio.h> int main() { int i, j, k, l; for (i = 0; i <= 8; i++) { for (j = 0; j <= 24; j++) { for (k = 0; k <= 32; k++) { for (l = 0; l <= 40; l++) { printf("%2d,%2d,%2d,%2d\n", i, j, k, l); } } } } return 0; }
Hope this helps.
Sincerely,
Cac
Luck is what happens when preparation meets opportunity.
You can input to Excel if VBA is accessible to you:
1. Open Visual Basic Editor: Tools/Macro/Visual Basic Editor - shortcut is alt F11
2. In VB Editor View/Project Explorer - shortcut is ctrl+r
3. Right click in Project Explorer and choose Insert Module
4. Paste this code in code module window
You can run code in VB Editor from Run Menu/Run Sub/UserForm - shortcut is F5.Code:Public Sub InputToExcel() Dim nA, n23456, n789, nT As Integer Dim n As Long Worksheets("Sheet1").Activate Cells(1, 1).Value = "A" Cells(1, 2).Value = "23456" Cells(1, 3).Value = "789" Cells(1, 4).Value = "T" Range(Cells(1, 1), Cells(1, 4)).HorizontalAlignment = xlHAlignCenter Range(Cells(1, 1), Cells(1, 4)).Font.Bold = True Range(Cells(1, 1), Cells(1, 4)).Borders.LineStyle = xlDouble n = 1 For nA = 0 To 8 For n23456 = 0 To 40 For n789 = 0 To 24 For nT = 0 To 32 Cells(n + 1, 1).Value = nA Cells(n + 1, 2).Value = n23456 Cells(n + 1, 3).Value = n789 Cells(n + 1, 4).Value = nT Range(Cells(n + 1, 1), Cells(n + 1, 4)).HorizontalAlignment = xlHAlignCenter n = n + 1 Next nT Next n789 Next n23456 Next nA End Sub
You can also run from main Excel Window Tools Menu/Macros/InputToExcel after code has been pasted in module.
You will get 304425 rows of data. My version of Excel is limited to 65536 rows but I think newer versions can handle this.
good luck.
k_c
"Perfection is the enemy of success."
-Elon Musk-
Hello Cac and K_C
You guys are the best
I just found that the . xls file format has a limit of 65,536 rows (as stated by k_c) in each sheet, while the . xlsx file format has a limit of 1,048,576 rows per sheet.
You both stated that 304 425 was the expected number of rows. I realize that this is too much and somewhat impractical as decks are never dealt to the last card. So even with a DD game dealt to the 26th card, we will never encounter a situation with zero tens or zero 23456 cards. So, I set the minimums for 789s to 4, for Tens to 8, and for 23456s to 12.
I am not comfortable yet with “other programming languages”, or even VBA, but I just found a quick way around my problem with the random function. This is sufficient for my actual purpose, i.e. to design a training aid for fast multiparameter mental calculus. It just doesn’t yield a comprehensive and orderly result like you would get.
Thanks guys.
NOTE: I will give a shot at VBA k_c
Last edited by Secretariat; 06-20-2024 at 07:21 AM.
Hi Sec,
What I can do is send you a compressed file with the 304,425 lines (9*25*33*41) and you simply import it into Excel (.xlsx).
Then, you delete all the lines whose sum is less than 39 or 52, for example.
Send me an email and I will reply with the file.
Sincerely,
Cac
Luck is what happens when preparation meets opportunity.
Javascript is pretty easy, and accessible to everyone. You can write scripts for drills like basic strategy, indices, card counting, etc.
Then when you get good at html you can add fancy formatting.
This one generates your parameters at random. (I attached it because I couldn’t get the forum to accept javascript in a post. To see and edit the code, unzip it and open it with a text editor.)
Nice to know.
I came up with results like the ones below using the random function in Excel which are similar to your file.
3 17 32 37 8 21 24 23 1 12 19 20 6 20 25 35 7 21 19 33 3 8 26 34 3 10 13 19 8 24 27 18 5 11 13 27 3 10 12 35
I think Secretariat and probably most everyone else with Excel uses newer version. I think newer versions have VBA 7 while I have VBA 6. VBA 7 is backward compatible to VBA 6 so code should work. I believe VBA 7/6 are still pretty similar.
k_c
"Perfection is the enemy of success."
-Elon Musk-
VBA7 was introduced to address the issue of 64bit office support, it's not related to xls vs xlsx file format. The xlsx file format has been around since 2003. So I was questioning why was using an old format with 65K row max, bigger file sizes, slower to load etc.
Chance favors the prepared mind
Posting Permissions
Reply With Quote
Bookmarks