?
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
Medium Priority
?
89 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I came across an unsolved Outlook issue and here is my solution.
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 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