Look for the largest value if condition met

[asad]

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
Asad

 

[sandy666]

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

[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]

Thanks for solution sandy666

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

 

[sandy666]

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

 

[asad]

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]

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)

• Introduction to Microsoft Power Query for Excel
• Microsoft Power Query for Excel Help

 

[asad]

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

=INDEX(A2:A15,MIN(IF(B2:B15<>B1,ROW(B2:B15)-ROW(B1))))

entered with ctrl+shift+enter

 

[AhoyNC]

If you have Excel 2010 or later here is a formula using AGGREGATE that doesn't require CTRL-SHIFT-ENTER, just ENTER.

Excel Workbook
	A	B	C	D
1	9:06:15	To City		9:27:51
2	9:22:11	To City
3	9:23:04	To City
4	9:27:51	To City
5	9:28:53	Dead
6	10:27:05	Dead
7	10:28:17	Dead
8	16:25:08	Dead
9	22:01:08	Dead
10	22:09:54	0
11	22:15:32	0
12	22:18:59	0
13	22:22:49	To City
14	22:36:07	To City
15	22:36:54	To City
16	22:53:04	To City
Sheet

 

[asad]

Wow,

never used Aggregate function before. Thanks a lot.