Aging Report Formula?

[savosean]

I was wondering if anyone knew if this was possible, I am trying to age a large file of accounts (5000+) that simply have user ID's attached to them, amounts and their date. What I am trying to do is to allocate each age appropriately. Meaning I would like to subtotal per User Id the fact that the amount in the first dated transaction is still owing to this day, however, it received two payments. Therefore, technically owing since 08-22-17 as seen below would be 465, and another 200 owing as of 12-31-18.

Illustration 1A :

USER $ Date outstanding 1-30 days 31-60 days 61-90 days
123 565 08-22-17 XXX XXX xxx xxx
123 -55 08-22-18 XXX XXX xxx xxx
123 -45 11-12-18 XXX XXX xxx xxx
123 200 12-31-18 XXX XXX xxx xxx

(EDIT: the formatting is not working for this table, but the columns should all be in order USER ID, Amount, DATE, number of days outstanding, 1-30 days, 31-60 days, 61-90 days,... and it continues on to 5+ years)

So far I am unable to do this correctly with out manually adjusting the subtotal feature. I've been able to subtotal each USER ID, therefore I know how old each transaction is... except its not really a running aging report at this point. Is what I am asking possible? If further explanation is required please let me know.

thanks

 

[SpillerBD]

Aging. Are you using a Pivot Table and grouping the Number of Days past-due?

 

[savosean]

Aging. Are you using a Pivot Table and grouping the Number of Days past-due?

I could use a pivot table, however, I used the subtotal feature... to subtotale every column at each change in USER ID. However, this doesn't correct allocate the aging of a debt, if payments have come in since then. As my output is lets say 180 days past due $500 , 90 days past due -$300, and 30 days past due $50... when what I'd really like to see is 180 days past due $200, and 30 days past due $50. Basically applying the payment to the oldest debt, and still having it showcase that debt has been owing since 180+ days.

I also have situations where 180 days past due $500, 90 days past due -$600, and 30 days past due $70... Therefore, the oldest debt has been eliminated, however, they overpaid the account. So in the account it should show a credit balance of $30 in the 90 days past due column.

I haven't really been able to find a solution online, and don't really know if what I am asking is even possible. Was hoping for some insight...

 

[Aryatect]

I could use a pivot table, however, I used the subtotal feature... to subtotale every column at each change in USER ID. However, this doesn't correct allocate the aging of a debt, if payments have come in since then. As my output is lets say 180 days past due $500 , 90 days past due -$300, and 30 days past due $50... when what I'd really like to see is 180 days past due $200, and 30 days past due $50. Basically applying the payment to the oldest debt, and still having it showcase that debt has been owing since 180+ days.

I also have situations where 180 days past due $500, 90 days past due -$600, and 30 days past due $70... Therefore, the oldest debt has been eliminated, however, they overpaid the account. So in the account it should show a credit balance of $30 in the 90 days past due column.

I haven't really been able to find a solution online, and don't really know if what I am asking is even possible. Was hoping for some insight...

Hi,

See if this helps:

	A	B	C	D	E	F	G
