Posts by pjums

Thanks a ton. Could you spare a few mins to explain the above formula to me ? esp this part : MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,32)

Lemon squeezy - Thanks a ton.

can you think of how to do the second part please - Second request is to do a vlookup in each account tab( as I have given as example in IBM tab) with the table in Worksheet and return Name values if the tab name matches the account and Name matches the name.

Hello all

Can you please help me write a formula in column C ( Value) of the worksheet to return the Value corresponding to the Name (column B) in the tab matching column A.

I have highlighted the row in respective tabs for clarity.

Second request is to do a vlookup in each account tab( as I have given as example in IBM tab) with the table in Worksheet and return Name values if the tab name matches the account and Name matches the name.

Many Thanks

Hello,

I have a pivot table with A to Z Company Names in the columns and Months in the rows. Cell value is the total sales from that company per month.

I can filter columns to see the top 5 grossing companies for the whole year but that's not what I want

I want to be able to filter on top 5 grossing company for each month.

SO I want to see company names A, B, C, D, E that did max sales in Jan

and companies D,E, F, G, H, I that did max sales in Feb

Possible ?

hello

I have an excel with several tabs - Main, Apples, Bananas, Grapes, Carrots....

On the Main tab column A contains all the tab names

What I need to do is in cell B2 of Main tab is:

if A2= Apples, then go to the tab Apples , count all the 'Yes' in column F of Apple tab and return the count value to cell B2 of Main tab.

And then fill the formula to all the cell of column B, so if A3= Bananes then go to the tab Bananas, count all the 'Yes' in Column F of Bananas tab and return the count value to cell B3 of Main tab.

Thank you so much for your help

Best Regards

Why the sensitivity around curly parenthesis, can something be done about it, fearing someone will break it

WORKS!

Thanks a lot

Have removed dates to avoid noise.

Based on the pivot I should see 131 for incidents and prtg in B6, but its showing 0

Not that simple...

All the range need to be dynamic!

I should also be able to select more than one category on the slicer in column K

they appear in a column that I have deleted !

I think you can completely ignore the dates, they are not relevant. Remove them from the formula for the ease of it.

Here

Look for the formula in B6 to B8

I want to be able to select the ticket types from the slicer pivot in Row K and have my numbers returned accordingly

Makes sense ?

Guilty as charged!

What I am trying to acheive is more complex than what I have requested here... so sample file will confuse. Does my logic make some sense to attempt something without the sample file?

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

No !! I am only just back at working on this project today... This issue takes priority now !

a big HIIIIIIII btw, missed you

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

Quote from Carim

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

Display More

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 ?

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