multi-summing for a check register.

[dbmathis]

I hope there is someone out there that can help me on this. This is a last ditch effort.

I have a column of blank cells m11 throught m22. Each cell possibly holds a value of a check that has been written but has not cleared.

One cell on my sheet contains a difference of my previous bank balance and my new bank balance after an unknown number of checks have cleared.

Say for Example

-----

My bank balance was $500.00 dollars on 9/22/2002

Checks that have not cleared =

m11 = $5.00
m15 = $15.00
m16 = $35.00
m20 = $6.00

-----

My account balance is now $489 dollars on 9/28/2002

$11.00 dollars is the difference.

It's obvious that checks $6.00 and 5.00 are the checks that have cleared!

What I am trying to do is find out a simple way to sum every number that is listed in m11:m22 in every sum combination possible.
Then display the combonation of numbers that = the difference when summed together

I know the long way to do this...

In one cell put

if(m11=sum(m11:m22),m11,"")

if(m11+m12=sum(m11:m22),m11,"")
if(m11+m13=sum(m11:m22),m11,"")

etc....

Then the next cell put
if(m11=sum(m11:m22),m12,"")

if(m11+m12=sum(m11:m22),m12,"")
if(m11+m13=sum(m11:m22),m12,"")

and so on....

But this would require an excessive amount of IF statments and named formulas.

Is there a simple VBA script to get around this?

Thankz in advance for any help.

David Mathis.

 

[PaddyD]

"Is there a simple VBA script to get around this? "

Simple, no. Do-able, yes. This topic was the subject of the recent MrExcel challenge - see:

http://www.mrexcel.com/board/viewtopic.php?topic=16914&forum=12&0

make sure to check out the link to the MS site oin Jay Petrulis's post & see if the method described by Tushar helps.

post back if you need more...

Paddy

 

[dbmathis]

This really confused me more than it helped me? That seems to be related to what I am talking about..

 

[PaddyD]

OK - a step by step guide using the Solver method outlined by Tushar (i.e. non-VBA):

Take a look at the following:

Book6
	A	B	C	D	E
1	Values	Matches
2	5	1	Starting#	500
3	15	0	Current#	489
4	35	0	Target#	11
5	6	1
6		11
7
Sheet2

I've done this for a smaller range than in your question, but the method generalises. In a blank sheet, follow the steps below (we'll re-create the above from scratch). You should then be able to apply them to your situation.

1) In a new sheet, enter the data as I've got it in col A.
2) In cell B6 enter the formula:

=SUMPRODUCT(A2:A5,B2:B5)
This sets up your source data for Solver.

What we are going to do is get Solver to work out which of the values in a2:a5, when multiplied by 1 and added together, equal your target value (11 in this case). If you want to see how the sumproduct helps here, check out:
http://mrexcel.com/board/viewtopic.php?topic=20900&forum=2

3) Work out your target value. I've done this in col D.

4) select cell B6 (or whichever has your sumproduct in). go to tools | solver.
5) set up the solver options you need:

- 'Target cell': B6
- 'Equal to' : select 'Value of & enter your target value
- 'By changing cells': highlight the range b2:b5. (In general, you'll need to highlight a set of blank cells that are the same size as your source values).
- 'Subject to constraints': click the add button. Set 'Cell reference' to b2:b5 (i.e. the range of blank cells). Click the drop-down box & select 'bin' (for binary). Click OK
- click the 'Options' button. Select the "assume linear model' box. Click OK
- Click 'Solve'

Check the results - numbers that add to the target will have a 1 next to them in col B.

NOTE: if there are multiple sets of values that add to the target, this method will only identify one of them, but what do you expect.


Paddy
This message was edited by PaddyD on 2002-09-23 17:31

 

[lawmgomez]

This solution is great! There is only one problem. When there are two or more solutions to this problem, solver only gives us one. Any ideas on how you can see multiple solutions through solver?

 

[Corni]

After a first solution is found by Solver, if you want to see if the solutions is unique or not, you could add a constraint, changing the binary value from 1 to 0 for one of the amount part of the solution. If you do not get an alternative solution, that means that the initial solution is unique.

 

[tusharm]

Why not get the correct information from the bank?

These days the information is available through web access or automated phone access.

But, if you must guess, check out
Find a set of amounts that match a target value
http://www.tushar-mehta.com/excel/templates/match_values/index.html

I hope there is someone out there that can help me on this. This is a last ditch effort.

I have a column of blank cells m11 throught m22. Each cell possibly holds a value of a check that has been written but has not cleared.

One cell on my sheet contains a difference of my previous bank balance and my new bank balance after an unknown number of checks have cleared.

Say for Example

-----

My bank balance was $500.00 dollars on 9/22/2002

Checks that have not cleared =

m11 = $5.00
m15 = $15.00
m16 = $35.00
m20 = $6.00

-----

My account balance is now $489 dollars on 9/28/2002

$11.00 dollars is the difference.

It's obvious that checks $6.00 and 5.00 are the checks that have cleared!

What I am trying to do is find out a simple way to sum every number that is listed in m11:m22 in every sum combination possible.
Then display the combonation of numbers that = the difference when summed together

I know the long way to do this...

In one cell put

if(m11=sum(m11:m22),m11,"")

if(m11+m12=sum(m11:m22),m11,"")
if(m11+m13=sum(m11:m22),m11,"")

etc....

Then the next cell put
if(m11=sum(m11:m22),m12,"")

if(m11+m12=sum(m11:m22),m12,"")
if(m11+m13=sum(m11:m22),m12,"")

and so on....

But this would require an excessive amount of IF statments and named formulas.

Is there a simple VBA script to get around this?

Thankz in advance for any help.

David Mathis.