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

[mkwang]

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 Received

26,106.00

Invoice	3043367	3043372	3043374	3043394	3043452	3043454	3043456	3043461	3043463	3043466	3043468	3043474	3043477
Amount	2,515.00	15,620.00	4,560.00	8,640.00	1,100.00	5,355.00	2,460.00	820.00	4,825.00	2,050.00	9,035.00	2,225.00	1,726.00	Total	Check
Trial 1	2,515.00	15,620.00	4,560.00		1,100.00		2,460.00							26,255.00	149.00
Trial 2	2,515.00	15,620.00	4,560.00				2,460.00	820.00						25,975.00	131.00
Trial 3	2,515.00	15,620.00	4,560.00					820.00				2,225.00		25,740.00	366.00
Trial 4	2,515.00	15,620.00			1,100.00		2,460.00		4,825.00					26,520.00	414.00
Trial 5	2,515.00	15,620.00			1,100.00		2,460.00	820.00		2,050.00			1,726.00	26,291.00	185.00
Trial 6	2,515.00	15,620.00					2,460.00		4,825.00					25,420.00	686.00

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


Thank you with best regards,
Mike

 

[jorismoerings]

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.

 

[mkwang]

Thank you so much and I will try this.

Best regards,
Mike

 

[smozgur]

Here’s a MrExcel video for future readers that shows how to solve the problem using Python in 365.

 

[NdNoviceHlp]

Hi mkwang. You can trial the code at the following link. To set up worksheet for the code as is, place your invoice amounts in A2:A&whatever and your payment received in C2 of sheet1. Run the test code. In column "B", x3 possible combinations of invoices (if available) will be outputted as "X", "Y" & "Z" next to the invoice values in "A". In your example there is only one possible combination (ie. "X" only") .... 2515.00, 8640.00, 5355.00, 820.00, 4825.00, 2225.00 & 1726.00. HTH. Dave

is there a way to compare one number (a total) against a bunch of smaller numbers to see which numbers match?

i have a client that has this really annoying bank. when they pay multiple people on the same day the transactions appear as one lump sum. for example if they pay Vendors A, B, and C on the same day, in the amounts of $100, $200, $300, it might appear as $600. Or it might appear as $400 (the sum...

www.mrexcel.com

edit: Just noticed this was a post from July/21.... hmmm