VBA code for multiple data lookup

[geomano]

Hi, I have a spreadsheet with values in column L. I would like macro to match date from column L with column C, which contain dates. For each value, which exists in column L more than 1, I'd love macro to compare the dates and remove entire rows except the one with the latest date. Could you please help me with creating that sort of macro? Many thanks.

[mdmackillop]

Please post a workbook with sample data and desired result, and any further clarification.

[geomano]

Hi, I am attaching a sample workbook.

[mdmackillop]

I can't follow your requirements. Please clarify on your attachment.

[p45cal]

You may not need your calculated columns to the right of column G to do this - except for one:
In cell H2 have the formula
=YEAR(C2)
and copy to the bottom.
Give that column a header, say 'Year'.
Now sort columns A:H only on C (History Timestamp), newest to oldest.
Select the whole range A1:H13776 and on the Data tab click on Remove Duplicates. In the dialogue box, tick the My Data has headers checkbox, then untick all the columns without headers (B, E & F) and untick the History Timestamp column.
Now click OK.

Because duplicates are removed from the bottom up, the latest dates are the kept ones.
Now you can re-sort your data as you wish.

Code which does this? (works on the active sheet):

Sub Macro9()
Columns("H:L").Delete 'optional
Set myRng = Range("A1").CurrentRegion.Resize(, 8)

myRng.Columns(8).FormulaR1C1 = "=YEAR(RC[-5])"
Range("H1").Value = "Year"
With ActiveSheet.Sort
  .SortFields.Clear
  .SortFields.Add Key:=Range("C2"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
  .SetRange myRng
  .Header = xlYes
  .MatchCase = False
  .Orientation = xlTopToBottom
  .SortMethod = xlPinYin
  .Apply
End With
myRng.RemoveDuplicates Columns:=Array(1, 4, 7, 8), Header:=xlYes
Range("A1").Select
End Sub

[geomano]

Hi p45cal, that's really simple, yet brilliant idea! Many thanks for your help!