Algorithm/calculation to find changes in number

[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:

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

<tbody>
</tbody>

**

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?

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

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

 

[RonB1111]

This approach still requires some manual effort, but at a much lower level. Assume your Header labels are in row 1; column A contains transaction # or other descriptors; and column B contains amounts. Now,

1. Add a column C with the label "Absolute Amts" in C1 and enter this formula in C2 and copy down:

=ABS(B2)

2. Now turn on autofilters and sort by column C ascending. (Exclude your ending cash balance and difference amounts from the sort by inserting a blank row and selecting only cells thru the last transaction before turning on the autofilters)

3. Add a column D with the label "Running Total" in D1 and enter this formula in D2 and copy down:

=IF(OR(SUM(B2,B1)=0,B2+B3=0),0,B2)

The amounts in column D still need a manual decision to include/exclude, but as you should see this is greatly simplified. You could highlight in some color the amounts in Col D that are to remain and then use the autofilters "filter by color" feature to filter for just these final items until you've fully identified the components of the ending balance.

 

[Arithos]

This approach still requires some manual effort, but at a much lower level. Assume your Header labels are in row 1; column A contains transaction # or other descriptors; and column B contains amounts. Now,

1. Add a column C with the label "Absolute Amts" in C1 and enter this formula in C2 and copy down:

=ABS(B2)

2. Now turn on autofilters and sort by column C ascending. (Exclude your ending cash balance and difference amounts from the sort by inserting a blank row and selecting only cells thru the last transaction before turning on the autofilters)

3. Add a column D with the label "Running Total" in D1 and enter this formula in D2 and copy down:

=IF(OR(SUM(B2,B1)=0,B2+B3=0),0,B2)

The amounts in column D still need a manual decision to include/exclude, but as you should see this is greatly simplified. You could highlight in some color the amounts in Col D that are to remain and then use the autofilters "filter by color" feature to filter for just these final items until you've fully identified the components of the ending balance.

Yes, this is the thought process I'm going for, however I was hoping to solve the operations in this kind of way.

Just typed it up, runs, but needs some adjustments. Going on your assumptions (correct ones) about the Row/Column buildup)

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)[COLOR=#ff0000]***[/COLOR]
            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

Any Ideas on how to do this? You could loop with adding two values aswell, every combination, and stop at three or four, or make it loop for all combinations of the UBound(array).

Suggestions`?


*** 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 =)

 

[RonB1111]

I'm not skilled in VBA, so hopefully another responder will see this and help you.

 

[Arithos]

I'm not skilled in VBA, so hopefully another responder will see this and help you.

Thank you for your response either way, your thought process is what i'm after, and you can display this in formulas, and yours is a start, its just easier to compare values quickly in VBA (I think) However Formulas will get me part of the way if VBA ends up beeing a dead end.

 

[RonB1111]

Here's a formula approach that solves it - perhaps you can convert this to VBA.

1. The data must start in row 4 (rows 1 thru 3 can either contain header or be blank)
2. Col C is added with this formula in C4 and copied down:

=ABS(B4)

3. Now sort the data by Col C ascending. Exclude Ending Cash Bal and Diff amt from sort
4. Col D is added with this formula in D4 and copied down:

=IF(OR(ROUNDDOWN(SUM(B4,B3),0)=0,ROUNDDOWN(B4+B5,0)=0),0,B4)

5. Col E is added with this formula in E4 and copied down:

=IF(OR(SUM(D1:D4)=0,SUM(D4:D7)=0),0,IF(OR(ROUNDDOWN(SUM(B4,B3),0)=0,ROUNDDOWN(B4+B5,0)=0),0,B4))

The items with non-zero amounts in Col E are the items that make up the ending balance. It will of course be off a small amount as a result of items that were off by decimals that were considered "matched".

Hope that helps.

 

[Arithos]

Here's a formula approach that solves it - perhaps you can convert this to VBA.

1. The data must start in row 4 (rows 1 thru 3 can either contain header or be blank)
2. Col C is added with this formula in C4 and copied down:

=ABS(B4)

3. Now sort the data by Col C ascending. Exclude Ending Cash Bal and Diff amt from sort
4. Col D is added with this formula in D4 and copied down:

=IF(OR(ROUNDDOWN(SUM(B4,B3),0)=0,ROUNDDOWN(B4+B5,0)=0),0,B4)

5. Col E is added with this formula in E4 and copied down:

=IF(OR(SUM(D1:D4)=0,SUM(D4:D7)=0),0,IF(OR(ROUNDDOWN(SUM(B4,B3),0)=0,ROUNDDOWN(B4+B5,0)=0),0,B4))

The items with non-zero amounts in Col E are the items that make up the ending balance. It will of course be off a small amount as a result of items that were off by decimals that were considered "matched".

Hope that helps.

This looks promising, I'll have a look at it first thing

 

[RonB1111]

You may also want to apply the ROUNDOWN() to the 1st 2 SUM's in the final formula. It wasn't needed for the sample data, but potentially could be helpful.