how to identify row based on max date and alphanumeri field

[Legacy 109559]

Hi,
I have the following data set where I have three columns: Name, Date and ID.
I am trying to identify the latest date against each name and in some cases where the dates are duplicated (as in case of John, 15/05/15) I want to be able to identify the name against latest date and highest ID value (looking at the numeric part of ID only).

so for John, I want to be able to identify with the help of formula in fourth column that this is the "newest" row to use. This way I can just filter the fourth column and get a unique row for each name with the latest date and highest ID.


I tried using this formula =IF(MAX(IF(A2=$A$2:$AB$11, $B$2:$B$11))=P2, "Newest", "") which works great if only max date was involved but I cannot figure out how to incorporate MAX ID in this formula as well to return "newest" against unique name


Name Date ID
John 15/05/2015 REF-137630
John 15/05/2015 REF-135755
John 04/05/2015 REF-135617
Alan 20/05/2015 REF-138261
Alan 19/05/2015 REF-138242
Alan 18/05/2015 REF-137820
Alan 07/05/2015 REF-136218
Adam17/08/2015 REF-153088
Adam23/06/2015 REF-144667
Adam13/05/2015 REF-137107

Thanks for your help.


KR

 

[Eric W]

Do the reference numbers always begin with "REF-"? Are they always assigned in order, i.e. a reference number on 15/05/2015 will always be greater than a reference number assigned on 14/05/2015?

 

[Legacy 109559]

Hi Eric,
Yes, the id will always begin with REF- and have the same number of characters.
Correct on second part as well, a reference number assigned to the latest date will be greater than one assigned to date before.
Thanks
KR

 

[Mackers]

Hi

Give this a whirl using control+shift+enter - it basically says "if the date is the max for this person and the ref number is the max for this person, write newest, otherwise don't":

=IF(B2&RIGHT(C2,6)=MAX(IF($A$2:$A$11=A2,$B$2:$B$11))&MAX(IF($A$2:$A$11=A2,--RIGHT($C$2:$C$11,6))),"Newest","")

This works on two conditions:

1. Your REF column has a 6 digit number which forms the last 6 characters of the cell
2. Your REF number (such as 135617) is unique and is always higher on more recent dates

I can clean the formula up if these aren't the case.

As a general rule, storing a number within a text string causes problems if you then want to perform mathematical operators on that number (like find the maximum value, sum or multiply). A better solution would be to store the number itself (such as 137630) in the column and then have the REF- concatenated separately or something.

Hope that helps

Mackers

Edit: just so you know, when you perform operations that cut cells up (like taking the LEFT() or RIGHT()most characters in a cell) Excel will return you a text string rather than a number. As such, if you want to check that the number is the largest, you will need to coerce the text string back into a number using VALUE() or 0+ or -- (I use --RIGHT() in the above example). This means you can then use MAX() on the data.

 

[Eric W]

Here's a version that should work.

Excel 2010

	A	B	C	D
John	REF-137630	Newest
John	REF-135755
John	REF-135617
Alan	REF-138261	Newest
Alan	REF-138242
Alan	REF-137820
Alan	REF-136218
Adam	REF-153088	Newest
Adam	REF-144667
Adam	REF-137107

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]15/5/2015[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]15/5/2015[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4/5/2015[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]20/5/2015[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]19/5/2015[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]18/5/2015[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]7/5/2015[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]17/8/2015[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]23/6/2015[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]13/5/2015[/TD]

</tbody>

Sheet15​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]{=IFERROR(IF(B1*(MID(C1,5,9)+0)=MAX(IF(A1=$A$1:$A$10,$B$1:$B$10*(MID($C$1:$C$10,5,9)+0),0)),"Newest",""),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I see Mackers added an option too. Let us know what you did.

 

[Legacy 109559]

A big thanks to both Eric and Mackers for this solution.
I have tried both formulas and they work like a charm.
I am going to go with Eric's recommendation only because its slightly faster.
once again , thanks for all your help.

KR

 

[Eric W]

I'm glad we could help.