|
|||||||||||||||||||||
|
|||||||
| Notices |
![]() |
|
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
Sum of the possible numbers in a column
I need Excel formula to find one amount who is sum of more than one number in a column.
At the given example 279 is the research amount from column B A B 1 2,251.00 2 2,136.00 3 10.00 4 279.00 56,633.00 5 13.00 6 256.00 7 12,513.00 8 504.76 9 43.62 10 31.44 11 338.48 12 82.39 13 72.21 14 87.24 15 1,090.75 16 141.29 17 208.03 18 55.56 19 71.64 The sum can be from 2 to 14 numbers. How can I be sure to find that my 279 is exactly the amount of 10+13+256. In fact this is possibility to pick up them to complete the amount. Thanks in advance |
|
#2
|
||||
|
||||
|
This is a combinations problem and can be addressed with the following macro. It takes a significant time to run as there are obviously a lot of combinations to potentially consider.
Code:
Sub Test()
Dim MyArray()
TargetValue = 279
LastRow = Cells(65536, 2).End(xlUp).Row
CombinationColumn = 2
For N = 1 To 2 ^ LastRow
MyNumber = N
ReDim MyArray(LastRow - 1)
For M = LastRow - 1 To 0 Step -1
If 2 ^ M <= MyNumber Then
MyArray(M) = True
MyNumber = MyNumber - 2 ^ M
Else
MyArray(M) = False
End If
Next M
Total = 0
For M = 0 To LastRow - 1
If MyArray(M) = True Then Total = Total + Cells(M + 1, 2)
If Total > Total Then Exit For
Next M
If Total = TargetValue Then
CombinationColumn = CombinationColumn + 1
For M = 1 To LastRow
If MyArray(M - 1) = True Then Cells(M, CombinationColumn) = "X"
Next M
End If
Next N
End Sub
__________________
Martin Eighty Twenty Spreadsheet Automation http://homepage.ntlworld.com/martin.rice1/ for all your Excel customisation and consulting needs. |
![]() |
| Bookmarks |
New topics in F1 Get the most out of Excel Formulas & Functions
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|