Determining If a Record Is the Most Recent in the Sheet

New Contributor

Hello Everyone,


I am working to try and find a function that will help me determine if a specific record is the most recent within the sheet. I have a sheet that holds a large amount of historical data, and at a given snapshot in time, there could be multiple versions of the same record listed. A field 'systemmodstamp' lets me know when the last change was made. The problem is that these records are randomly placed within the sheet. I need a way to see if the opportunityid in that cell has a duplicate or repreated entry, and if the system mod stamp is the greatest of its duplicates. I'll include a screenshot of some of the data and what I am looking for from the outcome.




Any advice would be greatly appreciated,



3 Replies
best response confirmed by arhomberg (New Contributor)




If you run Excel 2019/365, in C2:

=IF(B2 = MAXIFS(B$2:B$12,A$2:A$12,A2), TRUE, FALSE)

Otherwise, in D2 and validate with Ctrl+Shift+Enter:

=IF(B2 = MAX((A$2:A$12=A2)*B$2:B$12), TRUE, FALSE)

Corresponding sample attached

@L z. Wonderful, thank you so much for the assistance!

You're welcome. Somewhere on each reply there's an option to mark as best response => Can help others...