When a customer sends you a check for $1,173.76 with no backup then you match
it the best you can. Been there and done that. They will need all possible
solutions because they will want to match to the oldest stuff first.

This kind of code is also very handy for doing year end working papers where
you need to reconcile the ending amount of a Balance Sheet account. Usually
you can match off the vast majority of the debits and credits but very often
you end up with a few entries that (because of reversels, reclassifications
and just plain weirdness) don't match easily. That is another place where
this kind of thing thing is handy.
--
HTH...

Jim Thomlinson


"Ron Coderre" wrote:

Solver isn't a panacea....It's just a nice shortcut for relatively simple
situations without having to find or write code. However, if solver finds
one acceptable solution....couldn't we just create another "flag" field to
prevent the same value from being used more than once?

Regarding professional accounting/financial environments, I would hope that
proper internal controls would prevent the situation where a large number of
invoices/checks/whatever would have to be matched (trial and error) against
an amount. Of course there's always the customer who sends a massive check
paying some unknown combination of invoices. Consequently, for those
instances, a phone call to the payee should clear up the confusion
definitively. You wouldn't want to just guess, right?

If a large, multi-solution, iteratave approach cannot be avoided
though....You're right, a vba program would be the way to go.


***********
Regards,
Ron

XL2002, WinXP-Pro


"Bill Martin" wrote:

I do like your Solver approach -- I hadn't thought of that. Given that this is
an Accounting problem though, how would one get Solver to identify multiple
solutions to the problem when they exist? If you're trying to match invoices
you'd like to know that you're matching them correctly -- not just *possibly*
correctly. Which requires a person to stare at *all* the various possible
solutions and decide which one is most likely given some knowledge of the
customers involved and what they've ordered in the past, etc.

For example, take the list of 10 values that Dza provided and use them all twice
to make 20 entries. Now there are 8 valid solutions, but Solver only seems to
find one and stops.

Personally, I think you need VBA for this problem but I'm open to education...

Bill
----------------------------------
Ron Coderre wrote:
Have you tried using Excel Solver

First a little prep work....

A1:A1 (your list of values)
B1:B10 (leave blank)
C1: =A1*B1
(copy that fomula down through C10

C11: =SUM(C1:C10)

Now to use Solver....
ToolsSolver
Set Cell: C11
Equal to the Value of: 1173.76
By Changing Cells: B1:B10
Subject to the Constraints....
(click the add button and constrain B1:B10 to Binary)
Click [OK]
Click [Solve]

Excel will toggle cells B1:B10 between 1 and 0 until it comes up with a
combination that sums to 1,173.76

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Dave O" wrote:


The answer is:
$725.90
$240.16
$207.70

I've written a program that applies a brute force approach to the task-
it checks every possible combination of the "pool" of numbers to arrive
at the target total. The brute force idea works for comparatively
small pools, but since the number of possible combinations doubles with
each additional pool member the processing time increases
commensurately. One poster to this newsgroup wanted to process a list
of 100 numbers, which amounts to
1,267,650,600,228,230,000,000,000,000,000 possible combinations and
would require the resources of a major government (or maybe just the
NSA) to process.

How many of these do you have? I don't mind doing a few for you.