How to find newest date

NAMELESS

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.

prime_nathaniel

@NAMELESS anytime, glad it worked out

prime_nathaniel

@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.

NAMELESS

@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.

prime_nathaniel

@NAMELESS anytime, glad it worked out