SOLVED

Determining If a Record Is the Most Recent in the Sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2493351%22%20slang%3D%22en-US%22%3EDetermining%20If%20a%20Record%20Is%20the%20Most%20Recent%20in%20the%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2493351%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20to%20try%20and%20find%20a%20function%20that%20will%20help%20me%20determine%20if%20a%20specific%20record%20is%20the%20most%20recent%20within%20the%20sheet.%20I%20have%20a%20sheet%20that%20holds%20a%20large%20amount%20of%20historical%20data%2C%20and%20at%20a%20given%20snapshot%20in%20time%2C%20there%20could%20be%20multiple%20versions%20of%20the%20same%20record%20listed.%20A%20field%20'systemmodstamp'%20lets%20me%20know%20when%20the%20last%20change%20was%20made.%20The%20problem%20is%20that%20these%20records%20are%20randomly%20placed%20within%20the%20sheet.%20I%20need%20a%20way%20to%20see%20if%20the%20opportunityid%20in%20that%20cell%20has%20a%20duplicate%20or%20repreated%20entry%2C%20and%20if%20the%20system%20mod%20stamp%20is%20the%20greatest%20of%20its%20duplicates.%20I'll%20include%20a%20screenshot%20of%20some%20of%20the%20data%20and%20what%20I%20am%20looking%20for%20from%20the%20outcome.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22arhomberg_0-1624898004449.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291950i6AFD82D77BB170C2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22arhomberg_0-1624898004449.png%22%20alt%3D%22arhomberg_0-1624898004449.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22arhomberg_1-1624898038213.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291951i2C7EBF3F1F2F2B6A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22arhomberg_1-1624898038213.png%22%20alt%3D%22arhomberg_1-1624898038213.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20advice%20would%20be%20greatly%20appreciated%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2493351%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2493619%22%20slang%3D%22en-US%22%3ERe%3A%20Determining%20If%20a%20Record%20Is%20the%20Most%20Recent%20in%20the%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2493619%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1090090%22%20target%3D%22_blank%22%3E%40arhomberg%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Demo.png%22%20style%3D%22width%3A%20302px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291970i83F43742731A85B6%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Demo.png%22%20alt%3D%22Demo.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIf%20you%20run%20Excel%202019%2F365%2C%20in%20%3CSTRONG%3EC2%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(B2%20%3D%20MAXIFS(B%242%3AB%2412%2CA%242%3AA%2412%2CA2)%2C%20TRUE%2C%20FALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EOtherwise%2C%20in%26nbsp%3B%3CSTRONG%3ED2%3C%2FSTRONG%3E%20and%20%3CU%3Evalidate%20with%20Ctrl%2BShift%2BEnter%3C%2FU%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(B2%20%3D%20MAX((A%242%3AA%2412%3DA2)*B%242%3AB%2412)%2C%20TRUE%2C%20FALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ECorresponding%20sample%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2493712%22%20slang%3D%22en-US%22%3ERe%3A%20Determining%20If%20a%20Record%20Is%20the%20Most%20Recent%20in%20the%20Sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2493712%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3BWonderful%2C%20thank%20you%20so%20much%20for%20the%20assistance!%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

 

arhomberg_0-1624898004449.pngarhomberg_1-1624898038213.png

 

Any advice would be greatly appreciated,

 

Thanks

3 Replies
best response confirmed by arhomberg (New Contributor)
Solution

@arhomberg 

 

Demo.png

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...