Look for the largest value if condition met

Well-known Member
Hello All,

Can someone please help me with a formula to find the largest time value in the scenario below?

 09:06:15 To City 09:22:11 To City 09:23:04 To City 09:27:51 To City 09:28:53 Dead 10:27:05 Dead 10:28:17 Dead 16:25:08 Dead 22:01:08 Dead 22:09:54 0 22:15:32 0 22:18:59 0 22:22:49 To City 22:36:07 To City 22:36:54 To City 22:53:04 To City

<colgroup><col><col></colgroup><tbody>
</tbody>

In the data above, I need formula to give me value 9:27:51. I tried looking for largest value in column A if values in column B for next 20 rows are same. It gives me answer 22:53:04. This is not what I want. I want the answer to be 9:27:51. Is it possible?

Thanks

Excel Facts

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

sandy666

Well-known Member
you can try PowerQuery

 Column1 Column2 Column2 max 09:06:15​ To City To City 09:27:51​ 09:22:11​ To City Dead 22:01:08​ 09:23:04​ To City 0​ 22:18:59​ 09:27:51​ To City To City 22:53:04​ 09:28:53​ Dead 10:27:05​ Dead 10:28:17​ Dead 16:25:08​ Dead 22:01:08​ Dead 22:09:54​ 0​ 22:15:32​ 0​ 22:18:59​ 0​ 22:22:49​ To City 22:36:07​ To City 22:36:54​ To City 22:53:04​ To City

Code:
``````[SIZE=1]let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type time}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"max", each List.Max([Column1]), type time}},GroupKind.Local)
in
#"Grouped Rows"[/SIZE]``````

Well-known Member
Thanks for solution sandy666

Does this mean that there is no formula that can achieve this?

sandy666

Well-known Member
Does this mean that there is no formula that can achieve this?

I don't know but maybe someone else will give you formula solution

Well-known Member

I don't know but maybe someone else will give you formula solution
Thanks for the solution anyway.. If I do not get any formula, I will try to use this power query. I have never used this before, hence a bit reluctant.

sandy666

Well-known Member
I will try to use this power query. I have never used this before, hence a bit reluctant.

No problem,
Power Query for Excel 365/2019/2016 (built-in), Excel 2013/2010 (add-in)

Well-known Member

Finally worked it out. Probably not the best solution, but it will do for the time being.

Code:
``=INDEX(A2:A15,MIN(IF(B2:B15<>B1,ROW(B2:B15)-ROW(B1))))``
entered with ctrl+shift+enter

AhoyNC

Well-known Member
If you have Excel 2010 or later here is a formula using AGGREGATE that doesn't require CTRL-SHIFT-ENTER, just ENTER.
Excel Workbook
ABCD
19:06:15To City9:27:51
29:22:11To City
39:23:04To City
49:27:51To City
1022:09:540
1122:15:320
1222:18:590
1322:22:49To City
1422:36:07To City
1522:36:54To City
1622:53:04To City
Sheet

Well-known Member
Wow,

never used Aggregate function before. Thanks a lot.

Replies
3
Views
34
Replies
1
Views
26
Replies
8
Views
58
Replies
1
Views
44
Replies
1
Views
22