How to identify which invoices to apply for a lump sum payment received.

mkwang

New Member
Joined
May 29, 2015
Messages
11
Hi all,

I have day to day issue at work and would be very much appreciated if a solution could be recommended.

Background : I often receive a lump sum payment from client however no information as to which invoices applied. Sometimes the client could have more than 20 invoices.

Question : Is it possible to find which invoice(s) the payment refer? The problem is I don't even know number of invoices client to pay, and could only use trial and error at the moment (please refer to below table for an example)


Payment Received26,106.00

Invoice3043367304337230433743043394304345230434543043456304346130434633043466304346830434743043477
Amount2,515.0015,620.004,560.008,640.001,100.005,355.002,460.00820.004,825.002,050.009,035.002,225.001,726.00TotalCheck
Trial 12,515.0015,620.004,560.001,100.002,460.0026,255.00149.00
Trial 22,515.0015,620.004,560.002,460.00820.0025,975.00131.00
Trial 32,515.0015,620.004,560.00820.002,225.0025,740.00366.00
Trial 42,515.0015,620.001,100.002,460.004,825.0026,520.00414.00
Trial 52,515.0015,620.001,100.002,460.00820.002,050.001,726.0026,291.00185.00
Trial 62,515.0015,620.002,460.004,825.0025,420.00686.00

Assumption : client payment is exactly matched to total of those invoices (ie. no bank charges deducted, etc)


Thank you with best regards,
Mike
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi,

Hmm, replicate the thoughts of the client paying a lump sum amount. This is something which basically is a recipe for disaster because in fact you never know.
Although the thoughts can never be solved the mathematics can by using VBA to do the trial and error for you.

Not my invention so will not steal it but look at the VBA part at this link: Combination of numbers that sum or match a target value

I think this will help you in the right direction.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top