MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Add this...Add that...

Posted by bill.roberts on April 12, 2000 2:25 PM

I have a list of amounts (outstanding invoice amounts).

I received a number (payment from client which covers SOME of the outstanding amounts).

As the list of numbers is quite large, how can I ask EXCEL to find the composition of that number?

In other words, can EXCEL extract the values which make up payment?


Posted by Celia on April 15, 2000 5:47 AM

The answer to your question is “yes, I suppose so”, but I can’t think of an easy way.

It is possible to have Excel :-

· Allocate the amount received to the o/s amounts on a FIFO basis.


· Work out all of the combinations of the o/s amounts that make up the payments received and then :-
If there is more than one combination that makes up the amount received, or if no combinations make it up, allocate it based upon some predetermined criteria.
Such criteria might consist of :- FIFO, and/or specific o/s amounts to be excluded from the match, and/or whatever.

I think a different approach is needed.
Is it not possible to input the amount received already broken down by the o/s amount reference numbers? It would then be fairly simple to have Excel do a match.

PS. The above is merely an observation that is intended to assist. It should not be construed as a commitment to any further follow-up.