Brilliant suggestion needed, Trying to create algorithm to solve a Balance(economic) issue.

[Arithos]

First problem i post myself, and hoping some brilliant minds can help me out.
I’m doing a cash balance, and need an algorithm/macro to find what sums equal to the value in question. And what is needed is the values that adds up to the change in the balance, meaning, in the Buildup below, what values in section 2. Makes the changes equal to section 3. (ending balance)

An easy way to do this, is see what numbers in the balance equals the Ending balance. **(See last part of post for further explaining. )


Buildup of data:

1. One beginning cash balance, may be zero. (makes it easier)
2. Long string/column of data with values +/- (Section 2)(Many values, may differ)
3. One ending cash balance. (Section 3)

Three different possibilities in sorting section 2.

1. One number is equal to another, hence they cancel eachother out. (Trans: 31, 32)
2. A number has no counterpart, e.g -100, has no +100 to cancel it out. (Trans: 1)
3. A number has many counterpars, e.g -/+100, is cancelled out by -/+10, -/+10 and -/+80

As an extra frustration, Decimals might not be equal, but the Rounded numbers should be the same. (Imprecise I know, but its what I have to work with.)


That will be the buildup, and three different possibilities in the “Array/Column” section 2. from the buildup of the data. The result should be – Beginning balance, +/- the values remaining from section 2 Equals the value in section 3.

Am I making myself clear?

Recap of needs: I need a macro that figures out what transaction# adds up to the Difference (Data at the bottom)

What I am looking for, is suggestions on how to approach this, If a number of a positive value has its negative counterpart I know how to exclude them. Its when a number

Datasample:

[TABLE="class: cms_table, width: 231"]
<tbody>[TR]
[TD]Beginning balance[/TD]
[TD="align: right"]1368869,01[/TD]
[/TR]
[TR]
[TD]Transaction number[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]-486970,45[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]413924,88[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]-2416321,7[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]-1491107,7[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]2416321,74[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1491107,73[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]4489910,72[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]210665,77[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]-210665,77[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]-228539,82[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]247220,8[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]228539,82[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]-1766315,8[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]712271,19[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]-1368869[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]-4489910,7[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]1766315,8[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]408379,78[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]-247220,8[/TD]
[/TR]
[TR]
[TD="align: right"]21[/TD]
[TD="align: right"]-670119[/TD]
[/TR]
[TR]
[TD="align: right"]22[/TD]
[TD="align: right"]-712271,19[/TD]
[/TR]
[TR]
[TD="align: right"]23[/TD]
[TD="align: right"]3269343,93[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD="align: right"]-3269343,9[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]2622896,89[/TD]
[/TR]
[TR]
[TD="align: right"]26[/TD]
[TD="align: right"]-408379,78[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD="align: right"]-2622896,9[/TD]
[/TR]
[TR]
[TD="align: right"]28[/TD]
[TD="align: right"]-25,61[/TD]
[/TR]
[TR]
[TD="align: right"]29[/TD]
[TD="align: right"]670119[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="align: right"]1268203,93[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD="align: right"]1268203,93[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD="align: right"]-1268203,93[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD="align: right"]133483,63[/TD]
[/TR]
[TR]
[TD="align: right"]34[/TD]
[TD="align: right"]25,62[/TD]
[/TR]
[TR]
[TD="align: right"]35[/TD]
[TD="align: right"]-3303814,9[/TD]
[/TR]
[TR]
[TD="align: right"]36[/TD]
[TD="align: right"]865789,37[/TD]
[/TR]
[TR]
[TD="align: right"]37[/TD]
[TD="align: right"]-1268203,93[/TD]
[/TR]
[TR]
[TD="align: right"]38[/TD]
[TD="align: right"]3303814,87[/TD]
[/TR]
[TR]
[TD]Ending cash balance[/TD]
[TD="align: right"]926227,43[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Difference[/TD]
[TD="align: right"]-442641,58[/TD]
[/TR]
[TR]
[TD="colspan: 2"],Difference=Endingbal-Beginningbal[/TD]
[/TR]
</tbody>[/TABLE]



**

In my example, the result should be the following:

Beginning cash balance is cancelled out by transaction 16, (this is one transaction here, but might be more than one)


The ending balance is a combination of transactions: 1+2+33+36 = 926227,43 (ending balance)
Meaning all other transactions in between has "cancelled" each-other.


What would be the best approach?
- Find what numbers adds up to the ending balance?
- Exclude all numbers that can be excluded, then look at what you are left with and compare to ending/start?

My own suggestion is using an Array, and populating it, then comparing. Using just two "loop-within-loop" but think I will expand it up to 5-6, since a transaction might contain up to 5-6 sums "explaining" another.

The below is just something i but together to show my thought process thus far.

Sub ArrayIt()

Dim Lrow%
Lrow = ActiveWorkbook.Sheets(1).Range("A:A").Find("Ending cash balance").Row

Dim i%, X%, j%

Dim CBal()
ReDim CBal(1 To Lrow - 3, 1 To 2)
For i = 1 To UBound(CBal)
    'Loop it into an Array for calculations
    CBal(i, 1) = Cells(i + 2, 1).Value
    CBal(i, 2) = Cells(i + 2, 2).Value
Next i


'This section should leave me with only values that does not have a counterpart
'I need to specify the values as absolute, and exclude some differences in decimals
For X = 2 To UBound(CBal)
    For j = 1 To UBound(CBal)
        If CBal(X, 2) = CBal(j, 2) Then ' Maybe:  If CBal(X, 2) + CBal(j, 2) = 0 then (Before Commas only)***
            If X <> j Then
                CBal(X, 2) = "Excluded ref " & X & j 'just adding some reference to know which trans, canceled which
                CBal(X, 1) = "Excluded ref " & X & j
                CBal(j, 2) = "Excluded ref " & X & j
            End If
        End If
    Next j
Next X

'Then continue doint this kind of thing, to "relieve" the array of duplicates

'After I'm satisfied/got as far as I could, I'll loop the values back out
End Sub

*** I will replace "," and all after it with "", with some abreviation of Instr(CBal(x,x).value, ",") Mid formula, and Replace. Do not need help with this particular part of the code, its more the Thought process of "getting all possible outcomes covered" where the guidance is needed =)

There are many pit-falls here, all suggestions are welcome!


If I need to explain further or have been unclear, please let me know.

 

[VoG]

This may help you Combination of numbers that sum or match a target value

 

[Arithos]

This may help you Combination of numbers that sum or match a target value

Thank you, I will look into this

 

[Arithos]

This may help you Combination of numbers that sum or match a target value

Hmm, this works in a way, only it gives me solutions with all the sums, because the closing balance is explained by, beginning balance and all the numbers. So it returns the solution as all the numbers. I would like to incorporate some steps:
- If a the sum of numbers equals zero, they should be removed.
- The result should be the solution using the fewest transactions.

Is this possible?

 

[Arithos]

This could be solved easily before running the other macro:
- If a the sum of numbers equals zero, they should be removed.

Could I reprogram the macro already working sub from the link provided. "Combination of numbers that sum or match a target value"'
And make it return the solution with the fewest number of "observations"?