Help needed with a formula please

[kralin]

Hello - I would be very grateful for help with a formula please.
Please see sample dataset.

I need the formula to look at each individual ID and then to return a 'Y' in the row of the Alert with the most recent date (column D) if the ID has had:

either two or more successive instances of 'Did not attend'

Or two or more successive instances of 'did not attend' and 'cancelled' (or variations)

Or two or more successive instances of 'cancelled' and 'cancelled' (or variations)

Or any variation of 'did not attend' or 'cancelled' as long as there are two or more



following an instance of 'attended on time' - and additionally, if there is no 'to be attended' set at a future date.

In the attached example:

ID 1 has not attended but because they have a future date they don't need an alert

ID 2 needs an alert as they have had two instances of 'did not attend' or 'cancelled' since attending.

ID 3 has had two cancellations but has attended since then and so doesn't need an alert.

ID4 has only had cancellations so needs an alert

ID 5 has had a variety of cancellations since attending so would need an alert.


Please note this is sample data in real life the spreadsheet is many 1000s of rows long, so any formula would need to be robust enough to handle a lot of data.

I would only need to see 'Y' or 'N' on the row with the most recent date.