Help with column analysis

[Vegonzalez]

I have a table with dispatch data and I need to find the smallest time element when multiple units are on the same call.

If the Incident Number is the same, return the smallest Dispatch to Clear Time in seconds to column BE when Truck 1 annd Engine 2 are on the same call.  Sample data attached.

Thanks

https://www.dropbox.com/scl/fi/ysmjuc2999iulxyw6tl1w/Multi-Unit-Example.xlsx?rlkey=0khujasfjp2u5krkq...

[Anonymous]

Hi @Vegonzalez ,

Did @ryan_mayu  reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

[ryan_mayu]

not clear about your question. How did you get the result in Excel? I didn't see any formula.

[Vegonzalez]

Ryan,

Thanks, that did get the correct output.

[ryan_mayu]

you are welcome

[Vegonzalez]

Apologies.  Here is the sheet with the formula.  What I am trying to find is the amount of time multiple units were busy if they are frontline units.  Column BD has the calculation.  I also cannot figure out how to return just one number per event as I'll need to sum the column.  Many thanks!

https://www.dropbox.com/scl/fi/j3z0i9iluwibjt8rwa5gf/Multi-Unit-Example1.xlsx?rlkey=d1evjggsg10slu5f...

[ryan_mayu · Accepted solution]

@Vegonzalez 

you can try this

Column = minx(FILTER(Sheet1,Sheet1[Incident Number]=EARLIER(Sheet1[Incident Number])&&Sheet1[Frontline Unit]=1&&Sheet1[Multi U]>1),Sheet1[Dispatch To Clear (seconds)])+0

pls see the attachment below