+ Reply to Thread
Results 1 to 7 of 7

How to filter a field in pivot table using two filters

  1. #1
    Registered User
    Join Date
    08-25-2015
    Location
    Auckland, New Zealand
    MS-Off Ver
    2013
    Posts
    3

    How to filter a field in pivot table using two filters

    I am trying to filter a Total_Effect field in a pivot table based on two filters. The first filter is for value less than 0% and this result is then filtered to give me the bottom five results. The following shows how the source data could look like (note I have simplified the source data for this example). In the first example, it should give me the value of -0.20%, -0.80% & -1.00% and the second example should produce value of -0.15%, -0.20%, -0.40%, -0.80% & -1.00%.

    Stock Total_Effect
    A 0.30%
    B -0.20%
    C -1.00%
    D -0.80%
    E 1.10%

    Stock Total_Effect
    A 0.30%
    B -0.20%
    C -1.00%
    D -0.80%
    E 1.10%
    F -0.40%
    G -0.15%


    I know the vba code for filtering value less than 0 (shown below in bold) but don't know how to add another filter on the result of the first filter.

    Please Login or Register  to view this content.
    Thanks in advance
    Last edited by FDibbins; 08-25-2015 at 11:06 PM.

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: How to filter a field in pivot table using two filters

    post withdrawn by hum
    Last edited by humdingaling; 08-25-2015 at 11:27 PM. Reason: post withdrawn
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,498

    Re: How to filter a field in pivot table using two filters

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    I have added them for you - this time
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    08-25-2015
    Location
    Auckland, New Zealand
    MS-Off Ver
    2013
    Posts
    3

    Re: How to filter a field in pivot table using two filters

    Thanks FDibbins; sorry new to this forum; I should have read the rules before posting. Anyway, is anyone able to help? Thanks.

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: How to filter a field in pivot table using two filters

    someone can correct me if im wrong but im pretty sure you cannot apply two value filter per pivot field
    ie filter on top of filter

    the only way i can think of doing both bottom 5 less than zero you are going to need to do it with array and pivotitems filter
    much in the same way you would do for autofilter

  6. #6
    Registered User
    Join Date
    08-25-2015
    Location
    Auckland, New Zealand
    MS-Off Ver
    2013
    Posts
    3

    Re: How to filter a field in pivot table using two filters

    Thanks for the feedback. What do you mean by using array and pivot items filter?

    If it is not possible to do a filter on top of a filter, is it possible to set the second filter on a pivot chart in vba as I ultimately like to create a chart based on this pivot table? I.e. Set the pivot table filter to bottom 5 and create a chart with a filter of value less than 0. Anyone?

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: How to filter a field in pivot table using two filters

    i knew that was the coming question
    in all honestly i dont think i have done the best job of it...it seems to be a mish-mash of things as i am not very well versed in arrays
    and pivot vba is a minefield to me

    anyway since you didnt provide any data to work with...i just made it up to what i think your WB looks like
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 07-16-2015, 05:46 AM
  2. Replies: 0
    Last Post: 03-20-2015, 11:39 AM
  3. Applying Multiple Filters to a row field in Pivot table using VBA
    By pdalal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-10-2015, 02:11 PM
  4. Pivot Table: Multiple Value filters on one field
    By jamsta1972 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-27-2014, 02:20 PM
  5. Locking only one page field (report filters) of a pivot table in a protected sheet???
    By kzahariev in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-12-2013, 08:48 AM
  6. [SOLVED] How to set a 'Date Filter' on a field in the 'Report Filter' section of a Pivot Table?
    By Rhino_dance in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 03-28-2013, 11:01 AM
  7. Controlling multiple pivot table filters with one filter.
    By adoepker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-28-2011, 03:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1