0 votes
ago in VBA by Beginner (124 points)

Note the following data:

Are there any VBA formulas (or excel formulas) that can change the last column to this?

The last column is the receipt voucher and explains the invoice amount [i.e customer A invoice was 150; it was covered by two payments: voucher 105 (100) and voucher 106 (50) ].

ago by Skilled (306 points)
Can you upload sample of your workbook ..? and explain the logic behind how to get the results in that way.
ago by Beginner (100 points)
What is the logic used to associated voucher 105 and 106 with voucher 100?

PS: amount of 106 is 100, not 50. What happens with the other 50?

What is the value in the 'ledger' column?  Is that the current outstanding balance for the customer?

Why is payment 109 associated with Invoice 103 instead of payment 105?

What is the purpose of the column in question? Are you implying that payments can be applied to specific invoices, rather than to the current outstanding balance? In that case the info would have to be input rather than generated automatically.

For ease of manual checking I would sort by Customer, date, doc type so that cust records are not mixed.

Please do provide a better example, with explainations of the reason for applying specific payments to specific invoices.

1 Answer

+2 votes
ago by Beginner (204 points)
There is neither a special Excel formula nor a special VBA formula that will do what you want. The solution requires an algorithm, data structures and business logic.

The business logic would have to be defined first, as we have no way of knowing whether this is an ad hoc audit (where column G starts as blank and the goal is to "reconcile" the ledger), or if this is an on-going book of accounts.

If it is an audit, the algorithm might be simpler: just sort by Customer and Date and, for each customer, build a list of voucher strings until done. A couple of arrays and several pointers might be the data structures.

If, however, this is an on-going book of accounts, you need a more sophisticated algorithm and more complex data structures. For instance, when entries are made on 6-Jan, the value of G2 would only be 105. On the next day, 7-Jan, the algorithm would have to know that the payment would have to first be applied to G2 and the remainder to G4.

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts as your contributions grow. The more points you earn, the better the prizes.

Getting Started

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

Top Users Mar 2019
  1. YasserKhalil

    306 Points

  2. ParserMonster

    204 Points

  3. Alberto Semat

    186 Points

  4. danmcg

    127 Points

  5. Abdan

    124 Points

Prizes for March 2019
Terms and Conditions
1st place (1) 1-year MyExcelOnline Academy Membership
(2) My First Add-in Dev Pack & Course
(3) AutoMacro: VBA Code Generator (Developer)
(4) VBA Cheat Sheet Bundle
(5) $35 Amazon Gift Card
2nd place (1) My First Add-in Dev Pack & Course
(2) 101 Most Popular Excel Formulas E-Book
(3) VBA Cheat Sheet Bundle
(4) $25 Amazon Gift Card
3rd place (1) Mouse to Macro
(2) VBA Cheat Sheet Bundle
(3) $15 Amazon Gift Card