User ID	Amount	Date	Amount due	Date	Due from	Due Amount

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]565[/TD]
[TD="align: right"]8/22/2017[/TD]
[TD="align: right"]565[/TD]
[TD="align: right"]8/22/2017[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]-55[/TD]
[TD="align: right"]8/22/2018[/TD]
[TD="align: right"]510[/TD]
[TD="align: right"]8/22/2017[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]-45[/TD]
[TD="align: right"]11/12/2018[/TD]
[TD="align: right"]465[/TD]
[TD="align: right"]8/22/2017[/TD]
[TD="align: right"]8/22/2017[/TD]
[TD="align: right"]465[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]12/31/2018[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]12/31/2018[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]6/21/2019[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]12/31/2018[/TD]
[TD="align: right"]12/31/2018[/TD]
[TD="align: right"]190[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IF(A2=A1,IF(B2<0,B2+D1,B2),B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IF(B2>=0,C2,E1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(E2=E3,"",E2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(F2="","",D2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D3[/TH]
[TD="align: left"]=IF(A3=A2,IF(B3<0,B3+D2,B3),B3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=IF(B3>=0,C3,E2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F3[/TH]
[TD="align: left"]=IF(E3=E4,"",E3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=IF(F3="","",D3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=IF(A4=A3,IF(B4<0,B4+D3,B4),B4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=IF(B4>=0,C4,E3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=IF(E4=E5,"",E4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=IF(F4="","",D4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D5[/TH]
[TD="align: left"]=IF(A5=A4,IF(B5<0,B5+D4,B5),B5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]=IF(B5>=0,C5,E4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=IF(E5=E6,"",E5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]=IF(F5="","",D5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=TODAY()[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D6[/TH]
[TD="align: left"]=IF(A6=A5,IF(B6<0,B6+D5,B6),B6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6[/TH]
[TD="align: left"]=IF(B6>=0,C6,E5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]=IF(E6=E7,"",E6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6[/TH]
[TD="align: left"]=IF(F6="","",D6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[SpillerBD]

Subtotal acts on your data rather than reporting on your data, which is what a Pivot Table does.
What I see is that you have no base transaction, just debits and credits.
A Payment shouldn't be on this report. It is a different transaction. However, in this scenario a payment could be recorded with the same information as the invoice so the two items would be summed together and the aging $ correct.

 

[savosean]

Hi,

See if this helps:

	A	B	C	D	E	F	G
User ID	Amount	Date	Amount due	Date	Due from	Due Amount

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]565[/TD]
[TD="align: right"]8/22/2017[/TD]
[TD="align: right"]565[/TD]
[TD="align: right"]8/22/2017[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]-55[/TD]
[TD="align: right"]8/22/2018[/TD]
[TD="align: right"]510[/TD]
[TD="align: right"]8/22/2017[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]-45[/TD]
[TD="align: right"]11/12/2018[/TD]
[TD="align: right"]465[/TD]
[TD="align: right"]8/22/2017[/TD]
[TD="align: right"]8/22/2017[/TD]
[TD="align: right"]465[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]12/31/2018[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]12/31/2018[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]-10[/TD]
[TD="align: right"]6/21/2019[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]12/31/2018[/TD]
[TD="align: right"]12/31/2018[/TD]
[TD="align: right"]190[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=IF(A2=A1,IF(B2<0,B2+D1,B2),B2)[/TD]
[/TR]
[TR]
[TH]E2[/TH]
[TD="align: left"]=IF(B2>=0,C2,E1)[/TD]
[/TR]
[TR]
[TH]F2[/TH]
[TD="align: left"]=IF(E2=E3,"",E2)[/TD]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]=IF(F2="","",D2)[/TD]
[/TR]
[TR]
[TH]D3[/TH]
[TD="align: left"]=IF(A3=A2,IF(B3<0,B3+D2,B3),B3)[/TD]
[/TR]
[TR]
[TH]E3[/TH]
[TD="align: left"]=IF(B3>=0,C3,E2)[/TD]
[/TR]
[TR]
[TH]F3[/TH]
[TD="align: left"]=IF(E3=E4,"",E3)[/TD]
[/TR]
[TR]
[TH]G3[/TH]
[TD="align: left"]=IF(F3="","",D3)[/TD]
[/TR]
[TR]
[TH]D4[/TH]
[TD="align: left"]=IF(A4=A3,IF(B4<0,B4+D3,B4),B4)[/TD]
[/TR]
[TR]
[TH]E4[/TH]
[TD="align: left"]=IF(B4>=0,C4,E3)[/TD]
[/TR]
[TR]
[TH]F4[/TH]
[TD="align: left"]=IF(E4=E5,"",E4)[/TD]
[/TR]
[TR]
[TH]G4[/TH]
[TD="align: left"]=IF(F4="","",D4)[/TD]
[/TR]
[TR]
[TH]D5[/TH]
[TD="align: left"]=IF(A5=A4,IF(B5<0,B5+D4,B5),B5)[/TD]
[/TR]
[TR]
[TH]E5[/TH]
[TD="align: left"]=IF(B5>=0,C5,E4)[/TD]
[/TR]
[TR]
[TH]F5[/TH]
[TD="align: left"]=IF(E5=E6,"",E5)[/TD]
[/TR]
[TR]
[TH]G5[/TH]
[TD="align: left"]=IF(F5="","",D5)[/TD]
[/TR]
[TR]
[TH]C6[/TH]
[TD="align: left"]=TODAY()[/TD]
[/TR]
[TR]
[TH]D6[/TH]
[TD="align: left"]=IF(A6=A5,IF(B6<0,B6+D5,B6),B6)[/TD]
[/TR]
[TR]
[TH]E6[/TH]
[TD="align: left"]=IF(B6>=0,C6,E5)[/TD]
[/TR]
[TR]
[TH]F6[/TH]
[TD="align: left"]=IF(E6=E7,"",E6)[/TD]
[/TR]
[TR]
[TH]G6[/TH]
[TD="align: left"]=IF(F6="","",D6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This is great, however, a few follow up questions I have in regards to this. What's found in B6 shouldn't go against the more recent debt, it should go against the oldest debt if one is still present. Therefore, G4 should be 455 due since 2017-08-22, and G6 should see 200 due since 2018-12-31.

Additionally, I've tried continuing the code with another USER ID and it doesn't seem to flow through, is there something specific I must do... as in cannot simply drag the code down? I will keep looking at it, and see if I can figure out how on my own too. Thank you very much for the help!


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 54"]
<colgroup><col></colgroup><tbody>[TR]
[TD]USER ID[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][TABLE="width: 57"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Amount[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Date [/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][TABLE="width: 84"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Amount due[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Due From[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="width: 83"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Due amount[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]<strike></strike>123
[/TD]
[TD]565
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2017-08-22[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][TABLE="width: 84"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]565[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2017-08-22[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]-55
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2018-08-22[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>510
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2017-08-22[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]-45
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2018-11-12[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>465
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2017-08-22[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2017-08-22[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]465
[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]200
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2018-12-31[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>200
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2018-12-31[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]-10
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2019-06-21[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>190
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2018-12-31[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2018-12-31[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]190
[/TD]
[/TR]
[TR]
[TD]234
[/TD]
[TD]560
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2016-08-22[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>560
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2016-08-22[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2016-08-22[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]560
[/TD]
[/TR]
[TR]
[TD]234
[/TD]
[TD]234
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2019-08-22[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>234
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2017-08-22[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]234
[/TD]
[TD]-4
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2018-11-12[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>189
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2017-08-22[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]234
[/TD]
[TD]-78
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2018-12-31[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>111
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2017-08-22[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]234
[/TD]
[TD]-45
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2019-06-21[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>66
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2017-08-22[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][TABLE="width: 73"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]2017-08-22[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][TABLE="width: 83"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>66
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>

 

[savosean]

Subtotal acts on your data rather than reporting on your data, which is what a Pivot Table does.
What I see is that you have no base transaction, just debits and credits.
A Payment shouldn't be on this report. It is a different transaction. However, in this scenario a payment could be recorded with the same information as the invoice so the two items would be summed together and the aging $ correct.

Could you explain this further to me? I am trying to grasp this concept... I have used a Pivot table and it brings forth the same results as my subtotal function has. How do you mean "a payment could be recorded with the same information as the invoice so the two items would be summed together and the aging correct?"

As of now I have everything summed(Pivot Table) in such that all of the transactions associated to the same user ID are within the same row. I have columns throughout from 1-30 days past due all the way to 5+ years...

For example in USER ID 123 they have a debt of $500 past due from 180days which falls in the 121-180days past due column, however, made payments of $120 which fall within the 31-60 days pay due column, and has new debt from 1-30days past due of $200.

Therefore, USER ID 123 is 180 days past du on $380, and is 1-30 days past due on $200. This is the result I want to report, for over 5000 USER ID's which is a report I'd like to do every month. Therefore, I am in the process of trying to find a way of doing such a thing in the most automatic way possible so I can setup a procedures list for the next person to complete this tasks without much hassle.

 

[SpillerBD]

For example in USER ID 123 they have a debt of $500 past due from 180days which falls in the 121-180days past due column, however, made payments of $120 which fall within the 31-60 days pay due column, and has new debt from 1-30days past due of $200.

The payment APPLIES to the debt. So it doesn't apply to itself as you are listing them.
So if you have a payment listed in your transaction list it should have a listing to the invoice(s) it applies to. The complication for a simple table is when a payment applies to multiple debt items. This complication is where a database generally provides a solution that a simple table does.

Most people will think of the transactions at there summary status and not the individual transaction that net to the current balance, and usually an accounting application does it anyway.

In an Excel Table, you would have the transaction detail and fields for the application. An Invoice would apply to itself, so it would appear to have duplicate information. A Payment would list its information and then in the application field the part of the payment applied and the invoice application being made to.
Its the case of "your data must support your reporting"

 

[savosean]

The payment APPLIES to the debt. So it doesn't apply to itself as you are listing them.
So if you have a payment listed in your transaction list it should have a listing to the invoice(s) it applies to. The complication for a simple table is when a payment applies to multiple debt items. This complication is where a database generally provides a solution that a simple table does.

Most people will think of the transactions at there summary status and not the individual transaction that net to the current balance, and usually an accounting application does it anyway.

In an Excel Table, you would have the transaction detail and fields for the application. An Invoice would apply to itself, so it would appear to have duplicate information. A Payment would list its information and then in the application field the part of the payment applied and the invoice application being made to.
Its the case of "your data must support your reporting"

I believe I sort of understand what you are saying, however, I think I was oversimplifying the situation. And therefore, getting a bit lost in your explanation. As I was explaining it as if the first debit transaction is an invoice simply as an example. It's not... These are not invoices and payments from clients, I used that as an example in attempts to simplify my issue. This is a giant ledger, whereas the amounts in here are associated to a specific function in the organization. Sometimes we owe clients, other times, the client owes us. Hence the credit and debit transactions throughout. This ledger is a few years old now, and therefore their are transactions in there fairly dated. The only ID field that matches transactions together is their USER ID, besides that they have no relation. However, I don't want to be saying their is a transaction of 180 days old that's $500, and a transaction that's 90 days old that's -$60, and a 30 day old transaction of $60... I'd like it to accurately represent that currently sitting in this USER ID's account is $440 and it's 180 days old, and $60 which is 30 days old.

From my understanding of what you are saying is that I need to identify the credit amounts to certain debit amounts, however, this would be manual work and therefore defeat the purpose of what I am trying to do. If I am not understanding I am sorry.

 

[SpillerBD]

I take it then the ledger may be held in a system apart from Excel.
And I think you may be in a bit of a pickle where the data will not support exactly what you want with out a manual reconciliation. If the transactions have a relationship to match debits and credits then you should be good. But it doesn't sound like the data supports it.
It may not well support a manual reconciliation one month that can easily carry-forward to the next.

You will need to identify the relationships that are available to determine to what extent you can work with the data.
There may still be an approach where you work in the Table with a running balance for each ID, and when the running balance goes <zero, grab that date for aging. There would be more than just that step though....