One to many - allocating payment to invoices, without splitting them across rows.

anthonyfca

New Member
Joined
Jan 12, 2012
Messages
8
I've spent years trying to develop a spreadsheet solution for this problem, which amounts to a 3D solution needed for our 2D environment.
Problem is this:

Column of invoices - call it the ledger.
Column of payments - call it the bank.
Both columns end up in a third column that holds the amount for bot invoices and payments (whether payables or receivables, same difference, same end column).

The challenge is how to match payments to invoices in non one-to-one situations.

So say we have invoices 1, 2 and 3, bank part pays two of them with one payment..
Then (next week) bank pays off all three in one payment..
This is real easy in accounting software, because they have multiple tables available to play with.
A nice to have, perhaps even a necessity is to be able to report the history in a pivot table / table of some sort, but let's not restrict ideas with that.

My latest solution is
an "allocations" column that contains text of invoice number and amount paid, for each invoice this payment is paying all or part of.
a "balance"column so each invoice adds up all the payments allocated to it in the "allocations" texts and shows the remaining balance.

Here's an example of the "allocations" column text content for two amounts against invoices; this appears on the bank payment row.
In Col AE: NIN003-0008000.00SIX-0010000.00.

So what that is saying is:
NINE003 and SIX are invoice numbers from Col K2 , whereas AB2 contains the full amount of the payment the rest contains the amount of each payment allocation. The - means minus, not a dash.

The balance column, on each invoice row, contains:
=SUM(IFERROR((MID(AE:AE,FIND(K2,AE:AE)+LEN(K2),(11)))/1),0))
and that deducts from that row's invoice, the amount of each payment as listed in the text entries in the allocations column.

The point of this is to avoid multi row solutions where the payment is split up according to which invoice is paid or part paid.

Would I consider using a separate sheet entirely? Yes, because the general assumption is nearly all of the payments against invoices are payments in full and these part payment items are the exception, but the workflow would have to be slick and I am not confident it would be practical, however I want to just leave the door ajar.

Please don't hesitate to ask for clarification.

Regards

Anthony

!LedgerExample-01.xlsx
KLABACADAEAFAGAH
1INVREFPayeeLEDGERCONTROLNOTESAllocatorBalance
2FREDDYFIVEName one(15,000.78)(15,000.78)FREDDYFIVE-0015000.78(6,000.78)
3SIXName one24,000.008,999.22SIX00024000.007,000.00
4SIXName one(7,000.00)1,999.22SIX-0007000.00
5FREDDYFIVEName one5,000.006,999.22FREDDYFIVE00005000.00
6NIN003Name one18,000.0024,999.22NIN00300018000.0010,000.00
7NIN003Name one(18,000.00)6,999.22NIN003-0008000.00SIX-0010000.00
8NIN003BName one(10,888.00)(3,888.78)NIN003B-0010888.00(6,888.00)
9NIN003BName one4,000.00111.22NIN003B00004000.00
10FREDDYFIVEName one4,000.004,111.22FREDDYFIVE00004000.00
114,111.224,111.22
DATA
Cell Formulas
RangeFormula
AF2:AF3,AF8,AF6AF2=SUM(IFERROR((MID(AE:AE,FIND(K2,AE:AE)+LEN(K2),(11))/1),0))
K4,K9,K7K4=K3
AC2:AC10AC2=N(OFFSET(AC2,-1,0))+AB2
AB11,AF11AB11=SUM(AB1:AB10)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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