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
34
User ID
Amounts
Posting Date
11
$500
2017.07.24
11
-$150
2019.04.05
11
-$200
2019.05.10
11
$200
2019.06.24
11
-$350
2019.06.25
11
$6002019.09.03
11
-$100
2019.09.07
11
-$200
2019.10.03

<tbody>
</tbody>

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.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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 ?
 

savosean

New Member
Joined
Jun 7, 2018
Messages
34
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.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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 ?
 

savosean

New Member
Joined
Jun 7, 2018
Messages
34
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.
 

Forum statistics

Threads
1,077,736
Messages
5,335,903
Members
399,056
Latest member
CityGirlLuv

Some videos you may like

This Week's Hot Topics

Top