Advanced Filtering by MAX value
Hi all
I've created a report and wanted to know if there is a way to show unique rows by the highest date value
For example - below I would like to see the bottom table as my report (colours are just to show the matching rows - they are not part of the conditions required) where only the unique rows are pulled through (unique value in Date of Check column)
or would there be a way I could create a helper column in the source grid which looks at multiple columns and identifies which one has the latest date of all the rows that match otherwise?
Thanks
Dan
Best Answer
-
Hi Daniel,
Helper columns are the solution. Add a text/number column [Join] with the formula:
=JOIN(name@row:location@row, ", ")
Add a checkbox column [report] with the formula:
=IF([all checks complete]@row="N", 0, IF(countifs(join:join, join@row)=1, 1, IF(countifs(join:join, join@row, [date of check]:[date of check], @cell>[date of check]@row)>0, 0, 1))
Filter your report to include [report]=1.
Work for you?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Hi Daniel,
Helper columns are the solution. Add a text/number column [Join] with the formula:
=JOIN(name@row:location@row, ", ")
Add a checkbox column [report] with the formula:
=IF([all checks complete]@row="N", 0, IF(countifs(join:join, join@row)=1, 1, IF(countifs(join:join, join@row, [date of check]:[date of check], @cell>[date of check]@row)>0, 0, 1))
Filter your report to include [report]=1.
Work for you?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Didn't we already figure this out the other day HERE? Or were you looking for something different?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!