Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Oct 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table (2003)

    I have an upcoming physical inventory, where we generate count books via Excel. In trying to improve this process, we discovered that if we could create a pivot table that would provide us just a list of part number (along with their corresponding bin locations and tag #) that have more than 1 bin location on a single report we would save a ton of time.

    I have attached an example file. On this file there is a list of 50 part numbers, several of them appear more than once due to them having multiple bin locations. I have created a pivot table on a separate tab, and applied arrows indicating the duplicate part numbers that have multiple bin locations. What I am attempting to do, and am hitting a dead end on, is a way to extract only the product that have the multiple bin locations and disregard the products that have a single location.

    Any help/ideas you could provide would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table (2003)

    You could add a column that displays the number of times the product shows up on the list. In C2 enter =COUNTIF($B:$B,B2) and then fill down. Use this new field as an additional column in your pivot table. You can then use the filtering option and uncheck the 1.

    I've updated your sheet to show this. the PT is on the same sheet as the data.
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table (2003)

    You can move the bin field to the data area. This will give you a count of bins per part.
    You cannot filter a pivot table directly, but you can copy it to a different location and use AutoFilter to select the parts with multiple bins.
    See attached version.
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table (2003)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>You can filter a pivot table if you know the "trick"

    In your example, select C4 (the column to the right of the pivot lablel) and then turn on the autofilter (data - filter) [of course this assume the current filter has been removed]. Excel will put the autofilter on the pivot table.

    Steve

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table (2003)

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> I think you replied to the wrong post....

    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table (2003)

    Thanks, I didn't know that.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table (2003)

    Corrected, thanks. It was a glitch of the Lounge server - I posted a reply in the Access forum but it "hung". I then posted a reply to you, and the server used the text from the other reply instead of the text I typed. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  8. #8
    Lounger
    Join Date
    Oct 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table (2003)

    Thank you Mbarron, HansV (you are always a huge help), and thank you Sdckapr- I did not know the autofilter trick either (a bonus for the day).

    Thanks again,

Posting Permissions

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