Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    Harrow, UK
    Posts
    53

    Unanswered: How can I find most recent data?

    Say I have a sheet containing three column (A=Name, B=Value, C=Date).

    A contains a list of item names
    B contains a list of values
    C is the date that that row of data is received.

    It could look like :

    ----A----.----B----.----C----
    James Volvo 05/05/03
    Robert Ford 05/05/03
    James Audi 06/06/03
    Robert BMW 06/06/03

    What I want to do is delete the older rows if there is an updated row of data. i.e. on 05/05 I received James-Volvo, but later on 06/06 received James-Audi ad therefore James-Audi is the most recent data - so I want to delete James-Volvo. Similarly I want to delte Robert-Ford for the same reason (Robert-BMW is more recent information).

    This is a very over-simplified versino of a very large and complex sheet that I have, but it illustrats the point.
    Thank you,
    James

  2. #2
    Join Date
    Jun 2004
    Posts
    27
    Quote Originally Posted by jamesmc2
    Say I have a sheet containing three column (A=Name, B=Value, C=Date).

    A contains a list of item names
    B contains a list of values
    C is the date that that row of data is received.

    It could look like :

    ----A----.----B----.----C----
    James Volvo 05/05/03
    Robert Ford 05/05/03
    James Audi 06/06/03
    Robert BMW 06/06/03

    What I want to do is delete the older rows if there is an updated row of data. i.e. on 05/05 I received James-Volvo, but later on 06/06 received James-Audi ad therefore James-Audi is the most recent data - so I want to delete James-Volvo. Similarly I want to delte Robert-Ford for the same reason (Robert-BMW is more recent information).

    This is a very over-simplified versino of a very large and complex sheet that I have, but it illustrats the point.
    First Sort You Data By Date In Descending Order, Then Delete The Oldest Record. Hope This Answers Your Question

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Here is a bit of code to do what i think you are after

    Code:
    Sub Test()
        Dim LastRow As Long, I As Long, J As Long
        Dim EmployeeName As String, SellDate As Date
        Dim rownum As Long
        Dim FoundRange As Range
        
        
        LastRow = Range("A" & Rows.Count).End(xlUp).Row
        
        For I = 2 To LastRow - 1
            'get employee name from column A and Sell date from Column C
            EmployeeName = Range("A" & I).Value
            SellDate = Range("C" & I).Value
            For J = 1 To LastRow - 1
                'find an instance of Employee name
                Set FoundRange = Range("A" & I + 1 & ":A" & LastRow).Find(EmployeeName)
                If Not FoundRange Is Nothing Then
                    'if found r date is less than initial date delete the 2nd record
                    If FoundRange.Offset(0, 2).Value < SellDate Then
                        FoundRange.EntireRow.Delete
                        LastRow = LastRow - 1
                    ElseIf FoundRange.Offset(0, 2).Value = SellDate Then
                        'do something else no indicator
                    Else
                        'otherwise delete the inital range and reset sell date
                        SellDate = FoundRange.Offset(0, 2).Value
                        Range("A" & I).EntireRow.Delete
                        FoundRange.EntireRow.Cut
                        Range("A" & I).Insert
                        LastRow = LastRow - 1
                    End If
                Else
                    Exit For
                End If
            Next J
        Next I
            
    End Sub
    HTH
    David

Posting Permissions

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