Balance of a split transaction that is remaining to allocated

[albert_de]

Hi, I am trying to build a bank account ledger in excel that allows a transaction to be split across multiple (budget) categories. My goal is to be able to report how much of the base transaction split remains to be allocated.

I have no problem achieving this using a horizontal table, but I cannot figure out how to achieve this using a vertical table (as per a normal ledger).

In the attached screenshot, E2 is trying to calculate the balance of the split that remains to be allocated for the base transaction shown in G2 based on rows allocated with "split" in column C. The split amount is placed in either column F & G. (Split amounts do not affect the account balance, only the base transaction affects the account balance)

The issue I have is working out how to get Excel to calculate in columns until there is a change in another column (in this case column C to return the split remaining to be allocated.

Hopefully someone much smarter than me can teach me how to resolve this task. I would be grateful for any help as I am truly stuck. Thank you.

 

[albert_de]

Hi Peter

Thank you once again for your help today.

I have been busy trying learn more about INDEX and MATCH but I think the 39 degree day in Sydney is putting me to sleep!

I came up with a similar approach to the first point and then fell back onto what is probably bad habits with point 2 and got stuck.

To answer your question re point 2 - I take your point about split transactions possibly including a Debit and a Credit. At this stage I think I would be happy if I could work out how to look ay Column I & J independently.

With that in mind I tried the following 2 separate formulas in different split transactions (a Debit and a Credit):
Debit:
=IF(F19<>"split...","",2*SUM(I19:I19)-SUM(I19:INDEX(I20:I$37,MATCH(TRUE,INDEX(F20:F$37<>"split",0),0)-1)))

Credit
=IF(F19<>"split...","",2*SUM(J19:J19)-SUM(J19:INDEX(J20:J$37,MATCH(TRUE,INDEX(F20:F$37<>"split",0),0)-1)))

Separately I am happy with the way this works -only the column with the transaction amount in it is calculated, the other column is ignored.

How to solve this?

This is where I fell into a bad habit that probably doesn't work in this situation by trying to nest the above formula in G19 down. formula.
=IF(F19<>"split...","",IF(I19>0,2*I19-SUM(I19:INDEX(I20:I$37,MATCH(TRUE,INDEX(F20:F$37<>"split",0),IF(J19>0,2*J19-SUM(J19:INDEX(J20:J$37,MATCH(TRUE,INDEX(F20:F$37<>"split",0),0)))))))))

Doesn't work. I guess nesting doesn't work here?

I hope that makes some sense!

Cheers
Richard

 

[Peter_SSs]

To be honest, giving formulas that you have tried and don't quite do what you want is not what is required here. Whilst it is showing that that you are trying to solve the problem yourself (which is great to see), it doesn't help get to the desired result.

What would help me most to help you, is to have 6 or 8 smallish split transactions (& non-split transactions) that demonstrate the different input possibilities (eg entries in the wrong column, missing a row, totals that are equal to, greater than and less than the original transaction amount) and the desired results.

 

[albert_de]

Hi Peter,

OK, thanks.

Please see below as requested.

Thank you once again.

Cheers
Richard

split alternative thinking v2.2.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
18	Date	Tx Type	Payee	Category	split… /	remaining	Memo	Outflow	Inflow	Balance
19	1/01/2020	Direct Credit	Pay	Split (Multiple Categories)…	Split...	0.00			100.00	100.00				Split (Multiple Categories)…	split
20				Category A	split				65.00	100.00				Category A
21				Category B	split				35.00	100.00				Category B
22	2/01/2020	EFTPOS	Shell	Fuel				15.00		85.00				Category C
23	3/01/2020	EFTPOS	Bunnings	Split (Multiple Categories)…	Split...	37.00		50.00		35.00				Category D
24				Category A	split			13.00		35.00
25				Category B	split				28.00	35.00
26				Category C	split				9.00	35.00
27	4/01/2020	Direct Credit	CBA	Split (Multiple Categories)…	Split...	0.00			135.00	170.00
28				Category C	split				75.00	170.00
29				Category D	split				60.00	170.00
30	5/01/2020	EFTPOS	Coles	Category A				20.00		150.00
31	6/01/2020	Deposit	Bruce Willis	Category C					1,000,000.00	1,000,150.00
32	7/01/2020	BPAY	ATO	Category C				750,000.00		250,150.00
33	8/01/2020	EFTPOS	Happy Dollar	Split (Multiple Categories)…	Split...	1.99		28.64		250,121.36
34				Category B	split				1.99	250,121.36
35				Category C	split			26.65		250,121.36
36	9/01/2020	EFTPOS	Sydney Markets	Category C				125,000.36		125,121.00
37	10/01/2020	Direct Credit	Harry Potter	Split (Multiple Categories)…	Split...	79.00			100.00	125,221.00
38				Category A	split			65.00		125,221.00
39				Category B	split				21.00	125,221.00
40				Category D	split			14.00		125,221.00
41										125,221.00
42										125,221.00
43										125,221.00
Sheet2

Cell Formulas
Range	Formula
F41:F43,F27:F39,F19:F25	F19	=IF(E17="Split (Multiple Categories)…","split",IF(E18="Split (Multiple Categories)…","split",IF(E19="Split (Multiple Categories)…","Split...","")))
G27:G29,G19:G21	G19	=IF(F19<>"split...","",2*J19-SUM(J19:INDEX(J20:J$50,MATCH(TRUE,INDEX(F20:F$50<>"split",0),0)-1)))
G22,G32	G22	=IF(F22<>"split...","",2*SUM(I22:I22)-SUM(I22:INDEX(I23:I$37,MATCH(TRUE,INDEX(F23:F$37<>"split",0),0)-1)))
G33:G36,G30:G31,G23:G26	G23	=IF(F23<>"split...","",2*I23-SUM(I23:INDEX(I24:I$50,MATCH(TRUE,INDEX(F24:F$50<>"split",0),0)-1)))
G37	G37	=IF(F37<>"split...","",2*J37-SUM(J37:INDEX(J$37:J50,MATCH(TRUE,INDEX(F$37:F50<>"split",0),0)-1)))
G38:G40	G38	=IF(F38<>"split...","",2*J38-SUM(J38:INDEX(J$37:J39,MATCH(TRUE,INDEX(F$37:F51<>"split",0),0)-1)))
G41:G43	G41	=IF(F41<>"split...","",2*SUM(J41:J41)-SUM(J41:INDEX(J$37:J42,MATCH(TRUE,INDEX(F$37:F42<>"split",0),0)-1)))
K19	K19	=SUM(-I19+J19)
K20:K43	K20	=IF(F20="split",K19,K19-I20+J20)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I19:J50	Expression	=$F19="split"	text	NO
F19:F50	Expression	=$F19="split"	text	NO
K19:K50	Expression	=$F19="split"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E19:E50	List	=$O$18:$O$24
F19:F50	List	=$P$18:$P$20

 

[Peter_SSs]

If I have understood correctly you could try this

albert_de 2020-03-19 1.xlsm
	F	G	H	I	J
18	split… /	remaining	Memo	Outflow	Inflow
19	Split...	0			100
20	split				65
21	split				35
22				15
23	Split...	37		50
24	split			13
25	split				28
26	split				9
27	Split...	0			135
28	split				75
29	split				60
30				20
31					1000000
32				750000
33	Split...	1.99		28.64
34	split				1.99
35	split			26.65
36				125000.36
37	Split...	79			100
38	split			65
39	split				21
40	split			14
41
42
43
44
Sheet5

Cell Formulas
Range	Formula
G19:G44	G19	=IF(F19<>"split...","",2*SUM(I19:J19)-SUM(IF(I19="",J19:INDEX(J19:J$50,MATCH(TRUE,INDEX(F20:F$50<>"split",0),0)),I19:INDEX(I19:I$50,MATCH(TRUE,INDEX(F20:F$50<>"split",0),0)))))

Or if you don't have a huge data set (that is, not tens of thousands of rows) then you could also try this.
Because it uses the volatile function OFFSET, if used a huge amount of times in a sheet it can affect the speed of the sheet calculation.

albert_de 2020-03-19 1.xlsm
	F	G	H	I	J
18	split… /	remaining	Memo	Outflow	Inflow
19	Split...	0			100
20	split				65
21	split				35
22				15
23	Split...	37		50
24	split			13
25	split				28
26	split				9
27	Split...	0			135
28	split				75
29	split				60
30				20
31					1000000
32				750000
33	Split...	1.99		28.64
34	split				1.99
35	split			26.65
36				125000.36
37	Split...	79			100
38	split			65
39	split				21
40	split			14
41
42
43
44
Sheet6

Cell Formulas
Range	Formula
G19:G44	G19	=IF(F19<>"split...","",2*SUM(I19:J19)-SUM(OFFSET(I19,0,IF(I19="",1,0),MATCH(TRUE,INDEX(F20:F$50<>"split",0),0))))

 

[albert_de]

Hi Peter

Thank you for time and patience. The formula works perfectly.

I see where I went wrong with nesting the formula's.

Thank you very much for helping me with this task. I really appreciate it.

Cheers
Richard

 

[Peter_SSs]

Thank you for time and patience. The formula works perfectly.

You're welcome. Glad it worked out in the end.

 

[anthonyfca]

I can see this is an old question, but it relates to my own one-to-many ledger problem so I am looking at it for inspiration before asking my own question, in the meantime I came up with this so may as well offer it.
My answer would be to fill in the payee column fully and then use this formula in column D to give the rolling remaining amount per base amount:

=IF(EXACT(A1,A2),D1-F2-G2,+G2+F2)

and here shown:

splitz.xlsx
	D
2	100.00
Sheet1 (2)

Cell Formulas
Range	Formula
D2	D2	=IF(EXACT(A1,A2),D1-F2-G2,+G2+F2)

I now see it doesn't help my problem, but there we are, it was fun

Anthony

 

[anthonyfca]

sorry, new at this and still can't find the edit button:

splitz.xlsx
	A	B	C	D	E	F	G	H
1	payee	category	split	remaining	memo	debit	credit	balance
2	c/f	Split (Multiple Categories)	split	100.00			100.00	100.00
3	c/f	a	split	75.00			25.00
4	c/f	b	split	50.00			25.00
5	c/f	c	split	25.00			25.00
6	c/f	d		0.00			25.00
7	aldi	Split (Multiple Categories)	split	(6.00)		(6.00)		94.00
8	aldi	a	split	(5.00)		(1.00)
9	aldi	b	split	(3.00)		(2.00)
10	aldi	c	split	0.00		(3.00)
11	shell	b		10.00		10.00		84.00
12	tex mex	Split (Multiple Categories)	split	1,000.00			1000	1084
13	tex mex	a	split	978.70			21.30
14	tex mex	b	split	969.07			9.63
15	tex mex	c	split	99.40			869.67
16	tex mex			0.00			99.40
17	pay	manual split	View Split				1,500.00	2,584.00
Sheet1 (2)

Cell Formulas
Range	Formula
D2:D16	D2	=IF(EXACT(A1,A2),D1-F2-G2,+G2+F2)

 

[anthonyfca]

using the split column would do just as well, or that can be deleted.