I have numbers from 1 to 75 and need to make all possible four number combinations from these numbers. Such as: 30,75,15,44 or 52,66,71,3 etc. as long as there are no repeating sets of four numbers. Is this at all possible? First time user here so I hope I am posting this properly.
There are 1,215,450 such combinations. What would you do after you listed them?
Entia non sunt multiplicanda sine necessitate
I will not use all the combinations. I may need 1000 combinations for this project and next week I may need 500 different combinations. I just need to know that there are not any repeats per even or generation of combinations. I also hope that this will show me how to do future random combinations with say three number combinations from 15 different numbers. Or two number combinations from 20 different numbers. And so on. Is there any answer to my inquiry?
You could use a UDF:
To pick a random combination of 75 choose 5, select a 4-wide array of cells and array-enter this formula:
Please Login or Register to view this content.
=aiComboByNum(75, 4, RANDBETWEEN(0, COMBIN(75, 4) - 1))
The first (zeroth) combination is
=aiComboByNum(75, 4, 0) = 3 2 1 0
and the last is
=aiComboByNum(75, 4, 1215449) = 74 73 72 71
Thank you ... I'm not sure what to do with it but I certainly will try. I have used excel for years and only really used it's formulas. I assume this is used through the developer ?
Yes.
Adding a Macro to a Code Module – Excel 2007
1. Copy the code from the post
2. Click the Developer tab, then Visual Basic
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens
5. Press Alt+Q to close the VBE and return to Excel
Thank you so much for your patience and knowledge. I completed the five steps (using the code in the window) and I am left looking at a blank worksheet. Now I’m not sure how to generate a list of numbers once I am looking or working in the worksheet. Right now it is blank. What do I have to do to obtain a list of random four number combinations? (73, 2, 33, 69).
Thanks again for your help.
Enter the first formula I gave exactly as described.
I went to the blank work sheet and entered: =aiComboByNum(75, 4, RANDBETWEEN(0, COMBIN(75, 4) - 1))
and the result is : #NAME?
there is a typo in the code the first line should read
dont forget to array enter it with ctrl+shift+ enterPlease Login or Register to view this content.
see here
http://office.microsoft.com/en-us/ex...872901033.aspx
Last edited by martindwilson; 01-10-2010 at 10:57 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Sounds like you didn't get the code installed. See attached. Before you open the workbook, set macro security to Disable all macros with notification, and then enable macros when prompted.Originally Posted by Chris
There's no typo, Martin, it's correct as written.
True, thanks for the emphasis.
hm it fails like that but works with the correction tho. i get name error using as you posted but it gives the results you posted when the last As Long() is omited then again i didnt test it in 2007 but in 97
edit just tried in 2007 ok hmmmmmmmmmmmm
Last edited by martindwilson; 01-10-2010 at 01:59 PM.
I tested in 2003 and 2007; don't have anything older.
i see what you mean ,must be a version thing
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks