Find earliest data by date

[osgood82]

Hi all,

I wonder if anyone could help me with a work related excel query I have. I have two columns of data and need to find the earliest plan date and keep only that (deleting any duplicate named plans with later dates)

Please see the below example of columns I have, the result I need is for the formula to pick out the following results (Plan A 01/04/2008; Plan B 01/09/2007; Plan C 01/08/2008) Basically I need it only to bring back the data highlighted in red below (please excuse the full stops used to show the column seperation)

Column A .................Column B
Plan Name ..............Plan Start Date
Plan A .....................01/04/2008
Plan A .....................07/06/2009
Plan B..................... 01/09/2007
Plan B .....................01/10/2008
Plan B .....................01/10/2010
Plan C .....................01/08/2008
Plan C .....................01/09/2009

I hope this makes some kind of sense, sorry if I have not explained myself correctly.

Thanks
Jason

 

[GTO]

What are we deleting, the rows with duplicate plans and later dates?

 

[osgood82]

Yes that is exactly it, thanks for such a fast reply

 

[texasalynn]

Welcome to MrExcel board....
this will give you true/false, so you could then filter for the false and delete those rows

Excel Workbook
	A	B	C
1	Plan Name	Plan Start Date
2	Plan A	1/4/2008	TRUE
3	Plan A	7/6/2009	FALSE
4	Plan B	1/9/2007	TRUE
5	Plan B	1/10/2008	FALSE
6	Plan B	1/10/2010	FALSE
7	Plan C	1/8/2008	TRUE
8	Plan C	1/9/2009	FALSE
Sheet3

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[VoG]

This macro assumes that the dates are sorted ascending

Sub DelDups()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 3 Step -1
    If WorksheetFunction.CountIf(Columns("A"), Range("A" & i).Value) > 1 Then Rows(i).Delete
Next i
End Sub

 

[GTO]

Hi again,

In case the dates are not sorted, this would be slower but I think will test for the minimum date for ea plan.

Option Explicit
    
Sub exa()
Dim DIC         As Object   '<---Dictionary
Dim lLRow       As Long
Dim rngVals     As Range
Dim Cell        As Range
Dim aryVals     As Variant
Dim i           As Long
Dim ii          As Long
Dim wks         As Worksheet
    
    Set DIC = CreateObject("Scripting.Dictionary")
    '// Change to suit or better, use sheet's codename  //
    Set wks = ThisWorkbook.Worksheets("Sheet1")
    
    '// Set a reference to Plan Names, assumes one header row and data starting at row 2//
    With wks
        Set rngVals = Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    '// Plunk the vals into an array    //
    aryVals = rngVals.Value
        
    '// Get a collection of unique plans    //
    For i = 1 To UBound(aryVals, 1)
        DIC.Item(Trim(aryVals(i, 1))) = Empty
    Next
    
    '// Plunk them into an array (the array will now be one-dimensional)//
    aryVals = DIC.Keys
    
    For i = LBound(aryVals) To UBound(aryVals)
        '// Clear keys  //
        DIC.RemoveAll
        
        '// run thru the range, and add dates for the specific plan//
        For Each Cell In rngVals
            If Trim(Cell.Value) = aryVals(i) Then
                DIC.Item(Cell.Offset(, 1).Value2) = Empty
            End If
        Next
        
        '// Find the latest last row, as this will change as we loop//
        lLRow = wks.Cells(wks.Rows.Count, 1).End(xlUp).Row
        
        '// run UP the range... //
        For ii = lLRow To 2 Step -1
            '// ...if the cell has the plan we're looking for...    //
            If Trim(wks.Cells(ii, 1).Value2) = aryVals(i) Then
                '// if the date next to it isn;t the minimum date we have in the current//
                '// .Keys, delete the row                                               //
                If Not wks.Cells(ii, 2).Value2 = Application.Min(DIC.Keys) Then
                    wks.Rows(ii).Delete
                End If
            End If
        Next
    '// loop for ea plan//
    Next
End Sub

Hope that helps,

Mark

 

[osgood82]

Thanks Guys for your help.... it is very much appreciated and congrats on a very useful site indeed