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

[GB]

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

 

[bosco_yip]

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)),"")



Regards
Bosco

 

[GB]

Hi Bosco, this is great. Thanks so much.
regards
GB