Need to identify last record with duplicate values

[Nathan Shiffman]

Hello,
I have a large resource tracking file which keeps a record of current and former resources on our team. each team member is identified by a unique member ID code.
When a member moves to a new location I will create a new record on a different excel line with the new location. See an abridged example in attached sheet.
Here is what I need to do.
In column B, I want to identify the last record for each member. For those, who don't have duplicate member IDs it is easy. That's the only record. So its the last record.
The problem comes when I come to members with duplicate IDs.
The formula has to identify just the last record for that member, based on the allocation end date.
I gave some examples in my file.
Any help here would be immensely appreciated.
Thanks,
Nathan

 

[eibi]

Nathan,

If an array formula is satisfactory (remember to confirm with Control+Shift+Enter):

=IF(F2=MAX((I2=$I$2:$I$27)*($F$2:$F$27)),"last record","")

See attached.

 

[Nathan Shiffman]

That's really great. Thanks.
Could you please explain the logic behind the formula for me?

 

[eibi]

Sure.

For a given row...we apply a True/False test: I2=I2:I27. It compares the ID in I2 with the IDs in the the whole column, and the resulting array is {TRUE;FALSE;TRUE;FALSE...}, returning True for each row that has a matching ID and False for all other rows.

This array of TRUE and FALSE values is equivalent to an array of 1s and 0s {1;0;1;0.....}

Then, we multiply this array {1;0;1;0....} by the dates in F2:F27. The result is an array of dates only for rows with a matching ID, like so: {May 15;0;June 2;0;0...} --I made these dates up; they don't necessarily correspond to your sample file...and the dates actually pass to the array as integers rather than Calendar dates... but the explanation is easier this way...

Then, we apply MAX(...) to the array of dates that had matching IDs {May 15;0;June 2;0;0...} to select the biggest (that is, most recent) date.

Finally, we apply an IF() that says: If F2 is equal to the largest-date-for-rows-that-match-the-ID-in-cell-I2, return "last record"...

You can actually watch this step-by-step process by selecting Cell I2 and then clicking on Formula Ribbon -> Evaluate Formula.

 

[Nathan Shiffman]

Eibi,
Thanks for the detailed explanation. I didn't even have time to look intently into your explanation to meet my report deadline and as I entered your formula into my live data I am getting an #value error.
It appears that the error is because there are some values in the 'allocation end date' in my production data that has a #N/A value. They are there by design, and ideally I would like to keep that value.(Just to test it out, I replaced the #N/A values to blank values, but the formula still didn't work)

How can I modify your formula to ignore #N/A values?
Thanks,
Nathan

 

[eibi]

In the formula I provided:

=IF(F2=MAX((I2=$I$2:$I$27)*($F$2:$F$27)),"last record","")

replace this portion ($F$2:$F$27) with this: IFERROR($F$2:$F$27,0)

New formula:

=IF(F2=MAX((I2=$I$2:$I$27)*IFERROR($F$2:$F$27,0)),"last record","")

See attached.

 

[Nathan Shiffman]

Eibi,
I modified my formula to match yours but am still getting the same error.
This is the formula I am using:
=IF(K8=MAX(($O$8:$O$1574)*IFERROR($K$8:$K$1574,0)),"Last Record","")
When I trace the error on the formula it seems to still be pointing to the #N/A value.

Yet, when I modified it in my test data it seems to work though. See attached. (BTW, I opened your file but didn't see your modification).

Any ideas?
Thanks very much,
Nathan

 

[eibi]

The formula solution that I provided is specifically for an #N/A error. But you are getting a #VALUE error?

A #VALUE error suggests to me that one (or more) of your dates is keyed incorrectly and is being interpreted as a text value rather than a date. The solution for this is scrubbing your data to identify and fix data entry errors.

You can do this most easily by sorting all the dates from oldest to newest; and erroneous dates will filter to the bottom of the list...But it may be difficult to sort back into the original order, so save a backup file and sort with caution...

 

[Nathan Shiffman]

Eibi,
As it turns out, I didn't transcribe the formula correctly on my production data, missing a crucial step (highlighted in red below), which caused the error.
=IF(K8=MAX((O8=$O$8:$O$1574)*IFERROR($K$8:$K$1574,0)),"Last Record","").
The formula works perfectly on my prod data and I am very grateful for your help and patience.
Thanks again,
Nathan