Find latest date in excel based on multiple conditions

[mk0410]

I need to figure out a formula or pivot table show me if the Open test date is the most recent date in a table. So this is for a table of test types, each having multiple occurences on the report. I need to determine if the Open test was ordered more recently than the tests done. I've set up the 3 scenarios here. The ABC test has the open one with the most recent date. The LMN one the open date is between two closed ones. The QRS one has both the closed ones with more recent dates than the open one. XYZ has two open orders, neither are the most recent. Testing Example.xlsx

Test Date Done NEED
ABC 8/1/2024 Open Most Recent
ABC 7/1/2024 Closed
ABC 6/1/2024 Closed
LMN 2/2/2024 Closed
LMN 3/2/2024 Open No
LMN 4/2/2024 Closed
QRS 9/3/2024 Closed
QRS 7/3/2024 Closed
QRS 5/3/2024 Open No
XYZ 1/4/2021 Open No
XYZ 5/3/2021 Closed
XYZ 7/7/2021 Open No
XYZ 9/12/2021 Closed

[6StringJazzer]

Welcome to the Forum mk0410!

This probably needs an array formula (might also be solved with SUMPRODUCT).

In D2 and copy down:

Please Login or Register  to view this content.

This is an array formula. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.

[CARACALLA]

D2=IF($C2<>"",IF(C2="Closed","",IF(B2=AGGREGATE(14,6,$B$2:$B$14/($C$2:$C$14="Open"),1),"Most Recent","No")),"")

Copy down

[mk0410]

Is there a way to write this formula so you could compare just the values in the A column if they are the same? So get the most recent date of all the ABCs, then the most recent date for the LMNs, etc.

[6StringJazzer]

Please revise the sample result in your file so we can understand what you're after.

[josephteh]

Try, =IF(C2="Closed","",IF(AGGREGATE(14,6,$B$2:$B$14/($A$2:$A$14=A2),1)=B2,"Most Recent","No"))

[CARACALLA]

H2=IFERROR(INDEX($A$2:$A$100,SMALL(IF(FREQUENCY(IF($A$2:$A$100<>"",MATCH($A$2:$A$100,$A$2:$A$100,0)),ROW($A$2:$A$100)-ROW($A$2)+1),ROW(A2:A100)-ROW($A$2)+1),ROWS($A$1:A1))),"")

Control+shift+enter

copy down

I2=IF($F$1="all",IFERROR(AGGREGATE(14,6,$B$2:$B$100/($A$2:$A$100=H2)/($B$2:$B$100<>""),1),""),IFERROR(AGGREGATE(14,6,$B$2:$B$100/($A$2:$A$100=H2)/($C$2:$C$100=$F$1)/($B$2:$B$100<>""),1),""))

Copy down

select with drop down list in F1 ALL(Open and Closed) or Open , Closet