Jun 28 2021 09:35 AM
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,
Thanks
Jun 28 2021 10:29 AM
Solution
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
Jun 28 2021 11:01 AM
@L z. Wonderful, thank you so much for the assistance!
Jun 28 2021 11:27 AM
Jun 28 2021 10:29 AM
Solution
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