how to compare sum of a few numbers from a set against a given number?

[recyan]

Did not know where to post this, so ended up posting here.
I have a set of numbers, say (these could also be values of a column in a table)

FieldA = { 11, 16, 20, 23, 30, 37, 40, 50 }

I have a number, say 196.

I want {16,23,30,37,40,50} to be returned as these numbers add up to 196.

Note :


1) There is no possibility of two solutions with the kind of numbers that I may be using.
2) A solution using excel is also OK, though, personally I would prefer access.

Edit :
3) {16,23,30,37,40,50} - Each value is a separate record i.e. they are not in a single field, rather :
16
23
30
37
40
50

Can I request some guidance ?

Note :
Am casting the net wide.
Have posted this here http://www.access-programmers.co.uk/...d.php?t=236096 also.
In case there are any responses there, will update here.

Thanks

[June7]

Recyan, what are you up to? What is purpose of this calculation? Looks like a Project Euler type of problem.
Code would have to do a sum of every combination of the numbers in the given set until the sum matches the target. How many are possible here? Won't be easy I bet.

[recyan]

Recyan, what are you up to? What is purpose of this calculation? Looks like a Project Euler type of problem.

Nothing like that.

Code would have to do a sum of every combination of the numbers in the given set until the sum matches the target. How many are possible here?

The number off is not defined, but expect it to be a max of 50.

The practical scenario :
We receive payments from vendors.
One payment could be against n number of invoices for a particular vendor.
An invoice payment is always made in full.
I do a little bit of processing in terms of allotting the payment to various invoices.
Usually, my manager gets the payment details (i.e. against which all invoices the payment is made).
This time, he has not received the details for a particular payment.
And I have to chase him to follow up with the client to get the details & being what he is, it's a long chase.
I hate chasing people for what they are supposed to do by themselves,
And I am an automation freak.

Now finally to conclude :
I have a list of Pending Invoices with the corresponding Amounts for a Vendor.
I receive a Payment.
I want to find out all the invoices from the Pending List, whose total corresponds to the Payment received.

Thanks

[June7]

I meant how many possible combinations of the 8 values in your example set? The calculation is over my head. Now you say there could be up to 50 invoices?

Wouldn't you want to apply payment to the oldest invoices? You will go nuts trying to apply payments so that the invoices credited sum to the exact amount of the payment. What if the payment is partial and less than any outstanding invoice? And what if no combination of the invoices will sum to the payment? I have worked in accounting department and I don't remember having to do this and we were using sophisticated accounting program (Great Plains) costing like $10,000 per component. We recorded charges and payments on accounts and the difference provided net balance. Somehow the program calculated 30-, 60-, 90-day past due.

[recyan]

I meant how many possible combinations of the 8 values in your example set?

It could be any number, perhaps 1 or 2 or all of them, though, in case of 1, I should be able to visually detect it.

Wouldn't you want to apply payment to the oldest invoices? You will go nuts trying to apply payments so that the invoices credited sum to the exact amount of the payment.

The payments received, are not necessarily based on FIFO. If there is some issue with a particular invoice/s, that / those might be skipped.

What if the payment is partial and less than any outstanding invoice? And what if no combination of the invoices will sum to the payment?

This possibility is not there. Any invoice paid is paid in full or not paid at all.
And the combination will always sum to the payment.


Pls Note : For a change, my manager, managed to get the invoice details for the payment received. So at a practical level, my work is moving ahead.

But academically, I would still be interested in some insights. I now have a bone in my mouth. Neither can I chew it, nor can I swallow, nor leave it.

Thanks

Edit : Not sure, but I think use of combinations & permutation might help with the logic.

[June7]

Yes, I was looking at some sites last night about combinations & permutations - still over my head, never any good at advanced statistical math. But I think with 8 items the possible combinations were at least 255.

If a payment would always be the exact amount of an invoice, no problem, but if payment can be a combined remittance to cover multiple invoices, that is tricky.

I think a solution will involve array variable and looping structure. However, making this dynamic for any size set of invoices escapes me. This might get you a starting point http://www.mrexcel.com/forum/excel-q...mutations.html

[recyan]

Thanks June.
Will have a go asap & post back if successful.
Once again Thanks

[recyan]

Update :
In
http://www.access-programmers.co.uk/....php?p=1204886
Post 7 - by lagbolt - the attached db.

Thanks

[June7]

Just one more thought - are you sure there could not be more than one invoice of the same amount in the set?

[recyan]

No June, That problem is not there.

Thanks