0 votes
in VBA by Beginner (26 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) ].

by Skilled (401 points)
Can you upload sample of your workbook ..? and explain the logic behind how to get the results in that way.
by Beginner (0 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
by Skilled (265 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 based on the quality of your questions and/or answers. Users that ask great questions, may be given complimentary gift cards or training material.

Getting Started

So, why don't you join us? It really is a neat way to reward the most dedicated members in our VBA and Python community.

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