Doing the Impossible with a Pivot Table: Showing All Combinations of Data

[Scotty81]

Hello,

I'm trying to do something which is more of an outer-join (in database terms) rather than an Excel pivot. Essentially, I have a list of companies and products that they buy. I'd like to create a pivot table to show me the companies which aren't buying our list of products. Please note that this is different than specifying the "show values with no data" in the pivot table. Here's a simple example:

Raw Data:
Company Products Bought # of Units
Company A Product 1 5
Company A Product 2 6
Company B Product 2 3
Company B Product 3 2

So, above, Company A isn't buying any of Product 3, and Company B isn't buying any of Product 1. But, also note that these combinations are NOT listed in the above table. If they were, then the # of units bought would be zero and I could just filter on the products that have zero units bought.

So, I'd like the pivot table to show
Company A Product 3
Company B Product 1

Although I've worked with pivot tables before, I've never had a use case like this until now. Ideally, I'd like to keep the solution in Excel, and I'd like to avoid a VBA solution. So my question is: can this be done without too much heavy lifting? Excel version = 2013.

Thanks in advance.

Regards,
Scotty81

 

[Asala42]

Well the natural pivot view would look like this:
1) "Company" in Rows (bottom left box)
2) "Products" in Columns (upper right box)
3) Sum of Units in data (lower right box)

This would give you a quick view of your full data set, including company/product combinations where units sold are 0.