Identify the most recent entry for job stages

Leslie Merlino

I have a column with Job Number, another for Status and another for Last Updated Date. Each time an entry is made for a row on the job, the row is copied into a history sheet to store the data for each project.

Inside the history sheet, I need to identify the most recent entry for each stage of each job as some of the rows in the history sheet don't contain the most recent data but my client wants to be able to review all changes made.

What I am looking for is a way to count the hours and units columns for the rows that are most recent for each stage broken down by job.

Genevieve P.

Hi @Leslie Merlino

It sounds like you will need to have a helper column with a formula to indicate the most recent rows. Then you can use this helper column in any of the formulas you want afterwards, to create SUMs or COUNTs based on if this helper column is checked or not.

You can use a MAX function to look through a Date Created System column and find the MAX date (most recent date) that matches your different criteria, using COLLECT to identify your criteria.

I would them embed this formula into an IF statement in a Helper Column that says something like: If this current row is the Most Recent row for all these criteria, then check the box.

For example, here's the MAX(COLLECT part of the formula:

MAX(COLLECT([Date Range]:[Date Range], [Job Number]:[Job Number], [Job Number]@row))

^This would return the most recent date from the Date Range column, for the specific Job Number in this current row.

To add different criteria in, you just need to add the range like this [Column Name]:[Column Name], then the criteria after a comma by selecting that cell and using @row.

MAX(COLLECT([Date Range]:[Date Range], [Column 1]:[Column 1], [Column 1]@row, [Column 2]:[Column 2], [Column 2]@row))

Here it is in the IF statement.

Full Formula Example:

=IF([Date Range]@row = MAX(COLLECT([Date Range]:[Date Range], [Job Number]:[Job Number], [Job Number]@row)), 1, 0)

Is this what you were looking to do? Then you can use this helper column in a number of ways. You can either create a Report filtering only down to the Checked rows, or a Filter, or you can build other formulas that reference the checked box.

Ex:

SUMS the Hours for the most recent rows (using the Helper Column formula above) of a specific Job:

=SUMIFS([Hours Column]:[Hours Column], [Helper Column]:[Helper Column], 1, [Job Name]:[Job Name], "Specific Job")

Let me know if this makes sense or if you need more help! It would be helpful to see a screen capture of your archive sheet with column names if possible, but please block out any sensitive data.

Cheers,

Genevieve