How to get a row which has latest date in a column

[jarvishere]

I need to vlookup a set of information from one spreadsheet. Problem I am having is the table array which i am selecting has multiple records with same unique value I am using. I want to get the data with the latest datetimestamp. The dataset is not massively big but has close to 200K rows. Example below:

Master data with duplicate records

[TABLE="width: 325"]
<tbody>[TR]
[TD][TABLE="width: 325"]
<tbody>[TR]
[TD]unique_id[/TD]
[TD]name[/TD]
[TD]end_date[/TD]
[/TR]
[TR]
[TD]ABCDE[/TD]
[TD]John[/TD]
[TD="align: right"]31/08/2014 00:00[/TD]
[/TR]
[TR]
[TD]ABCDE[/TD]
[TD]John[/TD]
[TD="align: right"]31/08/2014 00:00[/TD]
[/TR]
[TR]
[TD]ABCDE[/TD]
[TD]John[/TD]
[TD="align: right"]31/08/2017 00:00[/TD]
[/TR]
[TR]
[TD]ABCDE[/TD]
[TD]John[/TD]
[TD="align: right"]31/08/2018 00:00[/TD]
[/TR]
[TR]
[TD]BCDE[/TD]
[TD]Kelly[/TD]
[TD="align: right"]01/09/2018 00:00[/TD]
[/TR]
[TR]
[TD]BCDE[/TD]
[TD]Kelly[/TD]
[TD="align: right"]01/01/2017 00:00[/TD]
[/TR]
[TR]
[TD]CEE[/TD]
[TD]Foo[/TD]
[TD="align: right"]31/08/2015 00:00[/TD]
[/TR]
[TR]
[TD]CEEFFF[/TD]
[TD]Bar[/TD]
[TD="align: right"]31/03/2019 00:00[/TD]
[/TR]
[TR]
[TD]CEEFFF[/TD]
[TD]Bar[/TD]
[TD="align: right"]31/08/2018 00:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]unique_id[/TD]
[TD]name[/TD]
[TD]end_date(MAX)[/TD]
[/TR]
[TR]
[TD]ABCDE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BCDE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CEE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CEEFFF[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

Formula for Name: =INDEX($B$2:$B$10,MATCH(1,($A$2:$A$10=A14)*($C$2:$C$10=C14),0))
Formula for Max Date: =MAX(IF($A$2:$A$10=A14,$C$2:$C$10))

Both formulas are array formulas so use Control+Shift+Enter.

 

[Aladin Akyurek]

B14, just enter and copy down:

=VLOOKUP(A14,$A$2:$B$10,2,0)

C14, assuming that dates are chronological order, just enter and copy down:

=LOOKUP(9.99999999999999E+307,1/($A$2:$A$10=$A14),$C$2:$C$10)

If not chronological...

=INDEX($C$2:$C$10,AGGREGATE(14,6,(ROW($A$2:$A$10)-ROW($A$2)+1)/($A$2:$A$10=$A14),1))