Looking for a formula to find max in a column based on condition in another

Gopikrishna Mamidipudi

I have Four columns

 

Task Number  Flag1      Flag2        Task name

10001             TRUE      TRUE           A

10002              TRUE      TRUE          B

10003              TRUE      TRUE          C

10004              FALSE      TRUE         D

10005              TRUE        TRUE        E

10006              FALSE      TRUE         F

10007              FALSE      FALSE       G

10008               FALSE     FALSE       H

 

I need to find the task name for the max task number that has FLAG1+FLAG2 as True. This indicates the last task completed on Project. Also, I need to find the next task that is identified by FLAG 1 being false and FLAG2 as being true.

 

In the above scenario 10005|E is the answer for the first. 10006|F is the answer for the second.

L_123

=max(collect([Task Number]:[Task Number],[flag1]:[flag1],1,[flag2]:[flag2]

L_123

Reread and realized I read to fast the first time, tried to fix and got cloudflared. Check below:

=max(collect([Task Number]:[Task Number],[flag1]:[flag1],1,[flag2]:[flag2]

This will get you the correct task. Next we concatenate:

=max(collect([Task Number]:[Task Number],[flag1]:[flag1],1,[flag2]:[flag2]))+" " +Index([task name]:[task name],match(max(collect([Task Number]:[Task Number],[flag1]:[flag1],1,[flag2]:[flag2])),[Task Number]:[Task Number],0))

That will get you your first result. Your next result will be:

=index([Task Number]:[Task Number],max(collect([Task Number]:[Task Number],[flag1]:[flag1],1,[flag2]:[flag2])+1)+" " +Index([task name]:[task name],match(max(collect([Task Number]:[Task Number],[flag1]:[flag1],1,[flag2]:[flag2])),[Task Number]:[Task Number],0)+1)

This should get you your second value

Gopikrishna Mamidipudi

Perfect. This works very well.