Index/Match?

[MOB]

See grid below - 3 Columns with Date/Time, Room No (eg GF Bedroom 17) and Type of Call

In column D I need a formula that if column C contains "Reset" it will look below that row in column B for the same room number, and for the row it finds, column C must also contain "Call" - it should then calculate the difference in times in column A

Eg below - Room 20 Call was 11:19, Reset was 11:20 so it should return 1 minute in cell D4

Hope that makes sense?

TIA

Date And Time Room Number Type Of Call
25/02/2016 11:21 GF BEDROOM 17 Present
25/02/2016 11:20 GF BEDROOM 17 Priority
25/02/2016 11:20 GF BEDROOM 20 Reset
25/02/2016 11:19 GF BEDROOM 10 Reset
25/02/2016 11:19 GF BEDROOM 20 Call
25/02/2016 11:17 GF BEDROOM 10 Priority
25/02/2016 11:16 GF BEDROOM 17 Call
25/02/2016 11:16 GF BEDROOM 26 Reset
25/02/2016 11:16 GF BEDROOM 26 Present
25/02/2016 11:15 GF BEDROOM 26 Priority
25/02/2016 11:14 GF BEDROOM 10 Call
25/02/2016 11:12 GF BEDROOM 26 Call
25/02/2016 11:10 GF BEDROOM 8 Reset
25/02/2016 11:05 GF BEDROOM 8 Assistance
25/02/2016 11:03 GF BEDROOM 8 Call
25/02/2016 11:00 SYSTEM Alive Check
25/02/2016 10:39 GF BEDROOM 8 Reset
25/02/2016 10:36 GF BEDROOM 8 Call
25/02/2016 10:25 GF BEDROOM 2 Reset
25/02/2016 10:24 GF BEDROOM 2 Call
25/02/2016 10:22 GF BEDROOM 6 Reset
25/02/2016 10:21 GF BEDROOM 6 Call
25/02/2016 10:19 GF DINING ROOM Reset
25/02/2016 10:19 GF DINING ROOM Present
25/02/2016 10:19 GF DINING ROOM Call
25/02/2016 10:00 SYSTEM Alive Check
25/02/2016 09:56 GF BEDROOM 2 Reset
25/02/2016 09:54 GF BEDROOM 2 Call
25/02/2016 09:35 FF BEDROOM 55 Reset
25/02/2016 09:33 FF BEDROOM 55 Priority
25/02/2016 09:30 FF BEDROOM 55 Call
25/02/2016 09:28 GF BEDROOM 21 Reset
25/02/2016 09:28 GF BEDROOM 21 Priority
25/02/2016 09:24 GF BEDROOM 21 Call

 

[Aladin Akyurek]

Looks like...

In D2 control+shift+enter, not just enter, and copy down:

=IF($C2="reset",INDEX($A$2:$A$35,MATCH(1,IF(ROW($A$2:$A$35)>ROW($A2),
    IF($B$2:$B$35=$B2,IF($C$2:$C$35="call",1))),0))-$A2,"")

<strike></strike>

 

[MOB]

Perfect, thanks!

 

[Aladin Akyurek]

Perfect, thanks!

You are welcome.