Forum Discussion
|| Challenge for Team ||
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 |
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.
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
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) )
