Announcement

Collapse
No announcement yet.

Find Duplicates Delete All Except Latest Entry

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Find Duplicates Delete All Except Latest Entry

    Hi, I am new to this forum and have done some searching but am unable to find a reply to my problem. I only have basic Excel knowledge and am new to writing macros.
    I have a spreadsheet that has reference numbers in column D. I need to do a macro that will identify duplicates, then check the date coded in column N and delete all but the latest record. This must be repeated till the end of the file which can vary in length.
    Please can someone assist me.
    Thanks

  • #2
    Re: Find Duplicates Delete All Except Latest Entry

    Do you want the entire row deleted, just the range D:N or only the date in N?

    Also, is your data sorted? If so, by which Column?
    Last edited by Dave Hawley; November 6th, 2006, 18:49.

    Comment


    • #3
      Re: Find Duplicates Delete All Except Latest Entry

      Hi Dave,

      I need the entire row deleted. The file is sorted by Column D and then by Column N in ascending order.

      Thanks

      Comment


      • #4
        Re: Find Duplicates Delete All Except Latest Entry

        Hi,

        Sub DelDupesExceptLast()
        Dim i As Long
        i = Range("A" & Rows.Count).End(xlUp).Row
        Application.ScreenUpdating = False
        Columns(15).Insert
        [o1] = "temp"
        With Range("O1:O" & i)
        .Offset(1, 0).Resize(.Rows.Count - 1, 1).Formula = "=COUNTIF($D$2:$D$" & i & ",D2)=COUNTIF($D$2:D2,D2)"
        .AutoFilter field:=1, Criteria1:="FALSE"
        .Offset(1, 0).Resize(.Rows.Count - 1, 1).EntireRow.Delete
        .AutoFilter
        End With
        Columns(15).Delete
        Application.ScreenUpdating = True
        End Sub


        HTH
        Kris

        ExcelFox

        Comment


        • #5
          Re: Find Duplicates Delete All Except Latest Entry

          Thanks very much works great. You have saved me a lot of frustration. This is a great site and I am sure I will learn a lot !: D : D : D

          Comment

          Working...
          X