Create table that returns recent date for multiple rows of matching data; except dat

[Penni]

I am looking for a data set that shows multiple species across all sites but with only the record for each species on a site with the most recent survey year. My data set has "Sites" for which there are multiple entries (Survey Date) for multiple plant species (Species), in some cases. What I want to do is return a dataset that has Site with all Species for Site but only the most recent Survey Year.

I've attached a small raw data set (I have 1,000s of records) along with a mini example of what I want to see in results.

[Bob Phillips]

This code should do it for you

Public Sub LatestData()
Dim wsthis As Worksheet
Dim wsres As Worksheet
Dim lastrow As Long
Dim nextrow As Long
Dim i As Long
    
    Application.ScreenUpdating = False
    Set wsthis = ActiveSheet
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Worksheets("Results").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Set wsres = Worksheets.Add(after:=wsthis)
    ActiveSheet.Name = "Results"
    
    With wsthis
        
        .Rows(1).Copy wsres.Range("A1")
        nextrow = 1
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To lastrow
        
            If .Cells(i, "E").Value <> .Cells(i - 1, "E").Value Then
            
                nextrow = nextrow + 1
                .Rows(i).Copy wsres.Cells(nextrow, "A")
            ElseIf .Cells(i, "F").Value > wsres.Cells(nextrow, "F").Value Then
            
                .Cells(i, "F").Resize(, 2).Copy wsres.Cells(nextrow, "F")
            End If
        Next i
    End With
    
    Application.ScreenUpdating = True
End Sub

[Penni]

The code works. Thank you. Two more questions:

1. Would the code change depending on how many rows there are?
2. What if two more columns of data were added after the last field of the original dataset? I've tried running the code as is but when I add two more fields, the code doesn't work.

[Penni]

The code only works for the original data set I sent. I tried using the code for both a smaller and larger data set and it only returns the results for the year 2020 stripping out any Site/Species that was last surveyed in a prior year (2019, 2018). See Results tab on attached data set run using using same code.

What changes would I have to make to run the code on a larger or smaller number of rows?

[Bob Phillips]

Sorry, I don't see the problem. Every one of those species was last surveyed in 2020, so the results are as I would expect.

BTW, the code should work regardless of sample size.

[p45cal]

I had a go with Power Query and got 19 results instead of 13 because there are quite a lot of Orange hawkweed (HIER AUR)/Site combinations.
I'm not sure which results the OP wants.
Power Query results are in sheet Resultsdd in the attached alongside macro results. Right-click a cell, say J2, and choose Refresh to update the query.