Pivot Table (2003)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Thanks, I didn’t know that.

Reply | Quote

I think you replied to the wrong post….

Steve

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote