Remove all but first duplicate

  • Great ... Thanks ...


    Now ... have you had a chance to test the macro RemoveDupsFixAdapted ... ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Sincerely hope you will not give up .... on your SLA Specific Dups project ... !!! ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Well ... you'd better start by catching up on the backlog of your tasks ...


    Then ... we will resume your operations on the " SLA Specific Dups Project " ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hi Carim


    Thank you for your paitence.


    rng.AutoFilter Field:=15, Criteria1:=">" & "*" & Evaluate("=SMALL(RIGHT(O" & a & ":O" & b & ",1)*1,1)")


    is returning error 13


    Only change I made to your code was replaced sheet1 with ActiveSheet

  • Hi Pooja,


    Hope you are doing better ...;)


    To put things back in their context ... you are testing the latest Sub RemoveDupsFixAdapted() macro, in order to only keep the Highest Priority ( which is by design the Smallest Number ...)


    With ActiveSheet, you are constraining yourself to always run the macro from the active sheet ....

    By the way, if it is always the case ... you can remove it altogether ... since ActiveSheet id the default ...


    1. Do we agree that in your real life sheet ... descriptions similar to " Fix - Product Type 1 - P1" do show in Column O (after N & Before P)


    2. Do we agree that in your real life sheet ... descriptions similar to " Fix - Product Type 1 - P1" always end up with the Priority Number


    3. Do we agree that in your real life sheet ... descriptions similar to " Fix - Product Type 1 - P1" do not have hidden spaces at the end ...


    Thanks for your confirmations

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • To put things back in their context ... you are testing the latest Sub RemoveDupsFixAdapted() macro, in order to only keep the Highest Priority ( which is by design the Smallest Number ...)

    - Yes.


    1. Do we agree that in your real life sheet ... descriptions similar to " Fix - Product Type 1 - P1" do show in Column O (after N & Before P)

    Yes, its in COlumn O


    2. Do we agree that in your real life sheet ... descriptions similar to " Fix - Product Type 1 - P1" always end up with the Priority Number

    I found there is one more category which doesnt end in a number. Fix - Desktop Support - Quick Assist. We may need to include it but for now the code to work I have suffixed it to make it look like "Fix - Desktop Support - Quick Assist - P4"



    3. Do we agree that in your real life sheet ... descriptions similar to " Fix - Product Type 1 - P1" do not have hidden spaces at the end ...

    No hidden spaces

  • You may not like me going to tangent but I need to priortise removing other response duplicates. I have re-written this. Its removing all the duplicates, but its also removing the headers of the table. WHat is wrong with this code ?


  • Thanks a lot for all your very precise answers ...!!!:)


    From my point of view ... there is no reason for this specific instruction not to operate as expected ...


    By the way ... forgot to mention that ... with your initial test file... it does work perfectly ...;)


    Could it be ... that there is a bug in any of the previous instructions ... such the need to adjust Column J ... which is ... for you ... apparently Column C ...


    Code
    Set rngT = Sheet1.Range("C2", Range("C" & Rows.Count).End(xlUp))


    My crystal ball is starting to smoke ....=O

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Regarding your message # 70 ....


    You should be careful with using .CurrentRegion


    It is the equivalent of Ctrl A .... which means the whole range ...


    Seems you headers are not in Row 1 ... so you are in for a mess ...


    Either Headers are in Row 1 ... and you can use CurrentRegion

    or

    Headers are somewhere else .... and you need to define the Range precisely ...


    Hope this clarifies

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    In order to make sure all the Columns which are to be permuted all over ... do correspond to you actual file ...


    have re-built from scratch a test file to ensure the revisited macro Version 2 does operate as expected ... in your real-life sheet ...



    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • thank you ... my headers are in row 1.

  • thank you ... my headers are in row 1.

    If your headers are located in Row 1 ... from looking at you macro ... there is nothing wrong ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Since you are juggling with many macros at the same time ...


    Whenever you can you will let me have your comments about the macro posted in message # 73

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Regarding your earlier question about your own test ... and about what might go wrong ... besides CurrentRegion ...


    Forgot to mention ... you do have to carefully label your Criteria ... and make sure they do produce the expected outcome ...


    otherwise ... the whole filtering process will end up with no filtered rows ... which will lead to your headers being deleted ...


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Once you have tested the three macros of your ' Remove SLA Specific Dups ' file ... feel free to share your comments ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello Pooja,


    As you know ... perseverance has landed on Mars ...


    Question : is there some left on Earth ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!