I googled it and replaced it. It removed the headers.
Remove all but first duplicate
- pjums
- Thread is marked as Resolved.
-
-
I googled it and replaced it. It removed the headers.
I do not know what you are exactly talking about ...
SpecialCells itself ... does not interfere with the Range size ...
You have to make sure your Range is properly defined ...
Should you need assistance ... feel free to post your macro ...
-
Regarding the macro to deal with all Duplicates with SLA type equal to Fix ...
Code
Display MoreSub RemoveDupsFix() ' Second Block to deal with Duplicates - SLA Type : Fix Dim rng As Range, rngT As Range Dim c As Range Dim FirstFilteredRow As Range Dim n As Long, i As Long Dim a As Long, b As Long Set rng = Sheet1.[A1].CurrentRegion Application.ScreenUpdating = False With rng ' Block #2 .AutoFilter .AutoFilter Field:=2, Criteria1:="Duplicate" .AutoFilter Field:=1, 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 = Sheet1.Range("C2", Range("C" & 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:=3, Criteria1:=.Keys()(i) b = Sheet1.Range("A100000").End(xlUp).Row Set FirstFilteredRow = rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).Areas(1).Rows(1) a = FirstFilteredRow.Row rng.AutoFilter Field:=5, Criteria1:=">" & "*" & Evaluate("=SMALL(RIGHT(E" & a & ":E" & b & ",1)*1,1)") Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete Next i End With End If rng.AutoFilter Application.ScreenUpdating = True End Sub
Let me know what is the outcome of your tests ...
Hope this will help
-
Hello,
Once you have tested the ' second block ' macro ... feel free to share your comments ...
-
Rather surprised not to get any feedback ....
-
-
Thanks a million. Have been unwell this week. Back to work today, checking them now.
-
The right order of Priorities:
1. Hope YOU are doing well
and then
2. Hope the macro will operate as expected ...
-
Thanks alot Carim. Very kind of you.
rng.AutoFilter Field:=5, Criteria1:=">" & "*" & Evaluate("=SMALL(RIGHT(E" & a & ":E" & b & ",1)*1,1)")
What is E ? Column E ?
-
the macro works on the example kinked here but fails at this line with Type 13 error when I translate it to my file ( the file has sensitive data which I cant share)
-
You are welcome ...
Yes E ... in this intruction is for Column E ...where SLA is listed and where .... starting from the Right ... you can extract the Priority Number ... such as in Fix - Product Type 1 - P1 to get the Number 1 ...
Hope this clarifies
-
-
Well ... do not know the exact structure of your actual file ...
But ... wherever is located the SLA column which holds the Priority Numbers ... it is the Column Letter to use in replacement of Letter E
Another possibility ... is for you ... to attach a file ONLY with your Headers in the first row .... and no data ...
-
I changed it to
rng.AutoFilter Field:=15, Criteria1:=">" & "*" & Evaluate("=SMALL(RIGHT(O" & a & ":O" & b & ",1)*1,1)")
-
Thanks for the Headers file ... AND the respective Column Letters of your Original Test file ...
Will review the macro accordingly ...
-
No hair left on my head ...!!!
Hopefully ... following modifications should be in line with your actual structure ...
Code
Display MoreSub RemoveDupsFixAdapted() ' Second Block to deal with Duplicates - SLA Type : Fix Dim rng As Range, rngT As Range Dim c As Range Dim FirstFilteredRow As Range Dim n As Long, i As Long Dim a As Long, b As Long Set rng = Sheet1.[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 = Sheet1.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) b = Sheet1.Range("A100000").End(xlUp).Row Set FirstFilteredRow = rng.Offset(1, 0).SpecialCells(xlCellTypeVisible).Areas(1).Rows(1) a = FirstFilteredRow.Row rng.AutoFilter Field:=15, Criteria1:=">" & "*" & Evaluate("=SMALL(RIGHT(O" & a & ":O" & b & ",1)*1,1)") Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete Next i End With End If rng.AutoFilter Application.ScreenUpdating = True End Sub
-
does criteria have to be a exact match ?
On the response dups it would be wonderful to have something more mature like:
.AutoFilter Field:=8
.AutoFilter Field:=5
.AutoFilter Field:=6, Criteria1:="PRTG Alerts"
.AutoFilter Field:=15, Criteria1:= <<Doesnt contain Assignment>> Operator:=xlAnd, Criteria2:= <<Contains First>>
as opposed to more static:
.AutoFilter Field:=8
.AutoFilter Field:=5
.AutoFilter Field:=6, Criteria1:=">" & "PRTG Alerts"
.AutoFilter Field:=15, Criteria1:="Incident First Assignment" Operator:=xlOr, Criteria2:="<=1000"
-
-
I may need your address to send a token of recognition :X
-
Pooja,
Before getting back to refining the First Block designed for Response Dups ...
would suggest to get the Second Block designed for Fix Dups out of the way ...
Do you agree ...???
-
Agree !
.AutoFilter Field:=8, Criteria1:="Duplicate" returns 1004 error
-
By the way ... regarding your real life worksheet .... is the Task Type located in Column J or in Column P ...???
The Column where you are getting PRTG, SQL, etc ....
-
i added the corresponding column in row 2 of the real life worksheet. Please read task Type as Ticket type which is column F.
Column J is Task.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!