Consulting

Results 1 to 5 of 5

Thread: Remove duplicates macro for a table ?

  1. #1

    Remove duplicates macro for a table ?

    I am looking to remove duplicates from column B :

    Date Order Shipping status Animal
    1 14-06-24 1000 Shipped Dog
    2 14-06-24 1001 In-progress Cat
    3 14-06-24 1001 Staged Cat
    4 14-06-24 1001 In-progress Elephant

    We can see that B2, B3 and B4 are duplicates. I would like to remove the entire rows for two of them using a vba script, then automatically sort column D alphabetically (Cat, Dog, Elephant).
    I know I can do those two things individually with the Data -> Remove duplicate and then sort, but I would like to do it with just a click of a button.

    Few things :
    This must be done from the current (active) sheet, as we have multiple sheets with the same table. The table names are unknown, so using the table name is impossible. However, the column headers are static and in this case for this column it is "Order".

    Thank you!

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,189
    Location
    Quote Originally Posted by MasterBash View Post
    I am looking to remove duplicates from column B :

    Date Order Shipping status Animal
    1 14-06-24 1000 Shipped Dog
    2 14-06-24 1001 In-progress Cat
    3 14-06-24 1001 Staged Cat
    4 14-06-24 1001 In-progress Elephant

    We can see that B2, B3 and B4 are duplicates.
    Sorry but your logic is a little wayward. Values B2,B3 & B4 are not duplicates. They refer to the same Order, but reflect either a different Status, or a different animal.

    This must be done from the current (active) sheet, as we have multiple sheets with the same table. The table names are unknown, so using the table name is impossible. However, the column headers are static and in this case for this column it is "Order".


    The tables will have a object number and a sheet name.

    Can you please attach a sample workbook, so we can remove any ambiguity fro your request?





    I would like to remove the entire rows for two of them using a vba script, then automatically sort column D alphabetically (Cat, Dog, Elephant).
    I know I can do those two things individually with the Data -> Remove duplicate and then sort, but I would like to do it with just a click of a button.

    Few things :
    This must be done from the current (active) sheet, as we have multiple sheets with the same table. The table names are unknown, so using the table name is impossible. However, the column headers are static and in this case for this column it is "Order".

    Thank you![/QUOTE]
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    workbook.xlsx

    I added the Before and After removing duplicates (column B) and sorting (Column D, made a mistake on the sheet and sorted column B, my bad). However, that remove duplicates and sorting will be done in the same table (In the Before sheet).
    It only matters if Order has duplicates, not the other columns.

    Everyday, we add a new sheet based on that day's date and a new table is being created off a template. So every day, the new sheet will have a different name (date) and the table name will be different. However, the table's columns stay the same.
    Last edited by MasterBash; 06-17-2024 at 03:22 PM.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,748
    Location
    Guessing here

    I made some dummy data in the attachment


    Option Explicit
    
    
    Sub WhatYouAskedFor()
        Dim r As Range, r1 As Range
        
        Application.ScreenUpdating = False
        
        Set r = ActiveSheet.Cells(1, 1).CurrentRegion
        Set r1 = r.Cells(2, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
        
        r.RemoveDuplicates Columns:=2, Header:=xlYes
                
        With ActiveSheet.Sort
            .SortFields.Clear
            .SortFields.Add Key:=r1.Columns(4), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange r
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Application.ScreenUpdating = True
        
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    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) / 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

  5. #5
    That works great Paul, thank you very much !

Posting Permissions

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