Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2008
    Posts
    7
    Thanks
    0
    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
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    That's not just sorting, you need to delete rows as well. You can do that using a macro:

    Code:
    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
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you put the array formula (confirm with ctrl-alt-shift) in D1:
    =MAX(IF($A$1:A20=A1,$C$1:$C$20))=C1

    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

    Steve

Posting Permissions

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