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
 

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
Array Formulas
CellArray Formula
C2=IF(MIN(IF($A$2:$A$100=$A2,$B$2:$B$100,""))=B2,TRUE,FALSE)
Press CTRL+SHIFT+ENTER to enter array formulas.
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
 

Forum statistics

Threads
1,081,726
Messages
5,360,903
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top