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
Check out the Formula Handbook template!