Home |
Search |
Today's Posts |
#1
|
|||
|
|||
solving for all possible combinations of a set of numbers
Hi
Let's say I have 6 sets 2 numbers: 1 2 3 17 18 19 7 8 9 10 11 12 What sort of function can I use to show all the possible combinations, where each number stays in its own column? For example, the first combination of 6 would be: 1 2 3 17 18 19 then 1 8 3 17 18 19 then 1 2 9 17 18 19 etc, where the 1 and the 7 stay in column 1, the 2 and the 8 stay in column 2, etc. TIA! |
#2
|
|||
|
|||
Assume your numbers are in A1:F2
In A3 put in the formula =IF(MOD(TRUNC((ROW()-3)/2^(COLUMN()-1)),2)=0,A$1,A$2) and drag fill to F3. Now select A3:F3 and drag down to row 66 (A66:F66) - 64 combinations. -- Regards, Tom Ogilvy "tradersm" wrote in message ... Hi Let's say I have 6 sets 2 numbers: 1 2 3 17 18 19 7 8 9 10 11 12 What sort of function can I use to show all the possible combinations, where each number stays in its own column? For example, the first combination of 6 would be: 1 2 3 17 18 19 then 1 8 3 17 18 19 then 1 2 9 17 18 19 etc, where the 1 and the 7 stay in column 1, the 2 and the 8 stay in column 2, etc. TIA! |
#3
|
|||
|
|||
if you have Access (Office Professional) installed, then you can link
to these two columns as if they were different tables and then create a cartesian product. No idea whatsoever how to do it in Excel. Since I'm assuming you probably don't use Access, here are the steps: 1. open a new database 2. Under the File menu, choose Get External Data, Link, and then choose your Excel SS. Link to the range you want. 3. do the same for the second range. 4. in your query, add both linked tables (the spreadsheets) 5. drop the two fields you want into the output. 6. If there is a join line (black line) connecting the two tables, delete it. 7. run the query by clicking the exclamation button. then if you want you can copy/paste that to Excel or output it pretty much any way you want. |
#4
|
|||
|
|||
Another play which generates the desired results (but in a single column
separated by hyphens) that you might wish to delve into: http://tinyurl.com/c2dpo The play is extendable to handle up to a max 6 x 6 matrix (total: 36 different numbers) which generates 6^6 = 46,656 combos. The next step up, a 7 x 6 matrix, will yield 7^6 = 117649 combos, which would exceed Excel's max 65536 rows <g. Modification steps are given in a later post in same thread: http://tinyurl.com/9v6s2 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "tradersm" wrote in message ... Hi Let's say I have 6 sets 2 numbers: 1 2 3 17 18 19 7 8 9 10 11 12 What sort of function can I use to show all the possible combinations, where each number stays in its own column? For example, the first combination of 6 would be: 1 2 3 17 18 19 then 1 8 3 17 18 19 then 1 2 9 17 18 19 etc, where the 1 and the 7 stay in column 1, the 2 and the 8 stay in column 2, etc. TIA! |
#5
|
|||
|
|||
Thank you, Max, that is exactly what I was looking for. If I make a million
I'll be sure to send you and the others here a generous tip "Max" wrote: Another play which generates the desired results (but in a single column separated by hyphens) that you might wish to delve into: http://tinyurl.com/c2dpo The play is extendable to handle up to a max 6 x 6 matrix (total: 36 different numbers) which generates 6^6 = 46,656 combos. The next step up, a 7 x 6 matrix, will yield 7^6 = 117649 combos, which would exceed Excel's max 65536 rows <g. Modification steps are given in a later post in same thread: http://tinyurl.com/9v6s2 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "tradersm" wrote in message ... Hi Let's say I have 6 sets 2 numbers: 1 2 3 17 18 19 7 8 9 10 11 12 What sort of function can I use to show all the possible combinations, where each number stays in its own column? For example, the first combination of 6 would be: 1 2 3 17 18 19 then 1 8 3 17 18 19 then 1 2 9 17 18 19 etc, where the 1 and the 7 stay in column 1, the 2 and the 8 stay in column 2, etc. TIA! |
#6
|
|||
|
|||
I had one more question:
let's say i have a row of numbers: 1 2 3 4 5 6 7 8 9 etc and I want to find and display all the combinations of 2, and 3 for 2: 1-2 1-3 1-4 etc and for 3 1-2-3 1-2-4 1-2-5 etc have any idea how to do that? TIA! "Max" wrote: Another play which generates the desired results (but in a single column separated by hyphens) that you might wish to delve into: http://tinyurl.com/c2dpo The play is extendable to handle up to a max 6 x 6 matrix (total: 36 different numbers) which generates 6^6 = 46,656 combos. The next step up, a 7 x 6 matrix, will yield 7^6 = 117649 combos, which would exceed Excel's max 65536 rows <g. Modification steps are given in a later post in same thread: http://tinyurl.com/9v6s2 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "tradersm" wrote in message ... Hi Let's say I have 6 sets 2 numbers: 1 2 3 17 18 19 7 8 9 10 11 12 What sort of function can I use to show all the possible combinations, where each number stays in its own column? For example, the first combination of 6 would be: 1 2 3 17 18 19 then 1 8 3 17 18 19 then 1 2 9 17 18 19 etc, where the 1 and the 7 stay in column 1, the 2 and the 8 stay in column 2, etc. TIA! |
#7
|
|||
|
|||
Think Tom Ogilvy just posted this response in .public.excel:
Code by Myrna Larson that does this: http://tinyurl.com/cdjck -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "tradersm" wrote in message ... I had one more question: let's say i have a row of numbers: 1 2 3 4 5 6 7 8 9 etc and I want to find and display all the combinations of 2, and 3 for 2: 1-2 1-3 1-4 etc and for 3 1-2-3 1-2-4 1-2-5 etc have any idea how to do that? TIA! |
#8
|
|||
|
|||
Glad to hear that !
We'll look forward to it <g -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "tradersm" wrote in message ... Thank you, Max, that is exactly what I was looking for. If I make a million I'll be sure to send you and the others here a generous tip |
#9
|
|||
|
|||
In case it's needed, how about a nice, easy to use, working sample file with
Myrna's subroutine implemented? A "Download File" link is available at: http://flypicture.com?display=updone&id=qtr8kao= File: MyrnaLarson_Combination_Permutation.xls Right-click on "Download File" Choose "Save Target As .." and save the file to your desired folder. Open the file from there (Do not open the file direct from the download link). You would need to enable macros, of course! Then to try it out for your situation, In Sheet1 --------- List the numbers: 1 2 3 4 5 6 7 8 9 into A3: A11 (vertically down from A3) (this list is the entire set) Enter in A1: C (< C is to run "combination") Enter in A2 the number of items (subsets to pick): 2 (say) Then *select A1* ( important!), and click the button "ListPermutations" The results will be written in col A in a new sheet (just to the left of Sheet1), viz.: 1, 2 1, 3 1, 4 1, 5 .... .... 6, 8 6, 9 7, 8 7, 9 8, 9 Then just go back to Sheet1, change the number in A2 to: 3 select A1 again, and click the button to run the sub. The results will be written in col A in another new sheet (to the immediate left of Sheet1), viz.: 1, 2, 3 1, 2, 4 1, 2, 5 .... .... 6, 7, 9 6, 8, 9 7, 8, 9 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#10
|
|||
|
|||
http://flypicture.com?display=updone&id=qtr8kao=
The link given is good, but if you click directly, it may not work. Do a copy paste of the entire line (including the "=" at the rightmost end of the line) into the address bar in the browser .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#11
|
|||
|
|||
Here's a new link to the sample file (previous one's gone):
http://savefile.com/files/4491071 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying all combinations of a range of numbers | Excel Worksheet Functions | |||
triadic combinations of words | Excel Worksheet Functions | |||
Combinations | Excel Worksheet Functions | |||
How to total itmes if they fall between a date range | Excel Worksheet Functions | |||
How to total items if they fall between a date range | Excel Discussion (Misc queries) |