Aug 06 2024 03:26 AM
Dear Team
This mail is offering a challenge to your team; So, the challenge is: - Suppose an Employee Muster Roll (A sheet that shows employee status daily wise whether he/she is present or not) is there and my requirement is one wants to find out how many employees are there who are present continuously for 10 days or more, how to find this.
I am attaching a file for more details
Mr. X | PP | PP | PP | PP | PP | PP | PP | PP | PP | PP | PP | WO | WO | PP | PP | PP |
Mr. A | PP | PP | PP | PP | WO | WO | PP | PP | PP | PP | PP | WO | WO | PP | PP | PP |
Mr. B | PP | PP | PP | PP | WO | WO | PP | PP | PP | PP | PP | WO | WO | PP | PP | PP |
Aug 06 2024 03:31 AM
If you cannot attach a sample workbook, please upload it to a cloud store such as Google Drive, Onedrive or Dropbox, then obtain a link to the uploaded file that allows access to it, and post that link in a reply.
Aug 06 2024 06:13 AM - edited Aug 06 2024 06:15 AM
Please create the Helper column and enter the below formula in each row
=MAX(SCAN(0,B2:Q2,LAMBDA(ini,arr,IFS(arr<>"PP",ini,OFFSET(arr,0,-1)<>arr,0,arr="PP",ini+COUNTA(arr))))+1)
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | Helper Column | |
Mr. X | pp | pp | pp | pp | pp | pp | pp | pp | pp | PP | PP | WO | WO | PP | PP | PP | 11 |
Mr. A | PP | PP | PP | PP | WO | WO | PP | PP | PP | PP | PP | WO | WO | PP | PP | PP | 5 |
Mr. B | PP | PP | PP | PP | WO | WO | PP | PP | PP | PP | PP | WO | WO | PP | PP | PP | 5 |
Please enter below formula below the table
=FILTER(A2:A4,R2:R4>=10)
It will give you below result
Mr. X |
Tejas Shah
Aug 06 2024 09:00 AM
You could use BYROW:
=LET(
ten_consecutive, REPT("PP", 10),
check, LAMBDA(each_row,
LET(
joined, CONCAT(each_row),
streak, XMATCH("*" & ten_consecutive & "*", joined, 2),
IF(ISNUMBER(streak), "Yes", "No")
)
),
BYROW(schedule, check)
)