I'm using Excel pivot tables to create a report that will be shared with others. I don't have any VBA or PowerQuery or PowerBI or coding experience, but am decent with Pivot Tables.
My source data for the pivot table is from an Excel file like this (fake example for privacy):
|Project #||Task||Person Who Performed Task||Person is internal employee?|
I want to be able to answer the following question:
For projects that had a task of "paint car" done by someone who was NOT an internal employee:
- How many of these projects had the task of "quality control" performed as well?
- With the table above, the answer should be "1 project" (project #2)
- How many of these projects did NOT have the task of "quality control" performed as well?
- With the table above, the answer should be "1 project" (project #3)
- List the project numbers that meet the criteria for question #2 (above).
- With the table above, the answer should be "project #3"
The point is to find out which projects have the task of "paint job" being done externally (by a contractor, not an internal employee), and yet are not having any quality control applied, as this represents a quality risk for the business - we aren't sure how good our external contractors are.
Using the table above, is there any way to answer those questions easily with a pivot table? Obviously my data is much more voluminous so I want a pivot table method.
I've tried messing around with stuff in the pivot table but Excel seems to get messed up by the fact that the Project Number never has the task of "Paint car" and "Quality control" in the same row in the data - when I try this, it always says "0" for the "Count of Project #".