SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2730707%22%20slang%3D%22en-US%22%3ETrying%20to%20find%20the%20shortest%20duration%20out%20of%20a%20array%20of%20numbers%20based%20on%20several%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2730707%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3Eattached%20is%20a%20sample%20spreadsheet%2C%20where%20I'm%20trying%20to%20find%20the%20shortest%20number%20of%20days%20in%20the%20attached%20example%20file.%26nbsp%3B%20%26nbsp%3BIt%20has%204%20columns%2C%20the%201st%20column%20is%20a%20list%20of%20%22case%20%23s%22%2C%20each%20case%20may%20have%20multiple%20follow%20ups%20denoted%20by%20a%20unique%20followup%20%23.%26nbsp%3B%20Column%20C%20is%20a%20list%20of%20follow%20up%20days%20for%20the%20Condition%20to%20be%20TRUE%20or%20FALSE.%26nbsp%3B%20%26nbsp%3B%20I'm%20trying%20to%20find%20a%20way%20to%20identify%20the%20shortest%20duration%20(follow%20up%20days)%20when%20healing%20is%20%3D%20TRUE%20for%20a%20given%20case%20%23.%26nbsp%3B%20%26nbsp%3BYou%20can%20see%20that%20there%20are%20some%20cases%20where%20there%20are%20multiple%20TRUE%20conditions%2C%20which%20is%20causing%20me%20grief.%26nbsp%3B%20%26nbsp%3BI%20have%20tried%20a%20few%20variations%20of%20an%20IF%2C%20AND%20with%20some%20success%20but%20trying%20to%20do%20this%20without%20adding%20columns.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2730707%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2730861%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20find%20the%20shortest%20duration%20out%20of%20a%20array%20of%20numbers%20based%20on%20several%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2730861%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1149369%22%20target%3D%22_blank%22%3E%40TSmithSIGN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20a%20pivot%20table%20would%20do%20the%20trick.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2733379%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20find%20the%20shortest%20duration%20out%20of%20a%20array%20of%20numbers%20based%20on%20several%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2733379%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1149369%22%20target%3D%22_blank%22%3E%40TSmithSIGN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20I%20would%20suggest%20PQ.%20The%20equivalent%20of%20the%20pivot%20table%20is%20grouping.%3C%2FP%3E%3CP%3EFrom%20there%20you%20can%20do%20your%20other%20calculations.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2733896%22%20slang%3D%22en-US%22%3ERe%3A%20Trying%20to%20find%20the%20shortest%20duration%20out%20of%20a%20array%20of%20numbers%20based%20on%20several%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2733896%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1149369%22%20target%3D%22_blank%22%3E%40TSmithSIGN%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20not%20sure%20where%20you'd%20like%20to%20place%20these%20numbers%20and%20on%20which%20version%20of%20Excel%20you%20are.%20Formulae%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DXLOOKUP(A2%2C%24A%242%3A%24A%2460%2F%24D%242%3A%24D%2460%2C%24C%242%3A%24C%2460%2C-1%2C0%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIFERROR(%20AGGREGATE(%2015%2C6%2C1%20%2F%20(%24A%242%3A%24A%2460%3DA2)%20%2F%20%24D%242%3A%24D%2460*%24C%242%3A%24C%2460%2C%201%20)%2C%20-1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EIf%20I%20understood%20the%20logic%20correctly.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@TSmithSIGN 

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

@TSmithSIGN 

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.

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