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]

What is FindSumCombinations?

 

[vladimiratanasiu]

What is FindSumCombinations?

It's a custom function, based on the macro from Find all combinations of numbers that equal a given sum in Excel. I used it to find all combinations of the numbers from the range A1:B5 that can be added up in order to get the value from cell A7, and the respective list was automatically generated in the range A8:A38. Now, I need a solution to group / organize in ascending order the combinations as they are in the column E8:E38, based on the number of elements from each cell (in our case 3, 4 and 5 ones), and and if possible the values to be sorted from 1 to 9 in each cell. If it's too difficult to get results from E8:E38, they can be sorted like in the range C8:C38, keeping only the ascending order for the number of the elements.

Thank you!

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

 

[GlennUK]

Why don't you do the required sorting in the macro, before it returns the result?

 

[vladimiratanasiu]

Why don't you do the required sorting in the macro, before it returns the result?

To be honest, I'm not very skilful in adjusting the complex macros. That's why, I'd be grateful if you / anyone else could help me with this / other solution, in order to solve my problem.
Thank you!

 

[vladimiratanasiu]

This is the code I used in the custom function FindSumCombinations. If someone could adjust it to my needs, I will be very thankful.

Option Explicit

Public Function FindSumCombinations(rngNumbers As Range, lTargetSum As Long)
 Dim arNumbers() As Long, part() As Long
 Dim arRes() As String
 Dim indI As Long
 Dim cellCurr As Range
 
 ReDim arRes(0)
 If rngNumbers.Count > 1 Then
  ReDim arNumbers(rngNumbers.Count - 1)
 
  indI = 0
  For Each cellCurr In rngNumbers
   arNumbers(indI) = CLng(cellCurr.Value)
   indI = indI + 1
  Next cellCurr
  Call SumUpRecursiveCombinations(arNumbers, lTargetSum, part(), arRes())
 End If
 ReDim Preserve arRes(0 To UBound(arRes) - 1)
 FindSumCombinations = arRes
End Function
 

Private Sub SumUpRecursiveCombinations(Numbers() As Long, target As Long, part() As Long, ByRef arRes() As String)
 Dim s As Long, i As Long, j As Long, num As Long, indRes As Long
 Dim remaining() As Long, partRec() As Long
 s = SumArray(part)
 
 If s = target Then
  indRes = UBound(arRes)
  ReDim Preserve arRes(0 To indRes + 1)
  arRes(indRes) = ArrayToString(part)
 End If
 If s > target Then Exit Sub

 If (Not Not Numbers) <> 0 Then
  For i = 0 To UBound(Numbers)
   Erase remaining()
   num = Numbers(i)
   For j = i + 1 To UBound(Numbers)
    AddToArray remaining, Numbers(j)
   Next j
   Erase partRec()
   CopyArray partRec, part
   AddToArray partRec, num
   SumUpRecursiveCombinations remaining, target, partRec, arRes
  Next i
 End If
End Sub


Private Function ArrayToString(x() As Long) As String
 Dim n As Long, result As String
 result = x(n)
 For n = LBound(x) + 1 To UBound(x)
  result = result & "," & x(n)
 Next n
 ArrayToString = result
End Function


Private Function SumArray(x() As Long) As Long
 Dim n As Long
 SumArray = 0
 If (Not Not x) <> 0 Then
  For n = LBound(x) To UBound(x)
   SumArray = SumArray + x(n)
  Next n
 End If
End Function

 

[GlennUK]

Can you have a helper column, and have an additional area for sorted results? Actually, does your version of Excel have the SORT function? If so, does putting that around your function work?

 

[vladimiratanasiu]

Can you have a helper column, and have an additional area for sorted results? Actually, does your version of Excel have the SORT function? If so, does putting that around your function work?

Yes, I used the SORT function, but the results are not OK. As you can see, after sorting, the combinations are not arranged in distinct groups with 3, 4 and 5 elements, but mixed together. On the other hand, the numerical content from cells keeps the ascending order only with the first two digits (1,1; 1,2; 1,3 etc.), but fails in the case of the whole combination (e.g. 1,2,8,4,5 vs. 1,2,4,5,8). Maybe there are other formulas more complex, that can take into account my criteria, but I don't know them. Any other working solution will be very appreciated.
Thank you!

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

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

 

[GlennUK]

What about having a help column, that calculates number of elements?

 

[vladimiratanasiu]

What about having a help column, that calculates number of elements?

Thank you for the answer! However, how could help me your last indication, considering I need all elements to be shown clearly as numerical values, organized in specific groups? Can you show me the mentioned solution in a specific table / an example, with formula/s and distinctive groups as required? Thank you!