Sep 07 2021 05:58 PM
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.
Sep 07 2021 07:09 PM
Sep 08 2021 09:02 AM
Sep 08 2021 09:29 AM
Then I would suggest PQ. The equivalent of the pivot table is grouping.
From there you can do your other calculations.
Sep 08 2021 12:51 PM
SolutionI'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.
Sep 08 2021 01:48 PM
Sep 09 2021 01:39 AM
@TSmithSIGN , you are welcome, glad it helped
Sep 08 2021 12:51 PM
SolutionI'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.