• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding the 2nd matching occurrence

gemchild79

New Member
Hello! I am looking to find the second occurrence of data using this spreadsheet. What I am needing to locate is the amount of time for the second break that was taken by each employee. The breaks are all coded with the number 3 in the D column of the sheet titled 'Monday'. I need to pull the amount of time the employee took for their break from column E in the corresponding row. Where I am having trouble is pulling the second break information because it is also coded with the number 3. I have included the sheets I am working with and here is the code used to find the original break period:

=IF(OR(B3="",D3="NCNS",D3="Sick",D3="absent",D3="vac",D3="term"),"",INDEX('Mon State Detail'!A:E,MATCH(1,('Mon State Detail'!A:A=A3)*('Mon State Detail'!D:D=3),0),5))

I greatly appreciate all of your help!
 

Attachments

  • check in master.xls
    766 KB · Views: 6
Hi,

Just double checking...Amanda Rowan has only 1 break however, Anna Brock has two...and the second one is 00:15:02
 
You can use the below in J2 and drag it down..

Iferror(INDEX('Mon State Detail'!$E$2:$E$563,MATCH(SMALL(IF('Mon State Detail'!$D$2:$D$563=3,IF('Mon State Detail'!$A$2:$A$563=$A3,ROW(E2:E563))),2),ROW($E$2:$E$563),0)),"")

To be acknowledged with CSE..

See the attached..

if otherwise let us know..
 

Attachments

  • check in master.xls
    760.5 KB · Views: 8
Back
Top