+ Reply to Thread
Results 1 to 4 of 4

Two Filter Items - How to combine or create OR statement?

  1. #1
    Registered User
    Join Date
    12-17-2010
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2003
    Posts
    5

    Two Filter Items - How to combine or create OR statement?

    I am attempting to create a PivotTable where my team can select their name as the report filter to view their activity. In the data, there are two fields where a team member could indicate that they're working on an account (two team members per account). Is there a way to create the PivotTable (or create another column in the data which the pivot can pull from) so that they only have to select their name once, regardless if it's in the first or second column? Simplified sample spreadsheet is attached.

    Thanks!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-17-2010
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013, 2016, 2019, 365
    Posts
    294

    Re: Two Filter Items - How to combine or create OR statement?

    I've had a bit of a play with this one, and have come up with two alternative approaches.

    The first (on 'With Pivot' sheet) means a 2-step (or sometimes 3-step) process of selecting the person then refreshing the pivot table. You'll see I've added a new column into the data to find which resource is chosen (selected in named range 'chosen'), then that's used in the pivot table. Not ideal, because I'd rather have a 1-step process. So...

    Second approach is on the 'With formula' sheet. Added to the data is a left-hand column which numbers each line on which the chosen name appears (selected in named range 'chosen2'). Then you simply use a VLOOKUP on that column to extract each line, putting a dash in if there's no data. You need to copy the formula down as far as the maximum number of records you're likely to get. Then all you need to do is select the person, and you get the data - 1-step process.

    On both sheets I've used data validation, with the validation list on its own sheet (can only be achieved using named ranges...).

    Hope that helps.
    Attached Files Attached Files

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Two Filter Items - How to combine or create OR statement?

    Below is just an extension of @ooth first suggestion w/use of VBA.

    If you wanted to you could add a Change event to the sheet object to catch the DV selection being modified (I1) to in turn update the PT

    Please Login or Register  to view this content.
    obviously the above brings it with Macro requirements for end users.

  4. #4
    Registered User
    Join Date
    12-17-2010
    Location
    Charleston, SC
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Two Filter Items - How to combine or create OR statement?

    Thanks for the help. I'm not sure the formula one will work for my issue, but I see the logic behind it and its very helpful. I also don't think my team can handle the refreshing and not messing up the formulas, but again the logic puts me in the right direction.

    Do you know if there's any way to manipulate a pivot table by adding formulas? I don't think so, but thought I'd ask.

    Thanks so very much for your help, and happy new year!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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