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
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
Last edited: