# Balance of a split transaction that is remaining to allocated

#### albert_de

##### New Member
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.

#### Attachments

• Split remaining.JPG
51.5 KB · Views: 25

#### albert_de

##### New Member
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

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Peter_SSs

##### MrExcel MVP, Moderator
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

##### New Member
Hi Peter,

OK, thanks.

Thank you once again.

Cheers
Richard

split alternative thinking v2.2.xlsx
BCDEFGHIJKLMNOP
18DateTx TypePayeeCategorysplit… /remainingMemoOutflowInflowBalance
191/01/2020Direct CreditPaySplit (Multiple Categories)…Split...0.00100.00100.00Split (Multiple Categories)…split
20Category Asplit 65.00100.00Category A
21Category Bsplit 35.00100.00Category B
222/01/2020EFTPOSShellFuel  15.0085.00Category C
233/01/2020EFTPOSBunningsSplit (Multiple Categories)…Split...37.0050.0035.00Category D
24Category Asplit 13.0035.00
25Category Bsplit 28.0035.00
26Category Csplit 9.0035.00
274/01/2020Direct CreditCBASplit (Multiple Categories)…Split...0.00135.00170.00
28Category Csplit 75.00170.00
29Category Dsplit 60.00170.00
305/01/2020EFTPOSColesCategory A  20.00150.00
316/01/2020DepositBruce WillisCategory C  1,000,000.001,000,150.00
327/01/2020BPAYATOCategory C  750,000.00250,150.00
338/01/2020EFTPOSHappy DollarSplit (Multiple Categories)…Split...1.9928.64250,121.36
34Category Bsplit 1.99250,121.36
35Category Csplit 26.65250,121.36
369/01/2020EFTPOSSydney MarketsCategory C  125,000.36125,121.00
3710/01/2020Direct CreditHarry PotterSplit (Multiple Categories)…Split...79.00100.00125,221.00
38Category Asplit 65.00125,221.00
39Category Bsplit 21.00125,221.00
40Category Dsplit 14.00125,221.00
41  125,221.00
42  125,221.00
43  125,221.00
Sheet2
Cell Formulas
RangeFormula
F41:F43,F27:F39,F19:F25F19=IF(E17="Split (Multiple Categories)…","split",IF(E18="Split (Multiple Categories)…","split",IF(E19="Split (Multiple Categories)…","Split...","")))
G27:G29,G19:G21G19=IF(F19<>"split...","",2*J19-SUM(J19:INDEX(J20:J\$50,MATCH(TRUE,INDEX(F20:F\$50<>"split",0),0)-1)))
G22,G32G22=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:G26G23=IF(F23<>"split...","",2*I23-SUM(I23:INDEX(I24:I\$50,MATCH(TRUE,INDEX(F24:F\$50<>"split",0),0)-1)))
G37G37=IF(F37<>"split...","",2*J37-SUM(J37:INDEX(J\$37:J50,MATCH(TRUE,INDEX(F\$37:F50<>"split",0),0)-1)))
G38:G40G38=IF(F38<>"split...","",2*J38-SUM(J38:INDEX(J\$37:J39,MATCH(TRUE,INDEX(F\$37:F51<>"split",0),0)-1)))
G41:G43G41=IF(F41<>"split...","",2*SUM(J41:J41)-SUM(J41:INDEX(J\$37:J42,MATCH(TRUE,INDEX(F\$37:F42<>"split",0),0)-1)))
K19K19=SUM(-I19+J19)
K20:K43K20=IF(F20="split",K19,K19-I20+J20)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I19:J50Expression=\$F19="split"textNO
F19:F50Expression=\$F19="split"textNO
K19:K50Expression=\$F19="split"textNO
Cells with Data Validation
CellAllowCriteria
E19:E50List=\$O\$18:\$O\$24
F19:F50List=\$P\$18:\$P\$20

#### Peter_SSs

##### MrExcel MVP, Moderator
If I have understood correctly you could try this

albert_de 2020-03-19 1.xlsm
FGHIJ
18split… /remainingMemoOutflowInflow
19Split...0100
20split 65
21split 35
22 15
23Split...3750
24split 13
25split 28
26split 9
27Split...0135
28split 75
29split 60
30 20
31 1000000
32 750000
33Split...1.9928.64
34split 1.99
35split 26.65
36 125000.36
37Split...79100
38split 65
39split 21
40split 14
41
42
43
44
Sheet5
Cell Formulas
RangeFormula
G19:G44G19=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
FGHIJ
18split… /remainingMemoOutflowInflow
19Split...0100
20split 65
21split 35
22 15
23Split...3750
24split 13
25split 28
26split 9
27Split...0135
28split 75
29split 60
30 20
31 1000000
32 750000
33Split...1.9928.64
34split 1.99
35split 26.65
36 125000.36
37Split...79100
38split 65
39split 21
40split 14
41
42
43
44
Sheet6
Cell Formulas
RangeFormula
G19:G44G19=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

##### New Member

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

##### MrExcel MVP, Moderator
Thank you for time and patience. The formula works perfectly.
You're welcome. Glad it worked out in the end.

#### anthonyfca

##### New Member

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:
Excel Formula:
``=IF(EXACT(A1,A2),D1-F2-G2,+G2+F2)``

and here shown:
splitz.xlsx
D
2100.00
Sheet1 (2)
Cell Formulas
RangeFormula
D2D2=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

##### New Member
sorry, new at this and still can't find the edit button:
splitz.xlsx
ABCDEFGH
1payeecategorysplitremainingmemodebitcreditbalance
2c/fSplit (Multiple Categories)split100.00100.00100.00
3c/fasplit75.0025.00
4c/fbsplit50.0025.00
5c/fcsplit25.0025.00
6c/fd0.0025.00
7aldiSplit (Multiple Categories)split(6.00)(6.00)94.00
8aldiasplit(5.00)(1.00)
9aldibsplit(3.00)(2.00)
10aldicsplit0.00(3.00)
11shellb10.0010.0084.00
12tex mexSplit (Multiple Categories)split1,000.0010001084
13tex mexasplit978.7021.30
14tex mexbsplit969.079.63
15tex mexcsplit99.40869.67
16tex mex0.0099.40
17paymanual splitView Split1,500.002,584.00
Sheet1 (2)
Cell Formulas
RangeFormula
D2:D16D2=IF(EXACT(A1,A2),D1-F2-G2,+G2+F2)

#### anthonyfca

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

Replies
3
Views
171
Replies
1
Views
202
Replies
1
Views
41
Replies
1
Views
143
Replies
0
Views
105

1,127,465
Messages
5,624,902
Members
416,064
Latest member
PaulBr2

### 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.

### Which adblocker are you using?

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

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