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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
What are we deleting, the rows with duplicate plans and later dates?
 
Upvote 0
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 {}.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thanks Guys for your help.... it is very much appreciated and congrats on a very useful site indeed
 
Upvote 0

Forum statistics

Threads
1,224,597
Messages
6,179,808
Members
452,944
Latest member
2558216095

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top