Consulting

Results 1 to 3 of 3

Thread: Best way to handle duplicate records....

  1. #1

    Best way to handle duplicate records....

    I have records like below. Aim is that the
    Last duplicate record shall stay test shall get deleted.
    In below example Row 1,Row 2 shall get deleted because A1 is duplicate 3 times. How can I achieve this for large number of Randomly arranged records?



    Column A. Column B
    A1. B1
    A1. B2
    A2. A21
    A1. B3

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,645
    Location
    Maybe


    Option Explicit
    
    
    
    
    Sub DeDup()
        Dim r As Range
        Dim i As Long
        
        Set r = ActiveSheet.Cells(1, 1).CurrentRegion
        
        With r
            For i = .Rows.Count To 3 Step -1
                Call Range(.Cells(2, 1), .Cells(i - 1, 1)).Replace(.Cells(i, 1).Value, True, xlWhole)
            Next i
            
            On Error Resume Next
            .Columns(1).SpecialCells(xlCellTypeConstants, xlLogical).EntireRow.Delete
            On Error GoTo 0
        End With
    End Sub
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,572
    Without code you can use Excel's Remove Duplicates but it needs a tweak since that functionality removes duplicates from the bottom up: You could add a column, add numbers to it ascending, then sort the range descending, do the Remove Duplicates, then sort again ascending, finally remove the added column.
    Quite a palaver.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

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