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?

[TABLE="width: 203"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]09:06:15 [/TD]
[TD]To City[/TD]
[/TR]
[TR]
[TD="align: right"]09:22:11 [/TD]
[TD]To City[/TD]
[/TR]
[TR]
[TD="align: right"]09:23:04 [/TD]
[TD]To City[/TD]
[/TR]
[TR]
[TD="align: right"]09:27:51 [/TD]
[TD]To City[/TD]
[/TR]
[TR]
[TD="align: right"]09:28:53 [/TD]
[TD]Dead[/TD]
[/TR]
[TR]
[TD="align: right"]10:27:05[/TD]
[TD] Dead[/TD]
[/TR]
[TR]
[TD="align: right"]10:28:17[/TD]
[TD] Dead[/TD]
[/TR]
[TR]
[TD="align: right"]16:25:08 [/TD]
[TD]Dead[/TD]
[/TR]
[TR]
[TD="align: right"]22:01:08[/TD]
[TD] Dead[/TD]
[/TR]
[TR]
[TD="align: right"]22:09:54[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]22:15:32[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]22:18:59[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]22:22:49[/TD]
[TD] To City[/TD]
[/TR]
[TR]
[TD="align: right"]22:36:07[/TD]
[TD] To City[/TD]
[/TR]
[TR]
[TD="align: right"]22:36:54[/TD]
[TD] To City[/TD]
[/TR]
[TR]
[TD="align: right"]22:53:04[/TD]
[TD] To City[/TD]
[/TR]
</tbody>[/TABLE]

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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td][/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]max[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

09:06:15​

[/td][td=bgcolor:#DDEBF7]To City[/td][td][/td][td=bgcolor:#E2EFDA]To City[/td][td=bgcolor:#E2EFDA]

09:27:51​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

09:22:11​

[/td][td]To City[/td][td][/td][td]Dead[/td][td]

22:01:08​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

09:23:04​

[/td][td=bgcolor:#DDEBF7]To City[/td][td][/td][td=bgcolor:#E2EFDA]

0​

[/td][td=bgcolor:#E2EFDA]

22:18:59​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

09:27:51​

[/td][td]To City[/td][td][/td][td]To City[/td][td]

22:53:04​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

09:28:53​

[/td][td=bgcolor:#DDEBF7]Dead[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

10:27:05​

[/td][td]Dead[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

10:28:17​

[/td][td=bgcolor:#DDEBF7]Dead[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

16:25:08​

[/td][td]Dead[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

22:01:08​

[/td][td=bgcolor:#DDEBF7]Dead[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

22:09:54​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

22:15:32​

[/td][td=bgcolor:#DDEBF7]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

22:18:59​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

22:22:49​

[/td][td=bgcolor:#DDEBF7]To City[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

22:36:07​

[/td][td]To City[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

22:36:54​

[/td][td=bgcolor:#DDEBF7]To City[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

22:53:04​

[/td][td]To City[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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