Help: Advance payment matching formula

[David Lam]

I currently have a problem that is very similar to this postings but abit more harder.

http://chandoo.org/wp/2014/06/06/matching-transactions-using-formulas-accounting/

Could you please look at the attached file and help me? I have tried looking and thinking of ways to approach this differently for a week however I’m out of juice mentally. To summarize my problem:

1. There is 7 columns; Business Name, Payment#, Document#, Invoice Amount, Payment Amount, Grand Total, Net Due, Payment Date and Days Late
2. A payment can be for more than 1 invoice
3. When a payment is made and it is matched to a invoice a payment# is allocated (you would see them paired up there could be a remaining or nil balance)
4. If the invoice amount is positive than the document number is a invoice#
5. If the payment amount is negative than the document number is a receipt number#
3. GOAL I am to split the payment against the invoices and allocate a payment date next to each line. It will look like this

4. There is 50,000 rows, I have attached just a sample to simplify my problem

The sample answer should look like this.

Looking at the table I could easily manually move them but for 50,000 rows; I will die. Is there a way to apply this on a mass level?

 

[NARAYANK991]

Hi ,

Can you confirm whether there will be two identical amounts having the same payment number ?

Narayan

 

[David Lam]

Hello Narayan,

The payment number would not have 2 identical amounts but the document number will have different values allocated.

Example:

Payment number is 105515226 but there is 4 different receipt numbers (1431831, 1431832 ,1450795 & 1452470)

Thank you for looking at this

 

[NARAYANK991]

Hi ,

This is a problem.

If one payment number could not have two identical amounts , then it would have been very easy to match the received amount to the invoice amount.

I am at a loss to understand how we can relate a received amount to an invoice amount. Do you have any suggestions ?

Narayan

 

[David Lam]

I had the same thought the payment number is my biggest let down. Looking at the data I take it the reason why there is 1 payment number is the accounting system/person grouped several invoices and receipts together resulting zero balance or what is left outstanding. I could try to solve a majority of them if the grouping gave a zero balance and get some admin staff to help me manually allocate the rest. Right now I could do this and give a estimate

={IF(B2:B3=B3,SUM(D2:D3)+VLOOKUP(B3,B1:E3,4,0))}

If its true than I copy the invoice amount into payment column and the false I will have to manually investigate

 

[NARAYANK991]

Hi ,

This formula will tell you whether there is a difference or not :

=SUMPRODUCT(($B$2:$B$22 = B2) * ($D$2:$E$22))

Enter this in any unused column , say K2 , and copy down.

If it is 0 , then there is no difference.

Narayan

 

[David Lam]

Ah your formula is ace has similar principles of what I'm trying to do. I did some testing is there a reason why the 105515226 paymentno batch giving -1.42109E-14? Disregard the 7 on top I was checking the results.

Thank you Narayan, is there anyway for me to move the dates into the payment dates?

 

[David Lam]

I would use the =max date for the payment batch to be the payment date. Once again it would be a estimate sadly

 

[NARAYANK991]

Hi ,

The value that you are seeing is strange because in my computer , I get 0.

However , this can be resolved by comparing it with a small number , say 0.005 , since such a number will not exist in any currency.

See the attached file.

Narayan