Remove all but first duplicate

Great ... Thanks ...

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

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

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 " ...

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

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 ...

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

My crystal ball is starting to smoke ....

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

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 ...

Sub RemoveDupsFixAdaptedV2()
' Second Block to deal with Duplicates - SLA Type : Fix
' Version Adapted 2 - 3 Mar 2021
Dim rng As Range, rngT As Range
Dim c As Range
Dim FirstFilteredRow As Range
Dim LastFilteredRow As Long
Dim n As Long, i As Long
Dim a As Long, b As Long

Set rng = ActiveSheet.[A1].CurrentRegion
Application.ScreenUpdating = False
    
    With rng
    ' Block #4
        .AutoFilter
        .AutoFilter Field:=8, Criteria1:="Duplicate"
        .AutoFilter Field:=5, Criteria1:="Fix"
    End With
    
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Loop for Each Unique Task INC                                                          '
        ' Filter with same criteria in order to remove Rows when Priority is Lower than Top one  '
        ' and ONLY Keep the One Row which shows The Top Priority N°  i.e. P with Smallest Number '
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
        Set rngT = ActiveSheet.Range("J2", Range("J" & Rows.Count).End(xlUp))
        If Not rngT Is Nothing Then
            With CreateObject("Scripting.Dictionary")
                For Each c In rngT.SpecialCells(xlCellTypeVisible).Cells
                    n = n + 1
                    If Not .Exists(CStr(c.Value)) Then .Add CStr(c.Value), n
                Next c
                
                For i = 0 To .Count - 1
                    rng.AutoFilter Field:=10, Criteria1:=.Keys()(i)
                        Set FirstFilteredRow = rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).Areas(1).Rows(1)
                        a = FirstFilteredRow.Row
                            With rng.SpecialCells(xlCellTypeVisible)
                                LastFilteredRow = .Areas(.Areas.Count).Row + .Areas(.Areas.Count).Rows.Count - 1
                            End With
                        b = LastFilteredRow
                        rng.AutoFilter Field:=15, Criteria1:=">" & "*" & Evaluate("=SMALL(RIGHT(O" & a & ":O" & b & ",1)*1,1)")
                    Range("O2", Range("O" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                Next i
            End With
        End If
        
    rng.AutoFilter
    
Application.ScreenUpdating = True

End Sub

Hope this will help

Quote from pjums

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 ...

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

Hello again,

In order to consolidate your three blocks ... attached is your complete test file

Hope this will help

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

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

Hello Pooja,

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

Question : is there some left on Earth ...