Find Max value, then subtract based on several conditions

rca

Board Regular
Joined
Mar 1, 2005
Messages
182
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!
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

shknbk2

Board Regular
Joined
Mar 5, 2016
Messages
240
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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #333333;;">ID</td><td style=";">Sequence</td><td style=";">Type</td><td style=";">Time</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #333333;;">R0000039</td><td style="text-align: right;;">0</td><td style=";">Manual</td><td style="text-align: right;;">03:11:45.014</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="color: #333333;;">R0000039</td><td style="text-align: right;;">1</td><td style=";">Manual</td><td style="text-align: right;;">03:11:45.018</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="color: #333333;;">R0000039</td><td style="text-align: right;;">2</td><td style=";">Manual</td><td style="text-align: right;;">03:11:45.441</td><td style="text-align: right;;">427</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="color: #333333;;">R0000059</td><td style="text-align: right;;">0</td><td style=";">Manual</td><td style="text-align: right;;">03:14:33.857</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="color: #333333;;">R0000059</td><td style="text-align: right;;">1</td><td style=";">Manual</td><td style="text-align: right;;">03:14:33.860</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="color: #333333;;">R0000059</td><td style="text-align: right;;">2</td><td style=";">Manual</td><td style="text-align: right;;">03:14:33.868</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="color: #333333;;">R0000073</td><td style="text-align: right;;">0</td><td style=";">Internal</td><td style="text-align: right;;">03:17:21.007</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="color: #333333;;">R0000073</td><td style="text-align: right;;">1</td><td style=";">Internal</td><td style="text-align: right;;">03:17:21.010</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="color: #333333;;">R0000073</td><td style="text-align: right;;">2</td><td style=";">Internal</td><td style="text-align: right;;">03:17:21.051</td><td style="text-align: right;;">44</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="color: #333333;;">R0000165</td><td style="text-align: right;;">0</td><td style=";">Automatic</td><td style="text-align: right;;">03:32:31.845</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="color: #333333;;">R0000165</td><td style="text-align: right;;">1</td><td style=";">Automatic</td><td style="text-align: right;;">03:32:31.850</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="color: #333333;;">R0000165</td><td style="text-align: right;;">2</td><td style=";">Automatic</td><td style="text-align: right;;">03:32:34.957</td><td style="text-align: right;;">3112</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E4</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">A4<>A5,A4=A3</font>),(<font color="Red">D4-INDEX(<font color="Green">A:D,MATCH(<font color="Purple">A4,A:A,0</font>),4</font>)</font>)*86400000,""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,099,366
Messages
5,468,205
Members
406,571
Latest member
carrie1994

This Week's Hot Topics

Top