 SOLVED

# Trying to find the shortest duration out of a array of numbers based on several conditions

Hello,

attached is a sample spreadsheet, where I'm trying to find the shortest number of days in the attached example file.   It has 4 columns, the 1st column is a list of "case #s", each case may have multiple follow ups denoted by a unique followup #.  Column C is a list of follow up days for the Condition to be TRUE or FALSE.    I'm trying to find a way to identify the shortest duration (follow up days) when healing is = TRUE for a given case #.   You can see that there are some cases where there are multiple TRUE conditions, which is causing me grief.   I have tried a few variations of an IF, AND with some success but trying to do this without adding columns.

6 Replies

# Re: Trying to find the shortest duration out of a array of numbers based on several conditions

I think a pivot table would do the trick.

# Re: Trying to find the shortest duration out of a array of numbers based on several conditions

Detlef,
thank you it does. However, it looks like I over simplified my questions by removing too much information from my sample spreadsheet. The original dataset has an roughly 12 other columns of data that will be used to do some additional analyses based on the shortest duration found for a given case #. Ideally I'd be able to do this with the original set of data/columns.

# Re: Trying to find the shortest duration out of a array of numbers based on several conditions

Then I would suggest PQ. The equivalent of the pivot table is grouping.

From there you can do your other calculations.

best response confirmed by TSmithSIGN (New Contributor)
Solution

# Re: Trying to find the shortest duration out of a array of numbers based on several conditions

I'm not sure where you'd like to place these numbers and on which version of Excel you are. Formulae could be

``=XLOOKUP(A2,\$A\$2:\$A\$60/\$D\$2:\$D\$60,\$C\$2:\$C\$60,-1,0,1)``

or

``=IFERROR( AGGREGATE( 15,6,1 / (\$A\$2:\$A\$60=A2) / \$D\$2:\$D\$60*\$C\$2:\$C\$60, 1 ), -1)``

If I understood the logic correctly.

# Re: Trying to find the shortest duration out of a array of numbers based on several conditions

Thank you Sergei,
both work like I was hoping. I like how you used the / in the lookup array with the XLOOKUP function!

# Re: Trying to find the shortest duration out of a array of numbers based on several conditions

@TSmithSIGN , you are welcome, glad it helped