Pivot Table Question

[mikeyredk]

Long time lurker first time poster; with all this great knowledge in the forum I had no need to create an account because I pretty much found my answers using the search button (doesn't that make u warm and fuzzy inside )

The question… is it possible in a pivot table to have the column heads do OR not an AND.

The Setup… I have four columns one is Product A Usage, Product B Usage, Product C Usage, Product D Usage. The rows are by Part Number and description. The data set within is pricing data from three different years. Supplier is the page header. Some Part Numbers are used in multiple products.

The Rub… If I put all four Products on at the same time they will all go on there but there are a lot of blanks because that part is not being used to make that product. I select no blanks and I get only one set of Product Usage data associated with Part Numbers.

The wants… To be able to see all products at once, be able to manipulate it in a way that will allow me to dig though the data to understand the price increase and where is it coming from.

 

[PaddyD]

hi - welcome to the board.

usual approach to excluding specific sub-sets of the source data from a pivot is to:

1) create extra column(s) in the source data - one for each exclusion condition you're applying
2) set up formula(s) to check for the condition(s)
3) if checking multiple exclusion conditions, set up final column as a global 'include / exclude' formula
4) put this last column in te pivot as a page field & set so as to only include the atomic records you're after.