Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2008
    Thanked 0 Times in 0 Posts
    In Excel 2003, is there a way to sort so that the only most recent date row are kept if the first two columns have teh same data?

    For the example attached, I would like to get the end result to show all rows that are not in yellow and only the row that I highlighted yellow the most recent date (becasue the first two columns contain the same information).

    Thank you for your help.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts
    That's not just sorting, you need to delete rows as well. You can do that using a macro:

    Sub RemoveDups()
      Dim r As Long
      Dim m As Long
      Cells(1, 1).CurrentRegion.Sort Key1:=Cells(1, 1), Key2:=Cells(1, 2), _
    	Key3:=Cells(1, 3), Order3:=xlDescending, Header:=xlYes
      m = Cells(Rows.Count, 1).End(xlUp).Row
      For r = m To 3 Step -1
    	If Cells(r, 1) = Cells(r - 1, 1) And Cells(r, 2) = Cells(r - 1, 2) Then
    	  Cells(r, 1).EntireRow.Delete
    	End If
      Next r
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts
    If you put the array formula (confirm with ctrl-alt-shift) in D1:

    And copy this to D220

    You will get in col D a value (True/False) representing whether the date is the max for the value in col A. You can sort by D (Descending), followed by anything you want to get the max dates for the values first.

    If you had a header row, you could also filter in Col D for True to only display the Max date values


Posting Permissions

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