|| Challenge for Team ||

Copper Contributor

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. XPPPPPPPPPPPPPPPPPPPPPPWOWOPPPPPP
Mr. APPPPPPPPWOWOPPPPPPPPPPWOWOPPPPPP
Mr. BPPPPPPPPWOWOPPPPPPPPPPWOWOPPPPPP
3 Replies

@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.

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

 12345678910111213141516Helper Column
Mr. XppppppppppppppppppPPPPWOWOPPPPPP11
Mr. APPPPPPPPWOWOPPPPPPPPPPWOWOPPPPPP5
Mr. BPPPPPPPPWOWOPPPPPPPPPPWOWOPPPPPP5

Please enter below formula below the table

=FILTER(A2:A4,R2:R4>=10)

It will give you below result 

Mr. X

 

Tejas Shah

 

 

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

Patrick2788_0-1722959958120.png