Delete Duplicate entry and retain the one with the latest date

[Kittinliscious]

Hello,

What could be the possible formula that would delete the duplicate addresses but retain the latest one?
For this example, I need to keep the one with 09/12/2010 date.

Address City State Zip Date
13th Floor ATLANTA GA 30303 31/12/1979
13th Floor ATLANTA GA 30303 31/03/1981
13th Floor ATLANTA GA 30303 30/03/2000
13th Floor ATLANTA GA 30303 20/02/2004
13th Floor ATLANTA GA 30303 09/12/2010

Many thanks in advance,

[Kittinliscious]

Attached a sample workbook.

Thanks again

[JeteMc]

I have a feeling that there is more to this, however to simply retain the record having the most recent date:
1. Find the most recent date using (cell L2): =AGGREGATE(14,6,E$2:E$6,1)
2. Display the corresponding records (Cells H2:K2): =INDEX(A$2:A$6,MATCH($L2,$E$2:$E$6,0))
Let us know if you have any questions.

[Kittinliscious]

Hi JeteMc,

I should have put more sample data on my worksheet. Sorry my fault.
I tried working on the sample formula you shared but I still couldn't make it work.
So here I attached a workable sample.

Thanks in advance,

[GeoffW283]

Here is a possible solution. It does need a helper column though.

In G2 (the helper column):

Please Login or Register  to view this content.

This is an array formula and must be committed via CTRL-SHIFT-Enter. Copy down col-F as far as is needed. The formula extracts distinct values for each of the address fields in the form of a row index and in particular where there are duplicates it chooses the most recent entry (I assume the input list is in chronological order).

In I2 copied across and down to M10:

Please Login or Register  to view this content.

This formula indexes into the address columns using the helper column index.

I added a couple more test cases to your data. See the attached workbook.

I'm sure there's a no-helper-column solution out there but at least let us know whether this is the result you are looking for.