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
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
Last edited: