Looking to identify most recent data

[mzani41]

Hello,

I have a spreadsheet that I am looking to add an additional column to identify the most recent data by sorting.

Right now I have 2 columns. Column A has data that can repeat and column B is a date. I am looking for a formula in column C that will add a "1" to the most recent date where column A has the same value.

I provided a sample below of what I am looking to accomplish.

Column A Column B Column C
0000000015 12/15/2014
0000000015 1/6/2015
0000000015 4/23/2015 1
0000000024 10/30/2014
0000000024 12/22/2014
0000000024 5/8/2015 1
0000000039 11/10/2014
0000000039 11/17/2014
0000000039 12/17/2014
0000000039 7/1/2015 1
0000000048 10/31/2014 1
0000000054 6/20/2015
0000000054 7/7/2015
0000000054 8/27/2015 1

If any has any idea of what this IF statement would look like please let me know. Thanks.

Mike

[JieJenn]

Give this array formula a try. Enter the formula with Ctrl + Shift + Enter keys together
=MAX(IF($A$2:$A$10=A2,$B$2:$B$10,""))=B2

[José Augusto]

Or try this regular formula in C1 and copy down

Please Login or Register  to view this content.

[snick45]

Hey Mike, what Excel are you using? If it's 2016, then you can use MAXIFS formula, which is really helpful.

[newdoverman]

Welcome to the forum.

This might work for you. Enter this in C2 with Ctrl + Shift + Enter (array formula) and fill down.

Please Login or Register  to view this content.

[newdoverman]

The AGGREGATE function as given in msg#3 will return TRUE or FALSE. If you need 1s and blanks and want to use the AGGREGATE function, surround the formula with an IF statement like this and enter normally.

Please Login or Register  to view this content.

[mzani41]

This worked. Thank you.

[newdoverman]

Thank you for giving feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.