Index/Match?

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,033
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Looks like...

In D2 control+shift+enter, not just enter, and copy down:
Rich (BB code):
=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>
 
Last edited:

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,033
Office Version
  1. 365
Platform
  1. Windows
Perfect, thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,911
Messages
5,598,819
Members
414,260
Latest member
joishe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top