combinations of numbers that sum to a given total excel

[Rohith1324]

[COLOR=rgba(0, 0, 0, 0.75)][FONT=&quot]combinations of numbers that sum to a given total excel[/FONT][/COLOR]
[COLOR=rgba(0, 0, 0, 0.75)][FONT=&quot]I've got a column of numbers, and I'd like to find the best combination of three of these numbers. In our case, the best combination is a sum that is as close as possible to our goal number
For example, we have a goal of 100 and this column of numbers:
[/FONT][/COLOR]
15
70
36
60
30
53
37
17
0
75
100
9[COLOR=rgba(0, 0, 0, 0.75)][FONT=&quot]If I sum 30+70+0=100 this group of 3 numbers (30,70,0) is the best combination since it reaches our goal number, 100. We can also get other combinations like, 60+30+9=99 and so on with the remaining numbers.[/FONT][/COLOR]
[COLOR=rgba(0, 0, 0, 0.75)][FONT=&quot]Is there a way through Excel (or anything else if you have in mind) that can list me the best three-number combination (something like recursive sum distribution)? Can anyone help me with this?[/FONT][/COLOR]

 

[MickG]

Try this:-
NB:- List of Numbers in column "A".
NB:- Total required in "B1".
NB:- Results start "C1"
NB:- Code run from CommandButton1.

Option Explicit
[COLOR="Navy"]Dim[/COLOR] Ray()
Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] rRng [COLOR="Navy"]As[/COLOR] Range, p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] vElements, lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] vresult [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] rRng = Range("A1", Range("A1").End(xlDown))
p = 3
vElements = Application.Index(Application.Transpose(rRng), 1, 0)
ReDim vresult(1 To p)
Call CombinationsNP(vElements, CInt(p), vresult, lRow, 1, 1)
Columns("C:E").ClearContents
Range("C1").Resize(UBound(Ray, 2), 3).Value = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
 
[COLOR="Navy"]Sub[/COLOR] CombinationsNP(vElements [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] vresult [COLOR="Navy"]As[/COLOR] Variant, lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] iElement [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] iIndex [COLOR="Navy"]As[/COLOR] Integer)
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
 
[COLOR="Navy"]For[/COLOR] i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    [COLOR="Navy"]If[/COLOR] iIndex = p [COLOR="Navy"]Then[/COLOR]
       '[COLOR="Green"][B]Ater Tolerance for Total in line below.[/B][/COLOR]
        [COLOR="Navy"]If[/COLOR] Application.Sum(vresult) < [b1] + 3 And Application.Sum(vresult) > [b1] - 3 [COLOR="Navy"]Then[/COLOR]
            lRow = lRow + 1
        ReDim Preserve Ray(1 To 3, 1 To lRow)
            Ray(1, lRow) = vresult(1)
            Ray(2, lRow) = vresult(2)
            Ray(3, lRow) = vresult(3)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Else[/COLOR]
        Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] i
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick