Sort a list of combinations based on the numerical values from each cell

[vladimiratanasiu]

Hello!
I have a range of cells with numerical combinations (A8:A38) equal with a given sum (A7), generated on the basis of a built-in list (A1:B5). Each combination contains in this case 3-5 values, but its length may depend on the size of range and the target number. I need a formula, macro etc., to sort all combinations in ascending series, according to the number of elements (1,2,3 etc.) from each cell, and if possible the content of that cell to be sorted from 1 to 9.

Thank you!

Book1.xlsm
	A	B
1	1	6
2	2	7
3	3	8
4	4	9
5	5	10
6
7	20
8	1,6,2,7,4
9	1,6,2,3,8
10	1,6,3,10
11	1,6,8,5
12	1,6,4,9
13	1,2,7,10
14	1,2,3,4,10
15	1,2,3,9,5
16	1,2,8,4,5
17	1,2,8,9
18	1,7,3,4,5
19	1,7,3,9
20	1,7,8,4
21	1,4,5,10
22	1,9,10
23	6,2,7,5
24	6,2,3,4,5
25	6,2,3,9
26	6,2,8,4
27	6,7,3,4
28	6,4,10
29	6,9,5
30	2,7,3,8
31	2,3,5,10
32	2,8,10
33	2,4,9,5
34	7,3,10
35	7,8,5
36	7,4,9
37	3,8,4,5
38	3,8,9
Sheet1

Cell Formulas
Range	Formula
A8:A38	A8	=TRANSPOSE(FindSumCombinations(A1:B5,A7))
Dynamic array formulas.

 

[GlennUK]

I mean you have an extra column to aid in sorting ... you can sort a range of more than one column. Does that make sense?

 

[vladimiratanasiu]

I mean you have an extra column to aid in sorting ... you can sort a range of more than one column. Does that make sense?

I posted the table with the combinations sorted in one column. If I understand correctly, you suggest me to use as many extra columns as series of combinations are, in order to sort them. It means also to make a lot of operations manually and to split the entire content in other sections. As I mentioned before, I'm interested in process the respective data within a single column, and find a solution as much as possible to automate this operation(s). Thank you!

 

[GlennUK]

No, you do not understand correctly. I am suggesting that you have a column next to your output that calculates the number of elements per cell of output, because you said that you wanted that to be the major sorting factor. Then you refer to the output AND the new column in your formula that does the sorting.