Find data with latest date

**teco** · 01-05-2017, 09:23 AM

Dear All

Kindly i need your support. I have a list of sites with dates. Some sites are repeated with different dates, i want to deduct sites with their latest dates if they are repeated. Thanks.

Input:

Date Site ID
10-Nov-15 100
10-Nov-15 100
10-Nov-15 100
20-Nov-16 100
20-Nov-16 100
20-Nov-16 100
25-Nov-16 105
25-Nov-16 105
25-Nov-16 105
6-May-16 375
6-May-16 375
6-May-16 375
2-Feb-15 490
2-Feb-15 490
2-Feb-15 490
2-Jan-17 105
2-Jan-17 105
2-Jan-17 105

Output

Date Site ID
20-Nov-16 100
20-Nov-16 100
20-Nov-16 100
6-May-16 375
6-May-16 375
6-May-16 375
2-Feb-15 490
2-Feb-15 490
2-Feb-15 490
2-Jan-17 105
2-Jan-17 105
2-Jan-17 105

**Richard Buttrey** · 01-05-2017, 09:33 AM

Hi,
It's not clear, at least to me what the rule is that says drop 10 Nov 2015 & 25 Nov 2016 but leave the others.

Please clarify

**Kaper** · 01-05-2017, 09:34 AM

Sort your data Descending on dates then use standard Remove Duplicates command (marking only Site ID column duplicate). Then sort back on dates but Ascending.

**Kaper** · 01-05-2017, 09:43 AM

The result as I proposed above would be:
Date Site ID
2015-02-02 490
2016-05-06 375
2016-11-20 100
2017-01-02 105

Wouldn't it be usefull for you?

**Kaper** · 01-05-2017, 09:55 AM

Or may be in C2 (and copy down) write array formula *

Formula:

Please Login or Register  to view this content.

and then filter data on column C to delete all rows marked for removal

*) ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

**teco** · 01-05-2017, 03:41 PM

Thanks Richard for taking interest. Actually there is data associated with these sites and i want to know the latest dates. Any how the problem is solved now. Thanks