Help with a formula please

[kralin]

Hello

I am using version 2019.

Hello - I would be very grateful for help with a formula please.
Please see sample dataset (image, hopefully is clear).

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. Many thanks.

 

[Eric W]

To boil down your requirements, it seems that if the 2 most recent entries are "Cancelled" or "Did not attend", then you want an alert. This is easy to do since the dates appear to be in descending order. (There is an exception on rows 15 and 16. If that can actually happen, the formula will need to be revised.) Try:

Book2
	A	B	C	D
1	ID	Date	Attendance?	Alert
2	1	14-Feb-24	to be Attended	N
3	1	21-Nov-23	Did not attend
4	1	21-Nov-23	Did not attend
5	1	14-Nov-23	Attended
6	2	21-Nov-23	Did not attend	Y
7	2	21-Oct-23	Cancelled
8	2	14-Sep-23	Attended
9	3	12-Nov-23	Cancelled	N
10	3	12-Oct-23	Attended
11	3	12-Sep-23	Did not attend
12	3	12-Aug-23	Cancelled
13	4	1-Feb-23	Cancelled	Y
14	4	3-Mar-23	Cancelled
15	4	3-Apr-23	Did not attend
16	4	4-Apr-23	Cancelled
17	5	4-Apr-23	Did not attend	Y
18	5	3-Mar-23	Cancelled
19	5	2-Feb-23	Cancelled
20	5	22-Jan-23	Did not attend
21	5	1-Jan-23	Attended
Sheet5

Cell Formulas
Range	Formula
D2:D21	D2	=IF(A2=A1,"",IF(A2<>A3,"",IF(SUM(COUNTIF(C2:C3,{"Did not*","Can*"}))=2,"Y","N")))

 

[kralin]

To boil down your requirements, it seems that if the 2 most recent entries are "Cancelled" or "Did not attend", then you want an alert. This is easy to do since the dates appear to be in descending order. (There is an exception on rows 15 and 16. If that can actually happen, the formula will need to be revised.) Try:

Book2
	A	B	C	D
1	ID	Date	Attendance?	Alert
2	1	14-Feb-24	to be Attended	N
3	1	21-Nov-23	Did not attend
4	1	21-Nov-23	Did not attend
5	1	14-Nov-23	Attended
6	2	21-Nov-23	Did not attend	Y
7	2	21-Oct-23	Cancelled
8	2	14-Sep-23	Attended
9	3	12-Nov-23	Cancelled	N
10	3	12-Oct-23	Attended
11	3	12-Sep-23	Did not attend
12	3	12-Aug-23	Cancelled
13	4	1-Feb-23	Cancelled	Y
14	4	3-Mar-23	Cancelled
15	4	3-Apr-23	Did not attend
16	4	4-Apr-23	Cancelled
17	5	4-Apr-23	Did not attend	Y
18	5	3-Mar-23	Cancelled
19	5	2-Feb-23	Cancelled
20	5	22-Jan-23	Did not attend
21	5	1-Jan-23	Attended
Sheet5

Cell Formulas
Range	Formula
D2:D21	D2	=IF(A2=A1,"",IF(A2<>A3,"",IF(SUM(COUNTIF(C2:C3,{"Did not*","Can*"}))=2,"Y","N")))

Thanks for taking the time and trouble to get back to me. What you suggested has worked.