Find Max value, then subtract based on several conditions

[rca]

Hi All,

I have the following data set:

ID Sequence Type Time
R0000039 0 Manual 03:11:45.014
R0000039 1 Manual 03:11:45.018
R0000039 2 Manual 03:11:45.441
R0000059 0 Manual 03:14:33.857
R0000059 1 Manual 03:14:33.860
R0000059 2 Manual 03:14:33.868
R0000073 0 Internal 03:17:21.007
R0000073 1 Internal 03:17:21.010
R0000073 2 Internal 03:17:21.051
R0000165 0 Automatic 03:32:31.845
R0000165 1 Automatic 03:32:31.850
R0000165 2 Automatic 03:32:34.957

I'm interested in creating a separate column that:
a) looks up the ID (column A)
b) for each unique ID, finds the largest Sequence #
c) takes the Time for the largest Sequence #
d) subtracts the Time found in step "c" from the Time corresponding to the smallest Sequence number for that given ID

For example, the R0000039 ID would be 03:11:45.441 - 03:11:45.014, or 427 milliseconds.

I've tried to use MAXIFS(), but that didn't seem to work.

Any ideas on how to tackle this (either with VBA or a formula)? Thanks!

 

[shknbk2]

Someone else might have a more elegant solution, but here's my first attempt. I'm not sure about your entire dataset, so this might not work if you have multiple sets of the same ID in different sections of the data. This solution will only work if there is one ID set and if it always starts with 0.

Book1
	A	B	C	D	E
1	ID	Sequence	Type	Time
2	R0000039	0	Manual	03:11:45.014
3	R0000039	1	Manual	03:11:45.018
4	R0000039	2	Manual	03:11:45.441	427
5	R0000059	0	Manual	03:14:33.857
6	R0000059	1	Manual	03:14:33.860
7	R0000059	2	Manual	03:14:33.868	11
8	R0000073	0	Internal	03:17:21.007
9	R0000073	1	Internal	03:17:21.010
10	R0000073	2	Internal	03:17:21.051	44
11	R0000165	0	Automatic	03:32:31.845
12	R0000165	1	Automatic	03:32:31.850
13	R0000165	2	Automatic	03:32:34.957	3112
Sheet1

Cell Formulas
Range	Formula
E4	=IF(AND(A4<>A5,A4=A3),(D4-INDEX(A:D,MATCH(A4,A:A,0),4))*86400000,"")