• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summing up values to get particular number

P

Prashantvk123

New Member
Dear All,

Hope you are doing well,

To start with in the below mentioned data table we have different columns which indicate Customer #, Invoice #, and amount,

Customer Invoice Number Amt
A A1 154
b A2 1589.5
c A3 198.25
d A4 1982.5
a A5 587.5
b A6 -125
c A7 2599
c A8 2564
c A9 -587
f A10 266

What I need from this is, suppose we have received the payment for $ 4576 for customer C, but I don't really know which invoice it belongs to, the data given here is relatively smaller so for now i know that the payment is for Invoice number A7, A8 and A9 (summing together) but when the data is huge we really get into trouble to find out exact match.

Is there a formula where we can get the payment value and than recognize which invoices it belongs to.??
please see the attached excel for better understanding.

Hope I have explained you well, looking forward for your response.

Thank you
 

Attachments

  • Payment Predictor.xlsx
    9.1 KB · Views: 1
Prashantvk,

I was intrigued by your problem; and I found a few helpful resources.

Resource 1. There is a formula-based solution to this type of problem discussed at the following link:


It's author, @XOR LX, is a great advocate of formula-based solutions (and, frankly, an excel mastermind).

While this option is exciting from the theoretical perspective of what a formula can accomplish, you mentioned that your data set is huge -- and I'm not sure that your processor will like the calculations that are required for a large data set.

Resource 2. Solver. I had never used Excel's solver before, but your inquiry (and the comments on the blog post I mentioned above) pushed me to look into it. I found a quick crash course here on Chandoo's site:

http://chandoo.org/wp/2010/10/15/excel-solver-tutorial/
And within just a few minutes of watching the video there, I was able to apply the solver feature of excel to solve the problem you presented.

See attached -- it worked for me, and once it's set up, you can re-use it as many times as you want. (Note: I've just included a screenshot of Solver -- I think you'll have to set it up from scratch on your end, but it's worth the time if you haven't used it before.)
 

Attachments

  • prashantvk1.xlsx
    75.1 KB · Views: 6
Just wondering if we have any Macro Solutions too for this problem as my data is huge almost 50k lines in excel....

can someone please help??
 

Hi,

I did it years ago via a VBA code (Ressource 3) …
First attempt worked but as the amounts list was growing,
time necessary raised in an exponential way !
It's enough with your sample attached but with a little more data,
you will have to wait, have time for a coffee, and still wait …
226 seconds to compute a sum among 26 amounts …

I found out a trick to reduced time process but with some limitations :
works only with exact amounts (1 cent difference can't match),
needs to sort data, … But I have to find back my code.
If I well remember it, I have not achieve it, works but needs optimization.

Maybe I'm wrong, but I think Ressource 1 is the fastest process …

Chance there is another VBA way, do a serious search on web,
I'm sure you will find something …

If not, first create a more robust sample worksheet,
more data for customer C, maybe a column marker for already paid, …

Search by name could be tricky, better is an Id : customer, sales, …

Then create a new thread in VBA forum with new worksheet attached !

Edit : already a thread on same subject in VBA forum !​
 
Last edited:
You must log in or register to reply here.
Back
Top