Solved

by excel How can I distribute the payment  based on the due date of  invoices " Oldest to newest "

Posted on 2016-10-14
10
68 Views
Last Modified: 2016-10-16
Dear Sir ,

I have a collection of payments from several clients,as well many of  invoices for them , I have summed the payments for each client to be as one payment  in order to cover several Invoices .

My Question now , by excel How can I distribute the payment  based on the due date of  invoices " Oldest to newest " to settle it ,Knowing that maybe the payment closed  full or part of the bill.



My Case

1-Payment
Customer Name    Payment
A                                  5,000$
B                                  3,000$

2-Invoices

Customer Name    Invoice No     Invoice Amount           Bill Date                 Solution /  I looking for it  
A                                          5                   3,000$                      Jan 2016                                  3,000
A                                          6                   2,300$                     Feb 2016                                  2,000

So, please note regarding invoice 5 ,300$ not taken  because the payment from customer A was only 5,000$ so there is remaining balance from invoice 5 =300$ will settle in next payment

Your cooperation is highly appreciated

Best Regards
Ala'a Khalil
0
Comment
Question by:Satech Jubara
  • 6
  • 4
10 Comments
 
LVL 40

Expert Comment

by:als315
ID: 41844987
Can it be done in VBA Macro?
0
 

Author Comment

by:Satech Jubara
ID: 41845224
Dear Als315,

Frankly I don't understand how i can d , i have enclosed my case in excel , could you please help me how i can do it

Thanks
0
 

Author Comment

by:Satech Jubara
ID: 41845226
Dear Als315,

Frankly I don't understand how i can d , i have enclosed my case in excel , could you please help me how i can do it

Thanks
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 40

Expert Comment

by:als315
ID: 41845409
We can do it in macro, but in this case it will be xlsm file and you should allow macros. Can you upload excel file with some sample data?
1
 

Author Comment

by:Satech Jubara
ID: 41845536
Dear ,

Please see the attached
0
 

Author Comment

by:Satech Jubara
ID: 41845635
could you please send me the code ???
0
 
LVL 40

Expert Comment

by:als315
ID: 41845767
Sorry, nothing is attached. You should select file ("Attach file") and then press "Upload File"
0
 

Author Comment

by:Satech Jubara
ID: 41845784
Dear Alexey ,

please see my attached file .

Your cooperation is highly appreciated :)

Thanks , once again

Ala'a Khalil
My-case.xlsx
0
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 41845843
Look at sample. Allow macros and press Calculate button
My-case.xlsm
1
 

Author Closing Comment

by:Satech Jubara
ID: 41845884
Dear als315

Yes great , Thank you so much my friend, this sheet will help me to solve many cases related with account receivable balances

Thanks , once again  

Thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question