+ Reply to Thread
Results 1 to 15 of 15

Pivot Table Filtering

  1. #1
    Registered User
    Join Date
    09-01-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Pivot Table Filtering

    Hi

    My data has the following data format:

    Name Department Payment
    Bob Cutting $10
    Bob Splicing $ 5
    Jill Cutting $ 7
    Jill Finishing $ 4
    Peter Painting $ 2

    I want to construct a pivot table that will allow me to see the total compensation paid to each employee IF they worked in a particular department.

    For example, if I select Cutting, I want to see something like this:

    Bob Cutting $10
    Bob Splicing $ 5
    Bob $15

    Jill Cutting $ 7
    Jill Finishing $4
    Jill $11

    Note that Peter is excluded since he did not work in Cutting at all.

    I can't figure out how to selectively filter so that I only see the employees who worked in the Cutting department, but the subtotals include work they did in other departments.

    Thanks!
    e
    Last edited by mrethan2012; 09-01-2014 at 09:48 PM.

  2. #2
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13/16/19
    Posts
    1,067

    Re: Pivot Table Filtering

    Hi mrethan2012,

    Have look at the attached file.

    It always helps by the way if you put your example data into an Excel file.

    Cheers
    Attached Files Attached Files
    Remember you are unique, like everyone else

  3. #3
    Registered User
    Join Date
    09-01-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Re: Pivot Table Filtering

    Thank you for your prompt response. However, the attached spreadsheet does not accomplish what i described.

    What I would like is for all the payments to Bob and Jill be identified and subtotaled BECAUSE they both were involved in the cutting department process.

    Thanks
    ethan

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Pivot Table Filtering

    in that case it is up to you, to make a decent example, without confidential information.

    please also add the desired (expected) result, manualy, in your sheet.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Registered User
    Join Date
    09-01-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Re: Pivot Table Filtering

    Thank you for your help. I have attached a spreadsheet with my desired result.

    Thanks!
    e
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-02-2014
    Location
    Singapore
    MS-Off Ver
    Office 2013
    Posts
    26

    Re: Pivot Table Filtering

    Compo_Pivot (2).xlsx

    Hi, I've added a filter which should achieve what you want.
    Check out my gig@ http://www.fiverr.com/s/5syr1l

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Pivot Table Filtering

    wihtout helpcells
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-01-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Re: Pivot Table Filtering

    Hi

    Thank you for your help, but the spreadsheet you both posted still does not perform what I specified. Please see the attached spreadsheet.

    The filtered spreadsheet should list Bob and Jill, and their total payments for all departments since they worked in the cutting department. However, your solutions only show the compensation paid to Bob and Jill for work they performed in the cutting department (but fails to include compensation they received from the splicing and finishing departments, respectively). The end result should look like this:

    Bob Cutting $10
    Bob Splicing $ 5
    Bob $15
    Jill Cutting $ 7
    Jill Finishing $4
    Jill $11

    Thanks
    e
    Attached Files Attached Files
    Last edited by mrethan2012; 09-02-2014 at 01:05 PM. Reason: Attached Spreadsheet.

  9. #9
    Registered User
    Join Date
    09-01-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Re: Pivot Table Filtering

    I will use another program to achieve this. I guess it cannot be done in Excel.

  10. #10
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Cool Re: Pivot Table Filtering

    Check if that is what you were trying to achieve..
    To make this automatic, I guess we have to use VBA..
    Check the attached file..
    Attached Files Attached Files
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  11. #11
    Registered User
    Join Date
    09-01-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Re: Pivot Table Filtering

    Thank you for your reply. Hiding rows is not what I had in mind so I'll use a different program. However, I appreciate all your efforts to assist me.

    Thanks everyone!

  12. #12
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot Table Filtering

    If you have PowerPivot I think you could do it without code or helper cells, but without you'd need formulas like this (you have to refresh the pivot after changing the value in G8 but that could be automated very easily).
    Attached Files Attached Files
    Remember what the dormouse said
    Feed your head

  13. #13
    Registered User
    Join Date
    09-01-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    7

    Re: Pivot Table Filtering

    Romperstomper -- WONDERFUL....thanks so much. I had given up but you found a very helpful solution for me.

    Thanks

  14. #14
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Pivot Table Filtering

    Glad to help.

  15. #15
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Pivot Table Filtering

    Nice Application Rompers...
    It was new for me..
    I am your Fan from now...

+ 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. [SOLVED] how can i sum pivot table filtering some values another table
    By moogii1102 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-19-2014, 02:17 AM
  2. [SOLVED] Filtering Data Pivot Table is Based On - Impact on Pivot Table
    By bbg22 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-17-2013, 06:34 AM
  3. Filtering a Pivot Table according to 'Future' Pivot Items
    By Eraserhead in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-27-2012, 03:25 PM
  4. Pivot Table filtering
    By drewmann in forum Excel General
    Replies: 2
    Last Post: 09-07-2011, 10:20 PM
  5. VBA Pivot Table Filtering
    By datawrecker in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-03-2010, 09:44 AM

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