Remove all but first duplicate

    Hello,


    To remove duplicates from multiple columns ... you could test following


    Code
    Sub RemoveDups()
    Range("A1:E11").RemoveDuplicates Columns:=Array(1, 3, 4), Header:=xlYes
End Sub


    Hope this will help

    :)

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

    Love 1

    You don't need VBA if you are using one of the newer versions of Excel.


    Data->Data Tools ->Remove duplicates


    This tool will remove all duplicates leaving one example of each

    Quote from pjums

    Many Thanks Carim. As always.


    Now that I see it it looks like such an easy solution !


    You are the best !


    Thanks a lot for your Thanks ...AND for your very kind words :)


    AND also for the Like :thumbup:

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

    Hi Carim,


    I wonder if you may help me with the next stage of the macro, I want to get specific with which duplicate to remove. The frmula I am looking for is if Column A, C and D match then if Column F is PRTG, remove the row with Column E value = "Incident First response" but if Column F is SQL remove the row with Column E value = "Incident First Assignment"


    Could you please do your magic ?


    Many Thanks

    Hello,


    Nothing really magic ... but Excel loves ... logic  ;)


    My understanding is that you are facing two types of potential Duplicates ...


    Is that right ...?


    If not could you clarify the number of different cases ...;)

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

    Yes, that's right. Think if you could help me with the logic I requested above (for PRTG and SQL) , I would be able to expand on it. I just need some hand holding to start with it please.

    The problem is I can't just say remove all the assignment sla ( column E) for PRTG tickets blindly. In some cases it doesn't have assignment sla, so I need to keep response SLA on it. So the first step is it has to have duplicate Response sla ( column c) only then do the next step! Hope I make sense.

    It seems to me you do need to dig a little bit further ...


    1. When comparing Duplicates in Rows 9 and 10 ... you have highlighted row 10 ...

    BUT if you are to apply your rule : "Column F is SQL, so remove the row where Column E value = "Incident First Assignment" ...

    This votes for Row 9 ...


    2. You have described what should be done for Column F values equal to PRTG and SQL .... but no indication for values RDS and Empty


    3. What should be done for Row 13 - RDS - Is it a True Duplicate or a False one ...?


    4. You seem to have inserted Column B to come up with your own rules to differentiate between your True Duplicates vs your False Duplicates ... wouldn't make more sense to optimize your formula to actually generate the expected result... taking into consideration all your rules ... ???


    Hope this will help

    :)

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

    Thanks for all the additional explanations ....:)


    Let's face it ... the more conditions you are adding ... the less we are dealing with ' standard duplicates ' ...


    As soon as I have a moment, will dive into all your detailed conditions ... ;)

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

    Hello,


    When it comes to analyzing your revised 'Cleaning' Column, one can draw the conclusion Rows 4,5,11 and 15 are coded to be deleted ...

    despite the fact they do NOT qualify as Duplicates ...


    As a consequence, it would appear to me your rules to determine which of the 3 new status have to be clarified ...;)

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

    Love 1
    Quote from pjums

    Row 4,5,11 and 15 are not to be deleted, because they don't qualify as duplicates. Does this platform allow chats?

    Thanks for the clarification ...:)


    With your Legend Green = Remove ... my understanding was they needed to be deleted ...;)

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

    Love 1

    Quite obviously you do master your own topic ...

    But since it's all Greek to me ... before diving into a macro, it is safer have to validate all the potential cases for All your Duplicates ...


    1. There are Two SLA Types ... Response And Fix ... each one does require its own treatment .

    2. Within the Response SLA Type, there are two different rules for the Three Task Types : a) PRTG And b) SQL,RDS (same rule)

    3. For the PRTG Task Type, the rule is to Keep the one showing in SLA Column 'Incident First Assigment' and Remove all other instances

    4. For the SQL or RDS Task Types, the rule is to Keep the one showing in SLA Column 'Incident First Response' and Remove all other instances.

    5. Within the Fix SLA Type, one single rule applies, which is to Keep P1 and Remove all other instances


    Hope my understanding is now correct ...:)

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

    Love 1

Participate now!

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

Register Yourself Login