Return the most recent result that is not blank
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.
Answers
-
How exactly is the source sheet being populated?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
And how do you determine which one is the most recent?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
Where is the date?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
The date is a Created Date column in the target sheet.
-
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))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!