Return data for latest instance of unique records

sstubbs

I have a few tables which are populated by form submissions where users are presented with list of vehicles, key rings, etc... These unique assets repeat in the same table over and over each time a new row is inserted in the same table.

For example, vehicle number 104 his submitted each month. There are a total of 96 vehicles.

Everyone month I'm asked which vehicles haven't submitted their vehicle inspection. Today I'm exporting to excel and manually comparing to the full list. There has to be a way to accomplish this in Smartsheets, right? How would I do that?

Additionally, we have 168 numbered key rings that users can select in a form with the actions Out and Return.

The KeyNumber field allows multi select. I need to figure out a way to return a list of all keys that are "Out", but only where the latest Created date is after the latest "Return" Created date.

I guess I'm imaging these two solutions will involve creating two separate tables named "Key Summary" and "Vehicle Summary", with a list of each unique record and a formula that returns the latest date from Created, and respectively the latest action (Out or Return) and odometer value for the latest row.

Would someone give me some guidance and examples of the formulas to accomplish this?

Thank you in advance for any guidance.

KPH

@sstubbs

Question 1 - When was last submission for each vehicle

The MAX COLLECT function will help you. You can set up a sheet with a column for vehicle ID (in the example I call this ID to look for) and a column containing a formula that will collect the Created date for each row where the vehicle matches the one on the sheet, and then return the latest. This is the formula if it was in the same sheet as the data.

=MAX(COLLECT(Created:Created, [Vehicle]:[Vehicle], [ID to look for]@row))

To put this on its own sheet you would need to replace the parts in bold with cross sheet references.

=MAX(COLLECT(Created:Created, Vehicle:Vehicle, [ID to look for]@row))

Let me know if you need help with setting those up.

Bonus points - Was the last submission in the last week

To reduce the effort further you could use an IF to evaluate the answer to the above formula to see if it was within the last week. If the date returned is after today minus 7 days ago the formula will return "within 7 days". If not it returns "more than 7 days ago".

=IF(formula above goes here > TODAY(-7), "within 7 days", "more than 7 days ago")

Here it is pasted in:

=IF(MAX(COLLECT(Created:Created, [Vehicle]:[Vehicle], [ID to look for]@row)) > TODAY(-7), "within 7 days", "more than 7 days ago")

You could wrap this in an IFERROR to remove the error message that will appear if there is no created date.

=IFERROR(IF(MAX(COLLECT(Created:Created, Vehicle:Vehicle, [ID to look for]@row)) > TODAY(-7), "within 7 days", "more than 7 days ago"), "no date")