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
73 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: 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 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

Technology Partners: 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!

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

695 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