Return value based on a MAX date and other criteria
Me again
Is it possible to create a summary report that shows the individual rows of data based on the latest date (Date of Completion) and other columns so as to only show 1 row per location (multiple locations can occur in a single building) - See example image below
The items in green have the latest dates against their respective locations and would pull through from the Source Data (top) to the Summary Report (bottom)
Any ideas?
Thanks
Dan
Best Answer
-
You can filter on multiple columns (up to 3). If you wanted a report, you could inset a checkbox column and use something along the lines of...
=IF([Date of Checks]@row = MAX(COLLECT([Date of Checks]:[Date of Checks], Building:Building, Building@row, Location:Location, Location@row)), 1)
This will check the box on every row for the most recent update for that particular building/location combo.
Answers
-
Did you manually highlight those rows or are they highlighted through some kind of conditional formatting?
-
Hi Paul
The colours are just to show which ones should carry through, so yes manually :)
I'm going to test if it's possible to sort by multiple columns i.e. primary sort = Date and secondary sort = Location and see if it gives me what I need
If however, my original method is possible I would love to try that as well to see which provides the best solution
Thanks
Dan
-
You can filter on multiple columns (up to 3). If you wanted a report, you could inset a checkbox column and use something along the lines of...
=IF([Date of Checks]@row = MAX(COLLECT([Date of Checks]:[Date of Checks], Building:Building, Building@row, Location:Location, Location@row)), 1)
This will check the box on every row for the most recent update for that particular building/location combo.
-
I tried that but I only get the UNPARSEABLE error message
Is there perhaps a statement missing for the IF or MAX values?
Thanks
Dan
-
@Paul Newcome - I think it got it working - missed a bracket!
I'm guessing the COLLECT function only works with 2 critieria?
Thanks so much again
Dan
-
Happy to help. 👍️
The COLLECT function can work with one range/criteria set, but it does require at least two ranges. The first range is the data you want to collect to be evaluated by the main function (in this case the MAX function). After that it is range/criteria set(s) to determine which rows from the first range should be pulled.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!