Return the most recent result that is not blank

Jacob Minnich

I have a collection of Smartsheets that I use to track lab testing for each lot# of product. I'm trying to use the following formula to pull the latest result from sheet "ResultImport".

=INDEX(COLLECT({ResultImport pH}, {ResultImport ProductLot}, ProductLot@row, {ResultImport CreatedDate}, MAX(COLLECT({ResultImport CreatedDate}, {ResultImport ProductLot}, ProductLot@row))), 1)

It works, but it returns a blank if two rows of results have the same day/time and Product-Lot, but the first one contains a blank (due to formatting / the way the results are delivered from the lab).

What I want is the latest result that is not blank. Any ideas? I thought about an IF(ISBLANK()) but I think it would need an increment, so I can't wrap my head around that. Maybe I'm making it too complicated.

Paul Newcome

How exactly is the source sheet being populated?

Jacob Minnich

The source sheet is being populated by importing an excel document from the lab into Smartsheet, then moving the rows to the existing sheet. Snip below of what the excel doc looks like. The columns included in the file are dynamic based on what results come off the previous day.

Paul Newcome

And how do you determine which one is the most recent?

Jacob Minnich

Results come in over the course of several days. I'm looking for retest results, which would have a later date, but if there's other testing for the same product- lot I want to filter out blanks.

So for each test there could be multiple results with different dates but I'm only interested in the results for that column that aren't blank and I want to choose the most recent.

Paul Newcome

Where is the date?

Jacob Minnich

The date is a Created Date column in the target sheet.

Paul Newcome

Basically what you want to do is COLLECT all of the non-blank entries for that Product Lot and then pull the last one. To determine the array number for the INDEX function specifying the last one, all you need to do is us a COUNTIFS to count how many are not blank for that Product Lot.

=INDEX(COLLECT({Column To Pull}, {Column To Pull}, @cell <> "", {Product Lot}, @cell = ProductLot@row), COUNTIFS({Column To Pull}, @cell <> "", {Product Lot}, @cell = ProductLot@row))

Jacob Minnich

Thank you! I had to wrap it in an IF statement so that it wouldn't return an error when it had a count of zero, but this did exactly what I needed!

Paul Newcome

Happy to help. 👍️