How to determine who arrived first

chiefb22enator

New Member
Joined
Jan 28, 2019
Messages
3
I'm trying to determine which fire vehicle arrived first at the scene of an incident (and there are thousands of them). Each incident has a unique incident number, and has one or more apparatus that are dispatched on the call. The arrival times for each unit are logged. But, I need a formula that compares the arrival date/time for each incident and determines who arrived first on each incident so I can sort and analyze further. Here's an example of the data I'm using:
IncNumberIncident CountIncTypeIncDescStationAppIDArvDateArvTime
2019-00000450142Brush, or brush and grass mixture fireST5B3
1/1/2019​
3:43:06​
2019-00000451142Brush, or brush and grass mixture fireST5ME5
1/1/2019​
3:40:06​
2019-00011510142Brush, or brush and grass mixture fireST2B1
1/5/2019​
4:16:36​
2019-00011510142Brush, or brush and grass mixture fireST2B61
1/5/2019​
4:21:16​
2019-00011510142Brush, or brush and grass mixture fireST2ME2
1/5/2019​
4:17:04​
2019-00011511142Brush, or brush and grass mixture fireST2ME3
1/5/2019​
4:15:54​
2019-00011510142Brush, or brush and grass mixture fireST2ME4
1/5/2019​
4:19:09​
2019-00011510142Brush, or brush and grass mixture fireST2ME5
1/5/2019​
4:17:12​
2019-00032811111ABuilding fire - Confined to Area Involved on ArrivalST1MRE23
1/12/2019​
19:04:05​
2019-00046101111ABuilding fire - Confined to Area Involved on ArrivalST3ME4
1/17/2019​
16:19:46​
2019-00046100111ABuilding fire - Confined to Area Involved on ArrivalST3ME7
1/17/2019​
16:18:46​
2019-00063170111ABuilding fire - Confined to Area Involved on ArrivalST5B2
1/23/2019​
22:31:37​
2019-00063171111ABuilding fire - Confined to Area Involved on ArrivalST5ME102
1/23/2019​
22:29:20​
2019-00063170111ABuilding fire - Confined to Area Involved on ArrivalST5ME2
1/23/2019​
22:33:09​
2019-00063170111ABuilding fire - Confined to Area Involved on ArrivalST5ME3
1/23/2019​
22:31:23​
2019-00063170111ABuilding fire - Confined to Area Involved on ArrivalST5ME4
1/23/2019​
22:32:53​
2019-00063170111ABuilding fire - Confined to Area Involved on ArrivalST5MT5
1/23/2019​
22:30:12​
2019-00063241111ABuilding fire - Confined to Area Involved on ArrivalST1ME1
1/23/2019​
22:51:12​
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,302
Welcome to the MrExcel Board!

Try:

Book2
ABCDEFGHI
1IncNumberIncident CountIncTypeIncDescStationAppIDArvDateArvTimeFirst?
22019-00000450142Brush, or brush and grass mixture fireST5B31/1/20203:43:06
32019-00000451142Brush, or brush and grass mixture fireST5ME51/1/20203:40:06Y
42019-00011510142Brush, or brush and grass mixture fireST2B11/5/20204:16:36
52019-00011510142Brush, or brush and grass mixture fireST2B611/5/20204:21:16
62019-00011510142Brush, or brush and grass mixture fireST2ME21/5/20204:17:04
72019-00011511142Brush, or brush and grass mixture fireST2ME31/5/20204:15:54Y
82019-00011510142Brush, or brush and grass mixture fireST2ME41/5/20204:19:09
92019-00011510142Brush, or brush and grass mixture fireST2ME51/5/20204:17:12
102019-00032811111ABuilding fire - Confined to Area Involved on ArrivalST1MRE231/12/202019:04:05Y
112019-00046101111ABuilding fire - Confined to Area Involved on ArrivalST3ME41/17/202016:19:46
122019-00046100111ABuilding fire - Confined to Area Involved on ArrivalST3ME71/17/202016:18:46Y
132019-00063170111ABuilding fire - Confined to Area Involved on ArrivalST5B21/23/202022:31:37
142019-00063171111ABuilding fire - Confined to Area Involved on ArrivalST5ME1021/23/202022:29:20Y
152019-00063170111ABuilding fire - Confined to Area Involved on ArrivalST5ME21/23/202022:33:09
162019-00063170111ABuilding fire - Confined to Area Involved on ArrivalST5ME31/23/202022:31:23
172019-00063170111ABuilding fire - Confined to Area Involved on ArrivalST5ME41/23/202022:32:53
182019-00063170111ABuilding fire - Confined to Area Involved on ArrivalST5MT51/23/202022:30:12
192019-00063241111ABuilding fire - Confined to Area Involved on ArrivalST1ME11/23/202022:51:12Y
Sheet3
Cell Formulas
RangeFormula
I2:I19I2=IF(G2+H2=MIN(IF(A$2:A$19=A2,G$2:G$19+H$2:H$19)),"Y","")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

