How to determine who arrived first

[chiefb22enator]

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:

IncNumber	Incident Count	IncType	IncDesc	Station	AppID	ArvDate	ArvTime
2019-0000045	0	142	Brush, or brush and grass mixture fire	ST5	B3	1/1/2019​	3:43:06​
2019-0000045	1	142	Brush, or brush and grass mixture fire	ST5	ME5	1/1/2019​	3:40:06​
2019-0001151	0	142	Brush, or brush and grass mixture fire	ST2	B1	1/5/2019​	4:16:36​
2019-0001151	0	142	Brush, or brush and grass mixture fire	ST2	B61	1/5/2019​	4:21:16​
2019-0001151	0	142	Brush, or brush and grass mixture fire	ST2	ME2	1/5/2019​	4:17:04​
2019-0001151	1	142	Brush, or brush and grass mixture fire	ST2	ME3	1/5/2019​	4:15:54​
2019-0001151	0	142	Brush, or brush and grass mixture fire	ST2	ME4	1/5/2019​	4:19:09​
2019-0001151	0	142	Brush, or brush and grass mixture fire	ST2	ME5	1/5/2019​	4:17:12​
2019-0003281	1	111A	Building fire - Confined to Area Involved on Arrival	ST1	MRE23	1/12/2019​	19:04:05​
2019-0004610	1	111A	Building fire - Confined to Area Involved on Arrival	ST3	ME4	1/17/2019​	16:19:46​
2019-0004610	0	111A	Building fire - Confined to Area Involved on Arrival	ST3	ME7	1/17/2019​	16:18:46​
2019-0006317	0	111A	Building fire - Confined to Area Involved on Arrival	ST5	B2	1/23/2019​	22:31:37​
2019-0006317	1	111A	Building fire - Confined to Area Involved on Arrival	ST5	ME102	1/23/2019​	22:29:20​
2019-0006317	0	111A	Building fire - Confined to Area Involved on Arrival	ST5	ME2	1/23/2019​	22:33:09​
2019-0006317	0	111A	Building fire - Confined to Area Involved on Arrival	ST5	ME3	1/23/2019​	22:31:23​
2019-0006317	0	111A	Building fire - Confined to Area Involved on Arrival	ST5	ME4	1/23/2019​	22:32:53​
2019-0006317	0	111A	Building fire - Confined to Area Involved on Arrival	ST5	MT5	1/23/2019​	22:30:12​
2019-0006324	1	111A	Building fire - Confined to Area Involved on Arrival	ST1	ME1	1/23/2019​	22:51:12​

 

[Eric W]

Welcome to the MrExcel Board!

Try:

Book2
	A	B	C	D	E	F	G	H	I
1	IncNumber	Incident Count	IncType	IncDesc	Station	AppID	ArvDate	ArvTime	First?
2	2019-0000045	0	142	Brush, or brush and grass mixture fire	ST5	B3	1/1/2020	3:43:06
3	2019-0000045	1	142	Brush, or brush and grass mixture fire	ST5	ME5	1/1/2020	3:40:06	Y
4	2019-0001151	0	142	Brush, or brush and grass mixture fire	ST2	B1	1/5/2020	4:16:36
5	2019-0001151	0	142	Brush, or brush and grass mixture fire	ST2	B61	1/5/2020	4:21:16
6	2019-0001151	0	142	Brush, or brush and grass mixture fire	ST2	ME2	1/5/2020	4:17:04
7	2019-0001151	1	142	Brush, or brush and grass mixture fire	ST2	ME3	1/5/2020	4:15:54	Y
8	2019-0001151	0	142	Brush, or brush and grass mixture fire	ST2	ME4	1/5/2020	4:19:09
9	2019-0001151	0	142	Brush, or brush and grass mixture fire	ST2	ME5	1/5/2020	4:17:12
10	2019-0003281	1	111A	Building fire - Confined to Area Involved on Arrival	ST1	MRE23	1/12/2020	19:04:05	Y
11	2019-0004610	1	111A	Building fire - Confined to Area Involved on Arrival	ST3	ME4	1/17/2020	16:19:46
12	2019-0004610	0	111A	Building fire - Confined to Area Involved on Arrival	ST3	ME7	1/17/2020	16:18:46	Y
13	2019-0006317	0	111A	Building fire - Confined to Area Involved on Arrival	ST5	B2	1/23/2020	22:31:37
14	2019-0006317	1	111A	Building fire - Confined to Area Involved on Arrival	ST5	ME102	1/23/2020	22:29:20	Y
15	2019-0006317	0	111A	Building fire - Confined to Area Involved on Arrival	ST5	ME2	1/23/2020	22:33:09
16	2019-0006317	0	111A	Building fire - Confined to Area Involved on Arrival	ST5	ME3	1/23/2020	22:31:23
17	2019-0006317	0	111A	Building fire - Confined to Area Involved on Arrival	ST5	ME4	1/23/2020	22:32:53
18	2019-0006317	0	111A	Building fire - Confined to Area Involved on Arrival	ST5	MT5	1/23/2020	22:30:12
19	2019-0006324	1	111A	Building fire - Confined to Area Involved on Arrival	ST1	ME1	1/23/2020	22:51:12	Y
Sheet3

Cell Formulas
Range	Formula
I2:I19	I2	=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]

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]

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]

maybe something like this?

IncNumber	firstDate	firstTime
2019-0000045	01/01/2019	03:40:06
2019-0001151	05/01/2019	04:15:54
2019-0003281	12/01/2019	19:04:05
2019-0004610	17/01/2019	16:18:46
2019-0006317	23/01/2019	22:29:20
2019-0006324	23/01/2019	22:51:12

// 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

 

[Eric W]

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]

Hi @chiefb22enator welcome to MrExcel.

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

