|| Challenge for Team ||

[rogGX1]

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

[#2]

@rogGX1 

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.

[#3]

@rogGX1 

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

[#4]

@rogGX1 

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)
)