Look for the largest value if condition met

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,421
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:540
22:15:320
22:18:590
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
Asad
 

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

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,757
you can try PowerQuery

Column1Column2Column2max
09:06:15​
To CityTo City
09:27:51​
09:22:11​
To CityDead
22:01:08​
09:23:04​
To City
0​
22:18:59​
09:27:51​
To CityTo 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]
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,421
Thanks for solution sandy666

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

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,421

ADVERTISEMENT

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.
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,421

ADVERTISEMENT

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
Joined
Oct 10, 2011
Messages
4,620
Office Version
  1. 365
Platform
  1. Windows
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
59:28:53Dead
610:27:05Dead
710:28:17Dead
816:25:08Dead
922:01:08Dead
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
 

asad

Well-known Member
Joined
Sep 9, 2008
Messages
1,421
Wow,

never used Aggregate function before. Thanks a lot.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,087
Messages
5,526,772
Members
409,720
Latest member
Antwain

This Week's Hot Topics

Top