Avatar of rohitsinha
rohitsinha
 asked on

Matching payments against open invoices

I have a list of open invoices in MS Excel which I want to match to payments. My problem is that the customer often pays for multiple invoices without payment details and I need to identify the invoices for which the payment is received. For e.g. I have
Inv Amt
A  1
B  2
C  3
D  4
E  5

If I receive a payment for $6, I want to be able to match it to either invoices AE or ABC or BD.

How can I get this done is MS excel?
Microsoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
rohitsinha

8/22/2022 - Mon
Lorenda Christensen

This will be VERY difficult and time consuming. You are basically wanting Excel to loop through an almost infinite number of possible combinations, and give you a "best match". How would you handle an issue like this with your same $6 payment?

A1
B2
C3
D4
E5
F1

rohitsinha

ASKER
I don't expect such duplications to be there as the values are in thousands and the number of open invoices are less than 50. The problem is I do not know how many invoices does the payment cover.
Lorenda Christensen

I understand your pain - I worked in AR for over 5 years now - but for only 50 invoices, you are talking about MILLIONS of possible combinations that the computer will have to cycle through FOR EACH PAYMENT, because there is no way to tell the computer to look at the "best guesses" first.
 
 
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
rohitsinha

ASKER
Yeah, its complicated and I was hoping that there was a way to do it
ASKER CERTIFIED SOLUTION
Member_2_933725

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
rohitsinha

ASKER
Thanks Mike. your spreadsheet works beautifully. Its even tells me how many possible solutions are there.
rohitsinha

ASKER
Very useful to match payments to multiple invoices
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.