Use formula to find potential sum out of a group of numbers

[Tosborn]

Hey Mr. Excelers,

I work at a bank. Very often I need to find a variance (specified value) out of a large range of numbers. Say for example I have a bunch of transactions:

[TABLE="width: 87"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]110.00
[/TD]
[/TR]
[TR]
[TD="align: right"]250.00
[/TD]
[/TR]
[TR]
[TD="align: right"]156,875.00
[/TD]
[/TR]
[TR]
[TD="align: right"]2.00[/TD]
[/TR]
[TR]
[TD="align: right"]5,566.27[/TD]
[/TR]
[TR]
[TD="align: right"]69.80[/TD]
[/TR]
[TR]
[TD="align: right"]15,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]189,987.00[/TD]
[/TR]
[TR]
[TD="align: right"]58,999.00[/TD]
[/TR]
[TR]
[TD="align: right"]68.77[/TD]
[/TR]
</tbody>[/TABLE]

I need to find out if I can sum any of these numbers to get 249,054.77.

In this case it is easy as I know it is the last 3 numbers that add up to make 249,054.77. That is, 189,987.00 + 58,999.00 + 68.77 = 249,054.77. Therefore I can make 249,054.77 by a simple =sum(A8:A10)

However, when reconciling large accounts sometimes this list is much larger and it is often a large task to find out which of the number will equal our desired value. Is there a formula (possibly macro) that can tell me that the three numbers to make up 249,054.77 are located in cells A8, A9 & A10?

Many thanks,

Tim

 

[Aladin Akyurek]

Name the MrExcel Logo - Answer to Challenge of the Month

 

[Tosborn]

Wow, thank you Aladin. That's amazing.

 

[Tosborn]

I tried to copy paste the data & the solution in VBA but can't get it to work.

I copy pasted the code into 2 modules in VBA. When I go to run the macro it says there are 4 different ones to run:

Challenge
COPY_SOLUTIONS
RESUME_Challenge
RESUME_LAST_SOLUTION

I know i'm a bit of a VBA dunce, where am I going wrong?

Thanks,
Tim

 

[wideboydixon]

For n different amounts, there are 2^n-1 possible combinations. So if you get up to, say, 20 amounts then you have 1048575 combinations. Some of these can be optimised out when you exceed the target figure but even so it's a lot of processing. Having said that, I did just knock this together:

Book1
	A	B	C
1	Values	Find Target	Result
2	110.00	190305.77	$A$3, $A$9, $A$11
3	250.00	360.00	$A$2, $A$3
4	156875.00	426927.84	$A$2, $A$3, $A$4, $A$5, $A$6, $A$7, $A$8, $A$9, $A$10, $A$11
5	2.00	112.00	$A$2, $A$5
6	5566.27	352497.04	$A$4, $A$6, $A$9, $A$11
7	69.80
8	15000.00
9	189987.00
10	58999.00
11	68.77
Sheet1

Cell Formulas
Range	Formula
C2	=FindTargetValue($A$2:$A$11,$B2)

Private counters() As Long
Public Function FindTargetValue(searchRange As Range, targetValue As Double) As String

Dim valueCount As Long

For valueCount = 1 To searchRange.Count
    FindTargetValue = TryCombination(searchRange, targetValue, valueCount)
    If FindTargetValue <> "" Then Exit For
Next valueCount

End Function
Private Function TryCombination(searchRange As Range, targetValue As Double, valueCount As Long) As String

Dim i As Long
Dim j As Long
Dim k As Long
Dim thisValue As Double

ReDim counters(valueCount - 1) As Long
For i = 0 To valueCount - 1
    counters(i) = i + 1
Next i

Do
    thisValue = 0
    k = valueCount - 1
    For i = 0 To valueCount - 1
        thisValue = thisValue + searchRange(counters(i)).Value
        If Round(thisValue, 2) > Round(targetValue, 2) Then
            k = i
            Exit For
        End If
    Next i
    If Round(thisValue, 2) = Round(targetValue, 2) Then
        TryCombination = searchRange(counters(0)).Address
        For i = 1 To valueCount - 1
            TryCombination = TryCombination & ", " & searchRange(counters(i)).Address
        Next i
        Exit Function
    End If
    i = k
    Do
        counters(i) = counters(i) + 1
        If counters(i) > searchRange.Count - (valueCount - 1 - i) Then
            i = i - 1
        Else
            For j = i + 1 To valueCount - 1
                counters(j) = counters(i) + j - i
            Next j
            Exit Do
        End If
    Loop Until i < 0
Loop Until i < 0

End Function

Note that I had to use Round() since there may be issues with floating point arithmetic. Let me know if that's any good for you.

WBD

 

[Tosborn]

Thank you wideboydixon just tried that, really works!!

Just when my friend was telling me it can't be done hahaa...... Anything is possible with excel!

Tim

 

[Tosborn]

Hey WBD,

Tried that on some bigger data sets today and it crashes excel! I opened up task manager, looks like the CPU gets a sudden spike and then gives up. It's not that the system is too slow it's just it probably can't handle such a large spike of activity all at once.

Any ideas? Maybe we can slow excel down at all?

Thanks,
Tim

 

[wideboydixon]

I can believe that. As I stated in my original post, large data sets means millions of different combinations. How many numbers are in your source range when it crashes?

WBD

 

[Aladin Akyurek]

I can believe that. As I stated in my original post, large data sets means millions of different combinations. How many numbers are in your source range when it crashes?

WBD

Would you care to help the OP to run the IOANNIS code also?

 

[Aladin Akyurek]

Try to also locate the contributions from Tushar Mehta (tusharm) on the matter...