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?

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.

rohitsinha

ASKER

Yeah, its complicated and I was hoping that there was a way to do it

ASKER CERTIFIED SOLUTION

Member_2_933725

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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