Determine the oldest date of debit transaction in account, that hasn't been summed to 0 yet

savosean

New Member
Joined
Jun 7, 2018
Messages
36
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]User ID
[/TD]
[TD]Amounts
[/TD]
[TD]Posting Date
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]$500
[/TD]
[TD]2017.07.24
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]-$150
[/TD]
[TD]2019.04.05
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]-$200
[/TD]
[TD]2019.05.10
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]$200
[/TD]
[TD]2019.06.24
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]-$350
[/TD]
[TD]2019.06.25
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]$600[/TD]
[TD]2019.09.03
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]-$100
[/TD]
[TD]2019.09.07
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]-$200
[/TD]
[TD]2019.10.03
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I there a way in which I could automatically identify the bolded cell? I have a list of 40,000 lines, with each line item identifiable by a USER ID, and a posting date... accompanied by a transaction amount. What
I want to do is highlight the cell with the date for the oldest debit transaction that has not yet been summed
down to $0. In the example above, the initial debit of $500 is no longer due... as debits and credits since then
have equaled $0... Therefore, the $600 on 2019.09.03 would now be the oldest standing debit within this user ID.
I want to highlight it in excel, this is in efforts to later on simply sort by highlight colour and grab those lines
items stating that this USER ID has been due since "insert date". Is this something that is possible?

I have tried a few tings already using conditional formatting, via some IF statements, as well as had looked ad index
and match. But, so far to no avail.
Any help would be greatly appreciated.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Is it POSSIBLE that there could be two or more debits for a user ID that have all been summed to zero ?
Is it POSSIBLE that ALL debits for a user ID have been summed to zero, so that there is NO item that is not yet summed to zero ?
Is it POSSIBLE that there could be two debits for a user ID on the same date ?
Is it POSSIBLE that credits could have exceeded an earlier debit ?
Or two transactions on the same date ? A credit closing off an older item, and then a newer debit ?
Is all the data already sorted first by User ID, then by date ?

Perhaps the problem can be re-stated like this -
Sum all the transactions for a User ID
If the total of all transactions is zero, ignore that User ID (because that means the balance now must be zero)
If the balance has NEVER been zero, show the date of the first debit for that User ID
If the balance has been zero at some point, identify the most recent date when it was zero, and then show the next debit item on or after that date.
Would that do it ?
 
Upvote 0
Is it POSSIBLE that there could be two or more debits for a user ID that have all been summed to zero ?
Is it POSSIBLE that ALL debits for a user ID have been summed to zero, so that there is NO item that is not yet summed to zero ?
Is it POSSIBLE that there could be two debits for a user ID on the same date ?
Is it POSSIBLE that credits could have exceeded an earlier debit ?
Or two transactions on the same date ? A credit closing off an older item, and then a newer debit ?
Is all the data already sorted first by User ID, then by date ?

Perhaps the problem can be re-stated like this -
Sum all the transactions for a User ID
If the total of all transactions is zero, ignore that User ID (because that means the balance now must be zero)
If the balance has NEVER been zero, show the date of the first debit for that User ID
If the balance has been zero at some point, identify the most recent date when it was zero, and then show the next debit item on or after that date.
Would that do it ?

Yes, the data is already sorted by USERD ID, then by date. What you described sounds exactly like what I am trying to do. I just don't know how to implement this into a formula.
 
Upvote 0
OK. By the way, there's often no need to quote an entire post like that, it just makes the thread longer.
Would you mind answering the other questions please, some of these might be relevant ?
 
Upvote 0
Sorry about that, thank you for you help.

Is it POSSIBLE that there could be two or more debits for a user ID that have all been summed to zero ? Yes, this is possible.
Debits can come in at anytime, as well as credits.
Is it POSSIBLE that ALL debits for a user ID have been summed to zero, so that there is NO item that is not yet summed to zero ? I don't fully understand this question
Do you mean, is it possible only credits exist? If so yes, it's possible that all debits have been cleared as they already summed 0.
Is it POSSIBLE that there could be two debits for a user ID on the same date ? This is possible yes.
Is it POSSIBLE that credits could have exceeded an earlier debit ? Yes.
Or two transactions on the same date? Yes. A credit closing off an older item, and then a newer debit ? Yes.
Is all the data already sorted first by User ID, then by date ? Yes.
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top