• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

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

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
Satech Jubara
Asked:
Satech Jubara
  • 6
  • 4
1 Solution
 
als315Commented:
Can it be done in VBA Macro?
0
 
Satech JubaraAuthor Commented:
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
 
Satech JubaraAuthor Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
als315Commented:
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
 
Satech JubaraAuthor Commented:
Dear ,

Please see the attached
0
 
Satech JubaraAuthor Commented:
could you please send me the code ???
0
 
als315Commented:
Sorry, nothing is attached. You should select file ("Attach file") and then press "Upload File"
0
 
Satech JubaraAuthor Commented:
Dear Alexey ,

please see my attached file .

Your cooperation is highly appreciated :)

Thanks , once again

Ala'a Khalil
My-case.xlsx
0
 
als315Commented:
Look at sample. Allow macros and press Calculate button
My-case.xlsm
1
 
Satech JubaraAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now