List permutations of sets?

Hello All,
I'm working on a problem that I cannot seem to find the correct answer to: I have a set of 12 items that are tested together in pairs to get a value. I need to then choose the best set of 6 pairs from those results. The math and formulas to get the "best results" are simple enough, but I first to to get a list of all the possible permutations of the group of 12 items.
So I'm starting with this (Max and Min results)
results.PNG

Taking the averages (for now, just an easy way to look at the data)
Averages.PNG

Assign those values into the permutations (smaller set shown for simplicity)
small data set.PNG

And then I want to show the best possible set of sets (maybe something like this, I'm sure I could find a better display option, but this works for now)
End goal.PNG

Problem is, manually inputting all possible permutations would take ages. I'd love for someone to clue me in on how Excel may be able to generate a list somewhat like this (formatting would be easy enough to correct)
Desired list.PNG

I only need to run this once to build the sheet, so I'm perfectly happy manually doing any formatting and data manipulation, other than the initial listing of permutations. Does anyone have any ideas for me?

Thanks.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Example Doc attached, But the document really only shows what I plan to do with the data.
My problem is how to compile a list to show the data. I expect the list to be ~10000 groups of 6 items each. Too many to enter by hand like I have in my example of 6 total items.
Apologies for my lack of proper terminology on this, as I'm sure there is a more accurate way to describe what I need.

So basically, I need to tell Excel to generate a list of all possible combinations of 12 items into 6 pairs using each item only once:
1/2 1/2 1/2
3/4 3/4 3/4
5/6 5/6 5/6
7/8 7/8 7/8
9/10 9/11 9/12
11/12 10/12 10/11 etc, etc, etc

Preferably these sets would be sent to separate cells, so "1" in one cell, "2" in the cell next to it. That would allow me to quickly fill in the 60k+ cells with data using simple IF/AND statements.

Thank you for your quick response and interest in helping me out here!

Bit of Eureka Moment here:
if the above code were manipulated to only do every Combination that begins with 1 (First row =1,2,3,4,5,6,7,8,9,10,11,12... to last row 1,12,11,10,9,8,7,6,5,4,3,2)
then that should give all of the combination I am looking for, also greatly decreasing the demand on memory, and reduce the amount of re-work i need to do to eliminate "duplicate" entries.

I'm trying to deciefer the code to see if i can get there myself, but I'd love any input from real pro's.

Also, I thought i may have had a work around with only selcting x number of rows for the array to fill, but it hit troubles as well:
If I run the code (=ListPermut(6)) selecting A1-F10 for example, it will run only the first 10 rows of the permutations. This works up to (10), but fails on 11 and 12.

Any thoughts?

Hi

See my proposal for your problem.

First column: Use in A2 and drag down to row 4096

Formula:

Please Login or Register  to view this content.

This formula gives us all possible combinations of 12 elements (4096) (-1 because I do not considered the value zero)
Notes:
Sheet x is a dummy (empty) sheet to use with ROW and COL and not perturb the sheet we use.
Excel DEC2BIN use only 9 digits, but we need 12 digits.
In B2 and C2 use the following formulas and copy C2 to D2 (C are row and D are column

Formula:

Please Login or Register  to view this content.

You can see I split your table Low/High into two tables, table LOW in $V$3:$AG$14 and HIGH in $AI$3:$AT$14
To do the first step use in E2 the following formula

Formula:

Please Login or Register  to view this content.

In V18 use the formula to get the 1st. step table V18:AG18

Formula:

Please Login or Register  to view this content.

In F2 use the following formula to mark the combinations that has 6 digits "1"

Formula:

Please Login or Register  to view this content.

So, we can combine each (first, second, ...) digit "1" with (first, second, ...) "0" from right to left.
Columns G:L use in G2 the formula

Formula:

Please Login or Register  to view this content.

Columns M:R use in M2 the formula

Formula:

Please Login or Register  to view this content.

Column S has the value of each valid group

Formula:

Please Login or Register  to view this content.

The solution are in V31:AI32 ...
3.670 63 1 2 3 4 5 6 7 8 9 10 11 12 (first place)
3.701 95 1 2 3 4 5 7 6 8 9 10 11 12 (second place)

The value 3.670 are in row 63 (Excel row 64) and the group are 1-7, 2-8, 3-9, 4-10, 5-11, 6-12
The value 3.701 are in row 95 (Excel row 96) and the group are 1-6, 2-8, 3-9, 4-10, 5-11, 7-12
See the file for clarification

Jose,
Thank you so much for everything you've done here! It does seem to do most of what I'm looking for, but I do not believe it is covering all possible options as I am looking for them. With a maximum possible 4096 combination on that sheet, I believe it is roughly 6000 short. My "calculation" works like so:
for 4 items required to make 2 pairs, you get 3 possible options:
1234
1324
1423
for 6 items required to make 3 pairs, you en up with 15 possible combinations
123456
123546
123645
132456
132546
132645
142356
142536
142635
152346
152436
152634
162345
162435
162534

I won't type them all out, but trust me in that there are then 105 combinations of pairs when you increase the set to 8 items (I did work out the first row of this manually).
so looking at 3-15-105 the pattern is a double factorial. 4 items: 3*1=3, 6 items: 5*3*1=15, and 8: 7*5*3*1=105. So I believe I'm looking for 10395 possible combinations (12!!=11*9*7*5*3*1)

After everything I tried yesterday, listing permutations in the typical sense will not work for me, due to the memory limit on Excel. What i'm going to work on now is figuring out a system to due what I was doing manually. The way i've worked is starting at:
1-2
3-4
5-6
7-8
I then increase the next to last row's second digit until i'm out of options:
1 2 1 2 1 2
3 4 3 4 3 4
5 6 5 7 5 8
7 8 6 8 6 7

then i'll up the value next to 3, and repeat the above for 3-5:
1 2 1 2 1 2 1 2 1 2 1 2
3 4 3 4 3 4 3 5 3 5 3 5
5 6 5 7 5 8 4 6 4 7 4 8
7 8 6 8 6 7 7 8 6 8 6 7
up the value again and repeat again:
1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2
3 4 3 4 3 4 3 5 3 5 3 5 3 6 3 6 3 6
5 6 5 7 5 8 4 6 4 7 4 8 4 5 4 7 4 8
7 8 6 8 6 7 7 8 6 8 6 7 7 8 5 8 5 7
and so on and so forth, until I reach the last value for the "1-2 group", which I believe would be:
1 2
8 7
6 5
4 3

but I need it done with 12 instead of 8.

Hi

I was left with a great doubt. Can you clarify me by indicating the value of the groups?

1-2, 3-4, 5-6, 7-8, 9-10, 11-12 -> 4.06?

1-2, 12-11, 10-9, 8-7, 6-5, 4-3 ->

Jose,
Those values would be the same, as they are just mirrored sets. I have made an error in my "last set" numbers. Thanks for pointing that out. I took a bit more time to think about it, and in the example below, it should end:
12
38
47
56
This gives from a set of 4: 3 options, going to 6 gives 15 total options. 3 from the set of 4 option, multiplied by the new 5x complexity of going to 6. Same thing happens
when you go to 8: 15 original options increased by a complexity of 7 = 105 total unique sets. Using that, I know that there are
15 options in the first line of 8 items (sets with 1-2 have a total of 15 permutations)

SHG,
You're on the right path, and gave me further proof of my logic that there are 10395 options when look at possible pairs from a set of 12 items. I am not looking for the graphs, just a way to have excel list each possible combination. So going from the graph example, assuming that you assign a value at each node, I just need the 2 point data that each line is based on.

So for the 6 item example, the 15 permutations would be:
1 2 1 2 1 2
3 4 3 5 3 6
5 6 4 6 4 5

1 3 1 3 1 3
2 4 2 5 2 6
5 6 4 6 4 5

1 4 1 4 1 4
2 3 2 5 2 6
5 6 3 6 3 5

1 5 1 5 1 5
2 3 2 4 2 6
4 6 3 6 3 4

1 6 1 6 1 6
2 3 2 4 2 5
4 5 3 5 3 4

I can see there is a pattern, and I am currently working on how to work it out mathematically. But if anyone else has seen this before or can be me to it, I'd be glad for any input.

Thanks!

Originally Posted by shg

It sound to me like you want a list of all the graphs of n nodes with perfect matchings. The first figure at https://en.wikipedia.org/wiki/Double_factorial shows the 15 for n=6. That make sense?

I suppose you are right. My error was COMBIN(4, 2) = 6 and COMBIN (6, 2) = 15, so I just thought of Combinations.

SHG,
Thanks for what you've done here, but I cannot get it to run. Could you walk me through getting it running?

PERFECT! You're the best!