Posts by pjums
-
-
-
-
-
-
-
-
-
-
Thanks Carim, I think I know how to use countif and that's why believe my logic is not correct but cant figure out how to fix it....
Perhaps I should rephrase my question and say :
I need a logic to return the total count of rows from Raw Data table which match 2 criteria:
1- column E contains Fix
2- column F of Raw Data matches any entry present in Column A of the slicer table
I don't care if its countif or anything else, so long as I can have a right output
-
-
I am sure I am not oing it right but cant get my head around the right way!
=(COUNTIFS('Raw Data'!E:E,"Fix",'Raw Data'!F:F.'Slicer'!A:A))
I want the logic to return the count of rows from Raw Data table when column E contains Fix and column F of Raw Data matches any entry present in Column A of the slicer table
Silcer table looks like this
Row Labels Complex CR Incidents PRTG Alerts Requests RFI Simple CR -
Display MoreRegarding 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
thank you ... my headers are in row 1.
-
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 ?
Display MoreCode- Sub RemoveDuplicates()
- Dim rng As range
- Set rng = ActiveSheet.[A2].CurrentRegion
- 'Set tbl = ActiveCell.CurrentRegion
- 'tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
- Application.ScreenUpdating = False
- With rng
- 'PRTG
- .AutoFilter
- .AutoFilter Field:=8, Criteria1:="Duplicate"
- .AutoFilter Field:=5, Criteria1:="Response"
- .AutoFilter Field:=6, Criteria1:="PRTG Alerts"
- .AutoFilter Field:=15, Criteria1:="<>*Incident First Assignment*"
- ' Delete Result i.e Visible Rows
- range("A2", range("A" & Rows.Count).End(xlUp)).SpecialCells(12).EntireRow.Delete
- ' all but PRTG
- .AutoFilter Field:=8
- .AutoFilter Field:=5
- .AutoFilter Field:=6, Criteria1:=">" & "PRTG Alerts"
- .AutoFilter Field:=15, Criteria1:="<>*Incident Intiial First Assignment"
- ' Delete Result i.e Visible Rows
- range("A2", range("A" & Rows.Count).End(xlUp)).SpecialCells(12).EntireRow.Delete
- 'Requests
- .AutoFilter
- .AutoFilter Field:=8, Criteria1:="Duplicate"
- .AutoFilter Field:=5, Criteria1:="Fix"
- .AutoFilter Field:=6, Criteria1:="RFI", Operator:=xlOr, Criteria2:="*CR*"
- .AutoFilter Field:=15, Criteria1:="Change - Standard Service Request"
- ' 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
-
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
-
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
-
just have too much backlog because I was off sick- I am testing it now
-
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.
-
Agree !
.AutoFilter Field:=8, Criteria1:="Duplicate" returns 1004 error
-
I may need your address to send a token of recognition :X
