Use Max Collect to find most recent record with value in multiselect columns

jthompson

I have a sheet with 2 multi-select columns. The value options for the 2 columns are the same, and are listed in a 2nd sheet. In the second sheet, I'd like to write a formula that finds the most recent date where the value is present in one of the two multiselect columns. Once I have that data, I'd like to know which column it came from. If the requirement doesn't exist in either column, I'd like to return "Not Complete"

Since a picture may be easier to understand, here is some dummy data from my two multi-select columns in my primary sheet

And my 2nd sheet (ignore column 6, this formula uses IF(CONTAINS( but will not work because it doesn't account for the fact a requirement may be listed as having issues later in the sheet and the most recent result is the one I'm interested in):

My initial thought was to find the max date where the value is in the "requirements with issues" column, then a separate formula for max date where the value is in the "requirements with no issues". Then compare the dates in a 3rd column to determine which is max between the 2 to be able to assign a value of "Passed" or "Not Complete"

My Most recent fail formula that isn't working:

=MAX(COLLECT({TestDate}, {Reqs with issues}, HAS({Reqs with issues},[Column5]@Row)))

where {TestDate} is another date column in my primary sheet

Is there a better approach for me to determine the most recent categorization for a specific requirement?

jthompson

Also curious if max row index would work better (does such a formula exist?) in case the same requirement is listed in two separate rows with the same date?

HeatherD.

Hi @jthompson !

You're really close with the MAX/COLLECT formula you created! Try using @cell within the HAS function:

=MAX(COLLECT({TestDate}, {Reqs with issues}, HAS(@cell, [Column5]@row)))

That should resolve the issue with your formula. You can then reuse the formula and replace {Reqs with issues} with a range referencing your Requirements Test with No Issues column to pull the latest date for the most recent pass date. Of course, you'll want to be sure you delete the {Reqs with issues} range name, including curly brackets, and click "Reference another sheet" instead of clicking "Edit reference" to avoid accidentally editing the existing range.

Hope this helps!

Best,

Heather