Results 1 to 6 of 6

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

  1. #1
    Seeker Penni's Avatar
    Join Date
    Mar 2020
    Posts
    5
    Articles
    0
    Excel Version
    Office 365

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,846
    Articles
    0
    Excel Version
    O365
    This code should do it for you

    Code:
    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

  3. #3
    Seeker Penni's Avatar
    Join Date
    Mar 2020
    Posts
    5
    Articles
    0
    Excel Version
    Office 365
    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.

  4. #4
    Seeker Penni's Avatar
    Join Date
    Mar 2020
    Posts
    5
    Articles
    0
    Excel Version
    Office 365
    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?
    Attached Files Attached Files

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,846
    Articles
    0
    Excel Version
    O365
    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.

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,886
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •