Remove all but first duplicate

pjums · Feb 20th 2021

Thank you.

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 Assignment and Remove all other DUPLICATE 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 DUPLICATE instances.

5. Within the Fix SLA Type, one single rule applies, which is to Keep P1 and Remove all other instances - Keep the highest Priority which can range from P1 (highest) to P4(lowest). INCxx may have P2 and P4 linked to it - Keep P2, remove P4, for INCyy make have P1, P2 and P3 linked to it - Keep P1, remove P2 and P3.

6- Each INC must have 1 Response and 1 Fix linked to it, after removing duplicates based on above rule. But some may have 0 Response or 0 Fix linked to it. I need to find a way to highlight them in any way you seem fit.

pjums · Feb 20th 2021

Thank you.

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) - So actually PRTG is an exception. The same rule to keep ' Incident First Response' applies to any other than PRTG, which includes SQL, RDS.

3. For the PRTG Task Type, the rule is to Keep the one showing in SLA Column 'Incident First Assignment and Remove all other DUPLICATE 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 DUPLICATE instances.

5. Within the Fix SLA Type, one single rule applies, which is to Keep P1 and Remove all other instances - Keep the highest Priority which can range from P1 (highest) to P4(lowest). INCxx may have P2 and P4 linked to it - Keep P2, remove P4, for INCyy make have P1, P2 and P3 linked to it - Keep P1, remove P2 and P3.

6- Each INC must have 1 Response and 1 Fix linked to it, after removing duplicates based on above rule. But some may have 0 Response or 0 Fix linked to it. I need to find a way to highlight them in any way you seem fit.

**Carim** · Feb 21st 2021

Thanks for your Recap

Looks like we have finally reached ... the starting line ...

pjums · Feb 21st 2021

?? Waiting for the Marco magic now ?

**Carim** · Feb 21st 2021

Quote from pjums

?? Waiting for the Marco magic now ?

Agree ...

Will have to build it with your ... six building blocks ...

Challenge is " how on earth ... can all these rules be translated into VBA "

pjums · Feb 21st 2021

Would a threat ?that the number of blocks may increase to 10 if still waiting for macro be a good push ?

**Carim** · Feb 21st 2021

Based on an initial test ... there seems to be ...still ... one point to further clarify ...

Regarding Rows 10 and 12, we are dealing with SQL and RDS ... which should follow the same rule ...

to delete SLA showing : " Incident First Assignment "

which is apparently not the case ...

Thanks for your clarification

pjums · Feb 21st 2021

Rule is correct. Labels were still incorrect. Changed them now. ???

**Carim** · Feb 21st 2021

OK ... first step

You could test following

Code

Sub RemoveDupsResponse()
' First Block to deal with Duplicates - SLA Type : Response
Dim rng As Range
Set rng = Sheet1.Range("$A$1:$F$16")
Application.ScreenUpdating = False
  
    With rng
      ' Block #1
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="Duplicate"
        .AutoFilter Field:=1, Criteria1:="Response"
        .AutoFilter Field:=6, Criteria1:="PRTG"
        .AutoFilter Field:=5, Criteria1:="Incident First Response"
    
    ' Delete Result i.e Visible Rows
    Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(12).EntireRow.Delete
    
    ' Block #2
        .AutoFilter Field:=5
        .AutoFilter Field:=6
        .AutoFilter Field:=6, Criteria1:="=RDS", Operator:=xlOr, Criteria2:="=SQL"
        .AutoFilter Field:=5, Criteria1:="Incident First Assignment"
        
    ' Delete Result i.e Visible Rows
    Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(12).EntireRow.Delete
    
        ' Remove Filter
        .AutoFilter
    End With
    
Application.ScreenUpdating = True

End Sub

Display More

Hope this will help

pjums · Feb 21st 2021

Thank you and sorry to be a pain. The second section needs to be more dynamic. Kind of looking for a way to delete first assignment from ANY duplicate Response rows, except for the PRTG(which has a different rule). My data set is not likely to be limited to SQL and RDS.

**Carim** · Feb 21st 2021

Quote from pjums

Thank you and sorry to be a pain. The second section needs to be more dynamic. Kind of looking for a way to delete first assignment from ANY duplicate Response rows, except for the PRTG(which has a different rule). My data set is not likely to be limited to SQL and RDS.

Not sure to understand your message ...

The macro RemoveDupsResponse is supposed to handle all possible cases whenever the SLA Type is Response ...

Does it produce the expected result ... or not ...???

pjums · Feb 21st 2021

It did produce the expected result. But in future I can have a few more ticket types in addition to PRTG, sql and RDS.

.AutoFilter Field:=6, Criteria1:="=RDS", Operator:=xlOr, Criteria2:="=SQL

Makes it more static. Can a logic be created that for ANY duplicate response other than PRTG removes first assignment please.

**Carim** · Feb 21st 2021

Got it ...

Below is the modification ...

Code

Sub RemoveDupsResponse()
' Version 2 - First Block to deal with All Duplicates with SLA Type : Response
Dim rng As Range
Set rng = Sheet1.Range("$A$1:$F$16")
Application.ScreenUpdating = False
  
    With rng
      ' Block #1
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="Duplicate"
        .AutoFilter Field:=1, Criteria1:="Response"
        .AutoFilter Field:=6, Criteria1:="PRTG"
        .AutoFilter Field:=5, Criteria1:="Incident First Response"
    
    ' Delete Result i.e Visible Rows
    Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(12).EntireRow.Delete
    
    ' Block #2
        .AutoFilter Field:=5
        .AutoFilter Field:=6
        .AutoFilter Field:=6, Criteria1:=">" & "PRTG"
        .AutoFilter Field:=5, Criteria1:="Incident First Assignment"
        
    ' Delete Result i.e Visible Rows
    Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(12).EntireRow.Delete
    
        ' Remove Filter
        .AutoFilter
    End With
    
Application.ScreenUpdating = True

End Sub

Display More

Hope this will help

pjums · Feb 22nd 2021

Thanks alot Carim. That should work, I will try it in couple of hours.

Would you have some time to put thought for :

5. Within the Fix SLA Type, one single rule applies, which is to Keep the highest Priority which can range from P1 (highest) to P4(lowest). INCxx may have P2 and P4 linked to it - Keep P2, remove P4.. INCyy may have P1, P2 and P3 linked to it - Keep P1, remove P2 and P3.

6- Each INC must have 1 Response and 1 Fix linked to it, after removing duplicates based on above rule. But some may have 0 Response or 0 Fix linked to it. I need to find a way to highlight them in any way you seem fit.

**Carim** · Feb 22nd 2021

You are welcome

Once you are in position to test ...(and hopefully validate ...) the first macro ... it will be the first relief ...!!!

Then, we will be confronted with the next stumbling blocks ...and proceed step-by-step ...

pjums · Feb 22nd 2021

That works Carim.. Thanks Alot. I made some changes like making the range dynamic etc, but I get the logic.

Kind of mostly need help with the logic that i can build on.

**Carim** · Feb 22nd 2021

Great News ...!!!

Thanks a lot for your Thanks ...AND for the Like

By the way regarding the dynamic range... change the instruction to :

Code

Set rng = Sheet1.[A1].CurrentRegion

Hope this will help

**Carim** · Feb 22nd 2021

Regarding the next two issues still pending ... both problems do require to handle each Task INC individually ...

Indeed the Highest Priority is, by definition, the Smallest Priority Number ... which is related to each INC ....

Will post a proposal as quickly as possible ...

pjums · Feb 22nd 2021

Thank you.

What does 12 signify in SpecialCells(12) ?

**Carim** · Feb 22nd 2021

Quote from pjums

Thank you.
What does 12 signify in SpecialCells(12) ?

It means: xlCellTypeVisible

Hope this will help

P.S. To make code clearer .... you are right ... should have used SpecialCells(xlCellTypeVisible)

Remove all but first duplicate

Participate now!

Share