chiefb22enator

New Member
Joined
Jan 28, 2019
Messages
3
Thanks Eric, I entered the formula, but it simply inserts the Y in every cell, even though the arrival times of the units on the same incident were different. Not sure I understand the direction on using CTRL/SHIFT/ENTER array function.
here is the actual column labels I'm using
Column A
Incident Number
 

chiefb22enator

New Member
Joined
Jan 28, 2019
Messages
3
Thanks Eric, I entered the formula, but it simply inserts the Y in every cell, even though the arrival times of the units on the same incident were different. Not sure I understand the direction on using CTRL/SHIFT/ENTER array function.
here is the actual column labels I'm using
Column A Column z Column AA
Incident Number Arrival Date Arrival time

There are 572 rows So, I used this formula: =IF(Z2+AA2=MIN(IF(A$2:A$572=A2,Z$2:Z$572+AA$2:AA$572)),"Y","")

Any additional ideas/direction would be very welcome!
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,684
maybe something like this?
IncNumberfirstDatefirstTime
2019-000004501/01/201903:40:06
2019-000115105/01/201904:15:54
2019-000328112/01/201919:04:05
2019-000461017/01/201916:18:46
2019-000631723/01/201922:29:20
2019-000632423/01/201922:51:12

Code:
// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"IncType", type text}, {"ArvDate", type date}, {"ArvTime", type time}}),
    Group = Table.Group(Type, {"IncNumber"}, {{"firstDate", each List.Min([ArvDate]), type date}, {"firstTime", each List.Min([ArvTime]), type time}})
in
    Group
btw. update your profile (Account details) about Excel version because I don't know if it will work for you
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,302
Thanks Eric, I entered the formula, but it simply inserts the Y in every cell, even though the arrival times of the units on the same incident were different. Not sure I understand the direction on using CTRL/SHIFT/ENTER array function.
here is the actual column labels I'm using
Column A
Incident Number
The Control+Shift+Enter tells Excel that the formula is an array formula instead of a regular formula. Your adaptation of the ranges seems to be correct. Make sure that the values in column Z are actual Excel dates, and the values in column AA are actual Excel times.

To enter an array formula, enter the formula in the formula bar, and when you're done, hold down the Control and Shift keys, then press Enter. If you do it right, then Excel will put {} on the ends of the formula. (You can't put them there yourself.) If you've already entered the formula, select the cell it's in, press F2, then hold down Control and Shift, and press enter. Once you've done that, you can copy and paste it like any other formula.

I tested the formula in my workbook without the CSE, and I get a Y in every cell like you're getting, so that's likely the issue. Let me know how it works.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,205
Office Version
2007
Platform
Windows
Hi @chiefb22enator welcome to MrExcel.

Here another option with a regular formula, but with a helper column (column I), to consider:


