• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find Max Date in consecutive days where multiple conditions the same.

GB

Member
I am trying to write a formula from the data in columns A to E which finds the maximum "Date To" where a "Staff" is the same and "Activity Code" is the same in a consecutive run of days in "Date To" column, then place answer into the "End" column. I have provide the expected output in the "End" column. Refer file or screenshot.

Appreciate any help here.
Regards
GB
66409
 

Attachments

bosco_yip

Excel Ninja
Try,

In the "End" column F2, formula copied down :

=IF((A2<>"")*((A2<>A1)+((B2-N(B1))>1)+(E2<>E1)),INDEX(B2:B$50,MATCH(1,INDEX(0+(((A3:A$51<>A2:A$50)+(B3:B$51-B2:B$50<>1)+(E3:E$51<>E2:E$50))>0),0),0)),"")

66415

Regards
Bosco
 

Attachments

Top