# || Challenge for Team ||

Copper Contributor

# || 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
3 Replies

# Re: || Challenge for Team ||

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

# Re: || Challenge for Team ||

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