Find earliest data by date

osgood82

New Member
Joined
Apr 4, 2011
Messages
9
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
What are we deleting, the rows with duplicate plans and later dates?
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
Welcome to MrExcel board....
this will give you true/false, so you could then filter for the false and delete those rows
Excel Workbook
ABC
1Plan NamePlan Start Date
2Plan A1/4/2008TRUE
3Plan A7/6/2009FALSE
4Plan B1/9/2007TRUE
5Plan B1/10/2008FALSE
6Plan B1/10/2010FALSE
7Plan C1/8/2008TRUE
8Plan C1/9/2009FALSE
Sheet3
Excel 2003
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
This macro assumes that the dates are sorted ascending

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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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.
Rich (BB code):
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

New Member
Joined
Apr 4, 2011
Messages
9
Thanks Guys for your help.... it is very much appreciated and congrats on a very useful site indeed
 

Watch MrExcel Video

Forum statistics

Threads
1,102,551
Messages
5,487,522
Members
407,604
Latest member
sama9000

This Week's Hot Topics

Top