Dante Amor.xlsm
	A	B	C		E	F	G	H	I
1	IncNumber	Incident Count	IncType		Station	AppID	ArvDate	ArvTime	Date+Time
2	2019-0000045	0	142		ST5	B3	01-ene	03:43:06 a.m.	01/01/2020 03:43:06 a.m.
3	2019-0000045	1	142		ST5	ME5	01-ene	03:40:06 a.m.	01/01/2020 03:40:06 a.m.
4	2019-0001151	0	142		ST2	B1	05-ene	04:16:36 a.m.	05/01/2020 04:16:36 a.m.
5	2019-0001151	0	142		ST2	B61	05-ene	04:21:16 a.m.	05/01/2020 04:21:16 a.m.
6	2019-0001151	0	142		ST2	ME2	05-ene	04:17:04 a.m.	05/01/2020 04:17:04 a.m.
7	2019-0001151	1	142		ST2	ME3	05-ene	04:15:54 a.m.	05/01/2020 04:15:54 a.m.
8	2019-0001151	0	142		ST2	ME4	05-ene	04:19:09 a.m.	05/01/2020 04:19:09 a.m.
9	2019-0001151	0	142		ST2	ME5	05-ene	04:17:12 a.m.	05/01/2020 04:17:12 a.m.
10	2019-0003281	1	111A		ST1	MRE23	12-ene	07:04:05 p.m.	12/01/2020 07:04:05 p.m.
11	2019-0004610	1	111A		ST3	ME4	17-ene	04:19:46 p.m.	17/01/2020 04:19:46 p.m.
12	2019-0004610	0	111A		ST3	ME7	17-ene	04:18:46 p.m.	17/01/2020 04:18:46 p.m.
13	2019-0006317	0	111A		ST5	B2	23-ene	10:31:37 p.m.	23/01/2020 10:31:37 p.m.
14	2019-0006317	1	111A		ST5	ME102	23-ene	10:29:20 p.m.	23/01/2020 10:29:20 p.m.
15	2019-0006317	0	111A		ST5	ME2	23-ene	10:33:09 p.m.	23/01/2020 10:33:09 p.m.
16	2019-0006317	0	111A		ST5	ME3	23-ene	10:31:23 p.m.	23/01/2020 10:31:23 p.m.
17	2019-0006317	0	111A		ST5	ME4	23-ene	10:32:53 p.m.	23/01/2020 10:32:53 p.m.
18	2019-0006317	0	111A		ST5	MT5	23-ene	10:30:12 p.m.	23/01/2020 10:30:12 p.m.
19	2019-0006324	1	111A		ST1	ME1	23-ene	10:51:12 p.m.	23/01/2020 10:51:12 p.m.
Sheet1

Cell Formulas
Range	Formula
I2:I19	I2	=G2+H2

_______________________________________________________________________________

Dante Amor.xlsm
	A	B	C		E	F	G	H	I	J
1	IncNumber	Incident Count	IncType		Station	AppID	ArvDate	ArvTime	Date+Time	First?
2	2019-0000045	0	142		ST5	B3	01-ene	03:43:06 a.m.	01/01/2020 03:43:06 a.m.
3	2019-0000045	1	142		ST5	ME5	01-ene	03:40:06 a.m.	01/01/2020 03:40:06 a.m.	Y
4	2019-0001151	0	142		ST2	B1	05-ene	04:16:36 a.m.	05/01/2020 04:16:36 a.m.
5	2019-0001151	0	142		ST2	B61	05-ene	04:21:16 a.m.	05/01/2020 04:21:16 a.m.
6	2019-0001151	0	142		ST2	ME2	05-ene	04:17:04 a.m.	05/01/2020 04:17:04 a.m.
7	2019-0001151	1	142		ST2	ME3	05-ene	04:15:54 a.m.	05/01/2020 04:15:54 a.m.	Y
8	2019-0001151	0	142		ST2	ME4	05-ene	04:19:09 a.m.	05/01/2020 04:19:09 a.m.
9	2019-0001151	0	142		ST2	ME5	05-ene	04:17:12 a.m.	05/01/2020 04:17:12 a.m.
10	2019-0003281	1	111A		ST1	MRE23	12-ene	07:04:05 p.m.	12/01/2020 07:04:05 p.m.	Y
11	2019-0004610	1	111A		ST3	ME4	17-ene	04:19:46 p.m.	17/01/2020 04:19:46 p.m.
12	2019-0004610	0	111A		ST3	ME7	17-ene	04:18:46 p.m.	17/01/2020 04:18:46 p.m.	Y
13	2019-0006317	0	111A		ST5	B2	23-ene	10:31:37 p.m.	23/01/2020 10:31:37 p.m.
14	2019-0006317	1	111A		ST5	ME102	23-ene	10:29:20 p.m.	23/01/2020 10:29:20 p.m.	Y
15	2019-0006317	0	111A		ST5	ME2	23-ene	10:33:09 p.m.	23/01/2020 10:33:09 p.m.
16	2019-0006317	0	111A		ST5	ME3	23-ene	10:31:23 p.m.	23/01/2020 10:31:23 p.m.
17	2019-0006317	0	111A		ST5	ME4	23-ene	10:32:53 p.m.	23/01/2020 10:32:53 p.m.
18	2019-0006317	0	111A		ST5	MT5	23-ene	10:30:12 p.m.	23/01/2020 10:30:12 p.m.
19	2019-0006324	1	111A		ST1	ME1	23-ene	10:51:12 p.m.	23/01/2020 10:51:12 p.m.	Y
Sheet1

Cell Formulas
Range	Formula
J2:J19	J2	=IF(COUNTIFS($A$2:$A$19,A2,$I$2:$I$19,"<"&I2)=0,"Y","")