Filter rows in sheet based on last date entry

Hey Guys,

Need some help. User will record their availability for an event, this all gets recorded in a sheet, including the current date and time. however some users might change their mind on the same day for the same event, resulting in multiple records form that user for the same event. I need to filter the data, to only show the last event from that user for the specific event and his/her associated availability. I tried SORTN with SORT and some other but does not have the desired result. Below pic marked RED shows the 3 records from the same user which needs to be filtered into only the latest record.

Hi, I have not tested, but maybe could you deal with it directly into Glide:

  1. Create a ‘relation’ column to ‘User’ (ie. a relation to self)
  2. Roll-up on ‘date’, select “latest”
1 Like

Just to add to @AyS_0908’s response. Also add an IF/THEN column that will compare the date in the rollup to the date in the row. If it matches return ‘true’. Then you can filter on only true values in the if/then column

1 Like

Thank you @AyS_0908 and @Jeff_Hager.
I tried your recommendation. The Roll-up on ‘date’ returns the ‘latest’ date in the column, however the ‘latest’ date for each user per event is required. The user can report his availability for all the Events on the same day, therefore the requirement to find the latest record per user per event.

Then just create a template of user and event joined together. Create the self relation using the template column. Same procedure, but just using the template column instead of just the user.

1 Like

Thank you @Jeff_Hager
Perhaps I am missing something:
Template of USER and EVENT results in “test3@test.com RC Th”
Relation of Template results in “test3@test.com

The Rollup and IF/THEN column have the same result

A relation is only gonna show the first column of the row it finds. It’s still returning the entire row, so you can ignore what you see in the relation column. As for the rollup, make sure you are using the relation and not the sheet.

BOOM!
Thank you @Jeff_Hager I did not do the Rollup on the relation. Appreciate the help!

1 Like