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

[anthonyfca]

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
	K	L																AB	AC	AD	AE	AF	AG	AH
1	INVREF	Payee																LEDGER	CONTROL	NOTES	Allocator	Balance
2	FREDDYFIVE	Name one																(15,000.78)	(15,000.78)		FREDDYFIVE-0015000.78	(6,000.78)
3	SIX	Name one																24,000.00	8,999.22		SIX00024000.00	7,000.00
4	SIX	Name one																(7,000.00)	1,999.22		SIX-0007000.00
5	FREDDYFIVE	Name one																5,000.00	6,999.22		FREDDYFIVE00005000.00
6	NIN003	Name one																18,000.00	24,999.22		NIN00300018000.00	10,000.00
7	NIN003	Name one																(18,000.00)	6,999.22		NIN003-0008000.00SIX-0010000.00
8	NIN003B	Name one																(10,888.00)	(3,888.78)		NIN003B-0010888.00	(6,888.00)
9	NIN003B	Name one																4,000.00	111.22		NIN003B00004000.00
10	FREDDYFIVE	Name one																4,000.00	4,111.22		FREDDYFIVE00004000.00
11																		4,111.22				4,111.22
DATA

Cell Formulas
Range	Formula
AF2:AF3,AF8,AF6	AF2	=SUM(IFERROR((MID(AE:AE,FIND(K2,AE:AE)+LEN(K2),(11))/1),0))
K4,K9,K7	K4	=K3
AC2:AC10	AC2	=N(OFFSET(AC2,-1,0))+AB2
AB11,AF11	AB11	=SUM(AB1:AB10)

 

[anthonyfca]

in the above mini sheet the balance figure is missing rom AF2, my error in creating the mini extract.