solving for all possible combinations of a set of numbers

[Guest]

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!

 

[Tom Ogilvy]

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.

 

[pietlinden]

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.

 

[Max]

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

 

[Guest]

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

 

[Guest]

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]

Think Tom Ogilvy just posted this response in .public.excel:

Code by Myrna Larson that does this:
http://tinyurl.com/cdjck

 

[Max]

Glad to hear that !
We'll look forward to it <g>

 

[Max]

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

 

[Max]

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

 

[Max]

Here's a new link to the sample file (previous one's gone):
http://savefile.com/files/4491071