Posts by pjums

    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

    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

    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 ?

    Files

    • For Carim.xlsx

      (44.46 kB, downloaded 55 times, last: )

    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


    Row Labels
    Complex CR
    Incidents
    PRTG Alerts
    Requests
    RFI
    Simple CR

    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 ?


    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