Show all combinations of 'n' numbers with condition(s)?

[figeroa]

Hello, I have a various difficulties within this excel problem.

counting combinations.xlsx


I must calculate all combinations of 7 numbers that give sum of 100. Numbers must be in interval from 1 to 39.
First condition is that sum of numbers must be changeable (i mentioned before sum of 100 to be simpler so I typed number 100 in the document). The sum can be also 150 or 88 for example, depending what number is entered in cell K2.

Second condition is that every combination must be unique (no such things like 7,8,9,10,16,20,30 and 30,20,16,10,9,8,7 and 7,8,10,9,3,20,16). This is example how results shouldn't be because all 3 combinations have same numbers only with different position/order). Only one combination of same numbers is possible and numbers must be ordered from smaller to larger numbers).

Is this possible to do, what is best option: functions or maybe some macro?

[MrShorty]

I think this sort of thing is usually easier using a symbolic language like VBA or C than using spreadsheet formulas. You can then use some relatively simple loops or even recursive algorithms to obtain your result.

This looks like basically the same kind of problem discussed here: http://www.excelforum.com/excel-gene...-of-100-a.html I'd suggest you start by reviewing this discussion and adapt it to your specific requirements.

[shg]

In a two-minute search, http://saliu.com/forum/lottery-sums.html

[shg]

....................

[shg]

One way:

Please Login or Register  to view this content.

Row\Col	A	B	C	D	E	F	G
1	18	17	15	14	13	12	11
2	18	17	16	14	13	12	10
3	18	17	16	15	13	11	10
4	18	17	16	15	13	12	9
5	18	17	16	15	14	11	9
6	18	17	16	15	14	12	8
7	18	17	16	15	14	13	7
8	19	16	15	14	13	12	11
9	19	17	15	14	13	12	10
10	19	17	16	14	13	11	10
11	19	17	16	14	13	12	9
12	…	…	…	…	…	…	…
80124	39	38	10	5	4	3	1
80125	39	38	10	6	4	2	1
80126	39	38	10	7	3	2	1
80127	39	38	11	5	4	2	1
80128	39	38	11	6	3	2	1
80129	39	38	12	5	3	2	1
80130	39	38	13	4	3	2	1