Return value based on a MAX date and other criteria

Daniel Barrett

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

Paul Newcome

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.

Paul Newcome

Did you manually highlight those rows or are they highlighted through some kind of conditional formatting?

Daniel Barrett

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

Paul Newcome

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.

Daniel Barrett

Hi @Paul Newcome

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

Daniel Barrett

@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

Paul Newcome

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.