find cells that add up to certain value

[Joshua Jacoby]

I am an accounting clerk, trying to reconcile an account. So, I have a list
of debits and credits for an account, and I'm trying to "tick and tie", or
balance, the account. For example:
A B
1 Debits Credits
2 10
3 25
4 33
5 41
6 55
7 58
8 41
9 65

My example is simple to balance since I can cross out the 25 and 33
balancing out with the 58. My problem is with an account ledger that has
500-1000 entries on it and trying to find which debits match which credits.
If anyone can help me with a fuction or macro or anything, I'd appreciate it!
For example: "what cells/values in column A equal 65"?

 

[bplumhoff]

Hello,

I suggest to look here: http://michael-schwimmer.de/vba096.htm

It is a nice beautification of Mr. Excel's winning solution of his
challenge of the month (August 2002): http://www.mrexcel.com/pc09.shtml


HTH,
Bernd

 

[Bob Umlas, Excel MVP]

the problem is that, especially with 500-1000 rows, there will likely be many
many combinations of values which will total a given result. You CAN use
Solver to do this, setting up conditions such as
A B C
10 =A1*B1
25 =A2*B2
33 =A3*B3
41 =A4*B4
55 =A5*B5
=SUM(C1:C5)
etc.
Solver would include setting B1:B5 to be integer, <=1, >=0, and setting C6 =
to your target value, like 55.
But with 1000 rows, this could churn for awhile!
HTH
Bob Umlas
Excel MVP

 

[bplumhoff]

Hello Joshua,

I suggest to look here: http://michael-schwimmer.de/vba096.htm

It is a nice beautification of Mr. Excel's winning solution of his
challenge of the month (August 2002): http://www.mrexcel.com/pc09.shtml


HTH,
Bernd