How to find the combination of numbers that equal to value n

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324
Hello guys
I found a very usefull code and I would like your assistance on implement it on limitating the quantity of numbers that will give the desired sum.
For instance I would like the combination of numbers limitating to 13 the set of numbers.
So I will have for each set of numbers 13 numbers as example.
I will choose the quantity of sets and also the quantity of number in each set that will give me as sum result my desired value.

Code:
Option Explicit 
Function RealEqual(A, B, Epsilon As Double)
    RealEqual = Abs(A - B) <= Epsilon
End Function
Function ExtendRslt(CurrRslt, NewVal, Separator)
    If CurrRslt = "" Then ExtendRslt = NewVal _
Else ExtendRslt = CurrRslt & Separator & NewVal
End Function
 
Sub recursiveMatch(ByVal MaxSoln As Integer, ByVal TargetVal, InArr(), _
    ByVal CurrIdx As Integer, _
    ByVal CurrTotal, ByVal Epsilon As Double, _
    ByRef Rslt(), ByVal CurrRslt As String, ByVal Separator As String)
    Dim I As Integer
    For I = CurrIdx To UBound(InArr)
        If RealEqual(CurrTotal + InArr(I), TargetVal, Epsilon) Then
            Rslt(UBound(Rslt)) = (CurrTotal + InArr(I)) _
            & Separator & Format(Now(), "hh:mm:ss") _
            & Separator & ExtendRslt(CurrRslt, I, Separator)
            If MaxSoln = 0 Then
                If UBound(Rslt) Mod 100 = 0 Then Debug.Print UBound(Rslt) & "=" & Rslt(UBound(Rslt))
            Else
                If UBound(Rslt) >= MaxSoln Then Exit Sub
            End If
            ReDim Preserve Rslt(UBound(Rslt) + 1)
        ElseIf CurrTotal + InArr(I) > TargetVal + Epsilon Then
        ElseIf CurrIdx < UBound(InArr) Then
            recursiveMatch MaxSoln, TargetVal, InArr(), I + 1, _
            CurrTotal + InArr(I), Epsilon, Rslt(), _
            ExtendRslt(CurrRslt, I, Separator), _
            Separator
            If MaxSoln <> 0 Then If UBound(Rslt) >= MaxSoln Then Exit Sub
        Else
             'we've run out of possible elements and we _
            still don 't have a match
        End If
    Next I
End Sub
 
Function ArrLen(Arr()) As Integer
    On Error Resume Next
    ArrLen = UBound(Arr) - LBound(Arr) + 1
End Function

Source:
HTML:
http://www.tushar-mehta.com/excel/templates/match_values/
 
What did you do?

You need to enable macros, select the last tab, and press the Run button.

How many numbers? 13

Total? 100

Largest number allowed? 0
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
I was checking here and there is something wrong.
I would like to find the set of 15 numbers from 01 to 25 that the sum equals to 247.
But the spreadsheet doesn't allow me to enter my total as 247 because the min allowed on your spreadsheet is 325.
But the set of numbers like below the sum is 247 I mean, exist sets of number from 01 to 25 that the sum is equal to 247, but your code is not allowing me to put as total 247.
{4,9,10,11,12,13,16,18,19,20,21,22,23,24,25}
{3,5,9,10,15,16,17,18,19,20,21,22,23,24,25}
Another thing, I need to specify a range to combine. The maxim value of a range I need to specify or I can specify the total range, e.g, 01 to 25 or any different range wanted.
With the code below I am almost there, I just need to count the array that match the quantity of numbers that I want.

Code:
[COLOR=#333333]Sub GenerateNumbers()Dim i As Integer[/COLOR]Dim TargetVal, Rslt(), InArr(), StartTime As Date, MaxSoln As Integer
    MaxSoln = Selection.Cells(1).Value
    TargetVal = Selection.Cells(2).Value
    InArr = Application.WorksheetFunction.Transpose( _
    Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Value)
    ReDim Rslt(0)
    recursiveMatch MaxSoln, TargetVal, InArr, LBound(InArr), 0, 0.00000001, _
    Rslt, "", "-"
    ReDim Preserve Rslt(UBound(Rslt) + 1)
    Selection.Offset(0, 1).Resize(ArrLen(Rslt), 1).Value = _
    Application.WorksheetFunction.Transpose(Rslt) [COLOR=#333333]End Sub[/COLOR]
 
Last edited:
Upvote 0
When I plugged that into the workbook I linked, here are some of the 939 results:

Row\Col
B​
2​
Partitions of 247 into 15 distinct numbers with a max value of 25
3​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 16 + 15 + 14 + 10 + 2 + 1​
4​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 16 + 15 + 13 + 11 + 2 + 1​
5​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 16 + 14 + 13 + 12 + 2 + 1​
6​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 16 + 15 + 14 + 9 + 3 + 1​
7​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 16 + 15 + 13 + 10 + 3 + 1​
8​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 16 + 15 + 12 + 11 + 3 + 1​
9​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 16 + 14 + 13 + 11 + 3 + 1​
10​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 15 + 14 + 13 + 12 + 3 + 1​
11​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 16 + 15 + 14 + 8 + 4 + 1​
12​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 16 + 15 + 13 + 9 + 4 + 1​
13​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 16 + 15 + 12 + 10 + 4 + 1​
14​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 16 + 14 + 13 + 10 + 4 + 1​
15​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 16 + 14 + 12 + 11 + 4 + 1​
16​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 15 + 14 + 13 + 11 + 4 + 1​
17​
25 + 24 + 23 + 22 + 21 + 20 + 19 + 18 + 16 + 15 + 14 + 13 + 12 + 4 + 1​
936​
25 + 23 + 22 + 21 + 20 + 19 + 18 + 16 + 15 + 14 + 13 + 12 + 11 + 10 + 8​
937​
24 + 23 + 22 + 21 + 20 + 19 + 18 + 17 + 15 + 14 + 13 + 12 + 11 + 10 + 8​
938​
25 + 24 + 23 + 21 + 19 + 18 + 17 + 16 + 15 + 14 + 13 + 12 + 11 + 10 + 9​
939​
25 + 24 + 22 + 21 + 20 + 18 + 17 + 16 + 15 + 14 + 13 + 12 + 11 + 10 + 9​
940​
25 + 23 + 22 + 21 + 20 + 19 + 17 + 16 + 15 + 14 + 13 + 12 + 11 + 10 + 9​
941​
24 + 23 + 22 + 21 + 20 + 19 + 18 + 16 + 15 + 14 + 13 + 12 + 11 + 10 + 9​
 
Last edited:
Upvote 0
How did you get that?
I mean, what I generated in here the code didn't respect the range 1 to 25 and on your example it does respect.
 
Upvote 0
How many numbers? 25

Total? 247

Largest number allowed? 25
 
Upvote 0
Thank you very much. Now I understood how it works. Many thanks for your assistance and help.
We can consider this topic closed/solved.
 
Upvote 0
Typo in post #15:

How many numbers? 15, not 25

Total? 247

Largest number allowed? 25


Entering 25 in the first line would indeed require a minimum value of 325 on the second. Sorry for the confusion.
 
Last edited:
Upvote 0
From top to bottom, the outputs are in colex order, meaning they are in lexical order if the order of appearance is reversed left to right. That's how the algorithm generates them.

From left to right, they are in decreasing order, which is pretty much a standard definition for a partition.

I don't have a suggestion for changing either.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top