How do I show all possible combinations with a list of values in excel.

[sdemeter]

I have a list of 40 values (ex. 1-40). How can I easily show all possible combinations. I don't want different orders of values to be considered a separate combination as well.

1
2
3
4
5
6
......
1, 2
1, 3
1, 4
.....
2, 3
2, 4
2, 5
.......
1, 2, 3
1, 2, 4
1, 2, 5
.......

[popipipo]

Do you know how many combination there are with 40 numbers?
1 number:1^1=1
2 numbers: 2^2=4
3 numbers: 3^3=27
4 numbers: 4^4=256
40numbers: 40^40= 1,20892581961463E+64

[shg]

Including the subset with no members, there are 2^40, which is just north of a billion combinations.

If you can pare that to a more practical number (< 1 million), see the workbook at https://app.box.com/s/b9b9fc06beb63b9562f9.

[sdemeter]

I definitely need to reword my problem. Its not the number of combinations for 40 values.

So I think I have 5 sets with 4 values in each. I am looking for the number of combinations if only 1 value from each set can be used in each combination. Again a different order of values doesn't make a different combination.

Set A
A1
A2
A3
A4

Set B
B1
B2
B3
B4

Set C
C1
C2
C3
C4

Set D
D1
D2
D3
D4

Set E
E1
E2
E3
E4

[shg]

In that case, see the workbook at https://app.box.com/s/47b28f19d794b25511be

[sdemeter]

Also have to mention I guess that a value from each set is not required. So for example A1 or B1, etc. could be its own combination.

[shg]

Put a single space in the top row (row 4):

Row\Col	A	B	C	D	E	F
2		Set A	Set B	Set C	Set D	Set E
3
4
5		A1	B1	C1	D1	E1
6		A2	B2	C2	D2	E2
7		A3	B3	C3	D3	E3
8		A4	B4	C4	D4	E4
9
10	1
11	2					E1
12	3					E2
13	4					E3
14	5					E4
15	6				D1
16	7				D1	E1
17	8				D1	E2
18	9				D1	E3
19	10				D1	E4
20	11				D2
21	12				D2	E1
22	13				D2	E2
3129	3120	A4	B4	C4	D3	E4
3130	3121	A4	B4	C4	D4
3131	3122	A4	B4	C4	D4	E1
3132	3123	A4	B4	C4	D4	E2
3133	3124	A4	B4	C4	D4	E3
3134	3125	A4	B4	C4	D4	E4

[sdemeter]

Thanks!. This is really helping, however when I put a space in row four it only makes the last column (Set D in your example) be its own separate combination without values from the other other sets. So for example A1 is not listed as its own combination .

[shg]

Arrangement 626?

Row\Col	A	B	C	D	E	F
2		Set A	Set B	Set C	Set D	Set E
3
4
5		A1	B1	C1	D1	E1
6		A2	B2	C2	D2	E2
7		A3	B3	C3	D3	E3
8		A4	B4	C4	D4	E4
9
10	1
11	2					E1
12	3					E2
13	4					E3
14	5					E4
15	6				D1
16	7				D1	E1
17	8				D1	E2
18	9				D1	E3
19	10				D1	E4
20	11				D2
21	12				D2	E1
22	13				D2	E2
634	625		B4	C4	D4	E4
635	626	A1
636	627	A1				E1

[sdemeter]

Ah your right. I see it now.

Any tips if I actually want to use 10 sets of 4. The tool said there were too many rows.

Thanks!

[shg]

That's 5^10 > 9 million arrangments. As a matter of philosophy, I don't think generating lists that long has any value that couldn't be achieved by some other method responsive to the problem.