How to find newest date

Hi all, this is probably an old question asked again, but I am trying to find the newest of entries on a sheet and return the value to another sheet. Here are the details:
- PrjectSheet contains the list of the projects;
- NotesSheets contains multiple note entries per project;
- None of the columns are intentionally sorted in any way.
I need to find the newest date note per each project and enter it under the "Latest Note" and "Note Date" columns on ProjectSheet. Here is a picture of it:
Any help would be greatly appreciated. Thanks in advance.
Best Answer
-
@NAMELESS anytime, glad it worked out
Principal Consultant | System Integrations
Prime Consulting Group
Email: info@primeconsulting.com
Follow us on LinkedIn!
Answers
-
@NAMELESS you want to do a collect() of the items in your note sheet filtered by name then you can do MAX on that collection to get the max date (most recent). You can then return those values or index to reference whatever you need.
collect just has to go into something else, even if it is a join(collect(),"") where it joins to nothing but the 1 answer.Principal Consultant | System Integrations
Prime Consulting Group
Email: info@primeconsulting.com
Follow us on LinkedIn! -
@prime_nathaniel, thank you for your useful hint and insight.
Based on the information you provided, I came up with the two formulas in the following that I am referencing for anyone else who might be interested in the solution:On the "ProjectSheet" under the "Note Date" column, I have:
=MAX(COLLECT({NotesSheet Date}, {NotesSheet Primary Column}, Name@row))On the same sheet under the "Latest note" column, I have:
=JOIN(COLLECT({NotesSheet Note Column}, {NotesSheet Primary Column}, Name@row, {NotesSheet Date}, [Note Date]@row), "")
The two formulas did the trick.
Thanks again.
-
@NAMELESS anytime, glad it worked out
Principal Consultant | System Integrations
Prime Consulting Group
Email: info@primeconsulting.com
Follow us on LinkedIn!
Help Article Resources
Categories
Check out the Formula Handbook template!