Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Find Duplicates Delete All Except Latest Entry

  1. #1
    Join Date
    6th November 2006
    Posts
    14

    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    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 at 18:49.

  3. #3
    Join Date
    6th November 2006
    Posts
    14

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Find Duplicates Delete All Except Latest Entry

    Hi,

    VB:
    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

  5. #5
    Join Date
    6th November 2006
    Posts
    14

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Find Latest Date Listed Against Specific Name
    By mikeyks in forum Excel General
    Replies: 4
    Last Post: May 20th, 2008, 11:22
  2. Stop Duplicates & Validate Date Entry
    By dgraham1956 in forum Excel General
    Replies: 3
    Last Post: July 7th, 2007, 11:59
  3. Delete Duplicates That Have Sum Zero
    By atran in forum Excel General
    Replies: 7
    Last Post: February 27th, 2007, 18:11
  4. data validation prvent duplicates force entry
    By search4f in forum Excel General
    Replies: 1
    Last Post: May 18th, 2006, 10:42
  5. Formulas: Find latest entry
    By Rick Manasa in forum Excel General
    Replies: 12
    Last Post: October 22nd, 2003, 06:07

Bookmarks

Posting Permissions

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