Dante Amor.xlsm
ABCEFGHI
1IncNumberIncident CountIncTypeStationAppIDArvDateArvTimeDate+Time
22019-00000450142ST5B301-ene03:43:06 a.m.01/01/2020 03:43:06 a.m.
32019-00000451142ST5ME501-ene03:40:06 a.m.01/01/2020 03:40:06 a.m.
42019-00011510142ST2B105-ene04:16:36 a.m.05/01/2020 04:16:36 a.m.
52019-00011510142ST2B6105-ene04:21:16 a.m.05/01/2020 04:21:16 a.m.
62019-00011510142ST2ME205-ene04:17:04 a.m.05/01/2020 04:17:04 a.m.
72019-00011511142ST2ME305-ene04:15:54 a.m.05/01/2020 04:15:54 a.m.
82019-00011510142ST2ME405-ene04:19:09 a.m.05/01/2020 04:19:09 a.m.
92019-00011510142ST2ME505-ene04:17:12 a.m.05/01/2020 04:17:12 a.m.
102019-00032811111AST1MRE2312-ene07:04:05 p.m.12/01/2020 07:04:05 p.m.
112019-00046101111AST3ME417-ene04:19:46 p.m.17/01/2020 04:19:46 p.m.
122019-00046100111AST3ME717-ene04:18:46 p.m.17/01/2020 04:18:46 p.m.
132019-00063170111AST5B223-ene10:31:37 p.m.23/01/2020 10:31:37 p.m.
142019-00063171111AST5ME10223-ene10:29:20 p.m.23/01/2020 10:29:20 p.m.
152019-00063170111AST5ME223-ene10:33:09 p.m.23/01/2020 10:33:09 p.m.
162019-00063170111AST5ME323-ene10:31:23 p.m.23/01/2020 10:31:23 p.m.
172019-00063170111AST5ME423-ene10:32:53 p.m.23/01/2020 10:32:53 p.m.
182019-00063170111AST5MT523-ene10:30:12 p.m.23/01/2020 10:30:12 p.m.
192019-00063241111AST1ME123-ene10:51:12 p.m.23/01/2020 10:51:12 p.m.
Sheet1
Cell Formulas
RangeFormula
I2:I19I2=G2+H2

_______________________________________________________________________________

Dante Amor.xlsm
ABCEFGHIJ
1IncNumberIncident CountIncTypeStationAppIDArvDateArvTimeDate+TimeFirst?
22019-00000450142ST5B301-ene03:43:06 a.m.01/01/2020 03:43:06 a.m.
32019-00000451142ST5ME501-ene03:40:06 a.m.01/01/2020 03:40:06 a.m.Y
42019-00011510142ST2B105-ene04:16:36 a.m.05/01/2020 04:16:36 a.m.
52019-00011510142ST2B6105-ene04:21:16 a.m.05/01/2020 04:21:16 a.m.
62019-00011510142ST2ME205-ene04:17:04 a.m.05/01/2020 04:17:04 a.m.
72019-00011511142ST2ME305-ene04:15:54 a.m.05/01/2020 04:15:54 a.m.Y
82019-00011510142ST2ME405-ene04:19:09 a.m.05/01/2020 04:19:09 a.m.
92019-00011510142ST2ME505-ene04:17:12 a.m.05/01/2020 04:17:12 a.m.
102019-00032811111AST1MRE2312-ene07:04:05 p.m.12/01/2020 07:04:05 p.m.Y
112019-00046101111AST3ME417-ene04:19:46 p.m.17/01/2020 04:19:46 p.m.
122019-00046100111AST3ME717-ene04:18:46 p.m.17/01/2020 04:18:46 p.m.Y
132019-00063170111AST5B223-ene10:31:37 p.m.23/01/2020 10:31:37 p.m.
142019-00063171111AST5ME10223-ene10:29:20 p.m.23/01/2020 10:29:20 p.m.Y
152019-00063170111AST5ME223-ene10:33:09 p.m.23/01/2020 10:33:09 p.m.
162019-00063170111AST5ME323-ene10:31:23 p.m.23/01/2020 10:31:23 p.m.
172019-00063170111AST5ME423-ene10:32:53 p.m.23/01/2020 10:32:53 p.m.
182019-00063170111AST5MT523-ene10:30:12 p.m.23/01/2020 10:30:12 p.m.
192019-00063241111AST1ME123-ene10:51:12 p.m.23/01/2020 10:51:12 p.m.Y
Sheet1
Cell Formulas
RangeFormula
J2:J19J2=IF(COUNTIFS($A$2:$A$19,A2,$I$2:$I$19,"<"&I2)=0,"Y","")
 

Forum statistics

Threads
1,089,637
Messages
5,409,467
Members
403,265
Latest member
HMR120

This Week's Hot Topics

Top