WPS Spreadsheet Formula to Flag Repeated Cancellations or Nonattendance
Question details
Create a Y or N alert on the most recent row for each ID when at least two consecutive statuses are Did not attend or Cancelled after the last attendance.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A large attendance dataset contains IDs, dates, and attendance statuses. The user needs an efficient formula that identifies repeated cancellations or nonattendance while ignoring IDs with a future To be attended date.
- Observed behavior
- The worksheet needs to return a unique list of IDs that meet the alert condition and show Y only on the latest row for each matching ID; other latest rows should show N.
Arrange the source data in columns A:C as ID, date, and status, and confirm the status text exactly matches Did not attend, Cancelled, and To be attended where applicable. For the row-level alert, sort the worksheet by ID ascending and date descending so the newest record appears first for each ID.
Use a LET formula to generate alert IDs and mark the latest row
Use this method for large datasets because it creates one spilled list of matching IDs before applying the Y or N alert.
The formula sorts the source data by ID and descending date, examines the first two records for each ID, and returns IDs whose relevant statuses occur twice. It then uses the spilled list to flag only the first, most recent row for each ID.
This approach assumes the source range is A2:C10001, with ID in column A, date in column B, and status in column C. Expand the range to match your worksheet.
Select cell F2 and enter this formula: =LET(data,SORT(A2:C10001,{1,2},{1,-1}),id,CHOOSECOLS(data,1),next,id=DROP(VSTACK("",id),-1),num,SCAN(0,next,LAMBDA(v,n,IF(n,v+1,1))),two,FILTER(data,num<3),status,CHOOSECOLS(two,3),arr,FILTER(CHOOSECOLS(two,1),(status="Did not attend")+(status="Cancelled")),unq,UNIQUE(arr),incl,BYROW(unq,LAMBDA(r,SUM(N(r=arr))=2)),FILTER(unq,incl))
In the Alert column, enter =IF(A2=A1,"",IF(COUNTIF($F$2#,A2),"Y","N")), then fill the formula down. The first row for each ID receives Y when that ID appears in the spilled alert list; otherwise it receives N.
Review the sorted results so the first row for each ID is the most recent date. Confirm that a future To be attended record prevents the ID from being treated as a repeated cancellation or nonattendance.
Build a Dynamic Attendance Alert in WPS Spreadsheet
WPS Spreadsheet can sort the attendance records, evaluate consecutive statuses with a LET formula, spill the matching IDs into a helper area, and mark the newest row for each ID with a clear Y or N alert.
- 1. Prepare the worksheet: Place ID, date, and status in columns A, B, and C, then sort the records by ID ascending and date descending from the Data menu.
- 2. Enter the helper formula: Select F2 and paste the LET formula to generate the spilled list of IDs requiring an alert.
- 3. Enter the alert formula: In the Alert column, use =IF(A2=A1,"",IF(COUNTIF($F$2#,A2),"Y","N")) and fill it down the records.
- 4. Verify the results: Check that Y appears only on the newest row for IDs with two consecutive Did not attend or Cancelled statuses, while other IDs show N.
Frequently Asked Questions
Which columns should contain the ID, date, and attendance status?
Use column A for the ID, column B for the date, and column C for the status. If your data uses different columns, update the source range and CHOOSECOLS references in the LET formula.
Why must the records be sorted by ID and date?
The alert formula marks the first row for each ID. Sorting IDs ascending and dates descending ensures that this first row is the most recent record.
How do I change the status values used by the formula?
Edit the text comparisons inside the formula, such as status="Did not attend" and status="Cancelled", so they exactly match the labels in your worksheet.
How can I prevent a future To be attended date from triggering an alert?
Keep the future To be attended record in the source data and ensure the records are sorted by date descending. The formula evaluates the newest records first, so a qualifying future attendance status prevents the ID from being included in the alert list.
What does the # symbol mean in $F$2#?
The # references the entire spilled result that begins in F2. When the list changes, COUNTIF automatically checks the updated spilled range.
