How do I use a pivot table to display all projects that are missing a certain task, but only in certain circumstances?

Question

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?
1	Paint car	John	Yes
1	Quality control	Chris	Yes
2	Paint car	Matt	No
2	Quality control	Chris	Yes
3	Paint car	Wilson	No
3	Balance Tires	Jeff	No

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:

1. 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)

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)

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 #".

Answer 1

Indeed a very intersting question, but I fear that your data structure makes a PivotTable approach quiet challenging. An alternative to this would be to use a formulaic approach that allows you to answer all of your questions accordingly.

Assuming your data is stored in the range A1:D7, you can put the following LET() statement somewhere on the sheet:

=LET(
data, $A$2:$D$7,
proj,
UNIQUE(INDEX(data,,1)),
task1,
IFERROR(INDEX(data, MATCH(proj&"Paint car", INDEX(data,,1)&INDEX(data,,2), 0), 2), ""),
internal1,
IFERROR(INDEX(data, MATCH(proj&"Paint Car", INDEX(data,,1)&INDEX(data,,2), 0), 4), ""),
task2,
IFERROR(INDEX(data, MATCH(proj&"Quality control", INDEX(data,,1)&INDEX(data,,2), 0), 2), ""),
internal2,
IFERROR(INDEX(data, MATCH(proj&"Quality control", INDEX(data,,1)&INDEX(data,,2), 0), 4), ""),
criteria,
IF(internal1="yes", "internal",
IF((internal1="no")*(internal2="yes"), "checked", "to be checked")),
HSTACK(proj, task1, internal1, task2, internal2, criteria)
)

Basically, you are specifying your data set followed by project numbers. Then you wanna extract the "Paint car" tasks and whether it is performed internally or not; the same idea for "Quality control". Based on this data, you define your criteria to answer your questions based on an IF() statement that checks whether the paint was "internally" and flags it as such. If the paint was non-internally and was checked it is flagged as "checked" and otherwise it needs "to be checked".

In theory, you could also apply all formulas in individual columns and drag it down, but the LET() statement allows you to specify everything in one go and returns an array of the output. This implies that you need to have the formula only in one cell, which makes it quite maintainable. In addition, it should be also feasible to make it more dynamic in terms of the activities if needed. If you want to return only the project # and the criteria you can simply adjust the arrays in HSTACK().

The final output looks as follows (headers are added manually):

Thus, to answer your questions:

1. Project #2 is already checked
2. Project #3 needs to be checked
3. The list of projects is automatically given by this approach, once you filter only for the projects that need to be checked

Answer 2

Don't be afraid of power query!

When I first started playing with power query, I was angry, thinking of all the times I'd needed it and had no idea it would be so easy to do ñ

Most of what you need to do can be done just by clicking around. Save a back-up of the original and remember: there's nothing you can do that you can't recover from. 🙃

First query

Highlight the table and, from "Get and Transform" section of the Data ribbon, make a query that reads the table. A new "query editor" window should appear. In the right margin of that window, change the name of that query to whatever makes sense for you.

Query the query

In the home ribbon of the query editor window, there's a button called "Manage..." and when you click it, one of the child-choices is "Reference". Click it, and you'll start a new query that uses your first query. Rename this second query to "Paint car" and then use the buttons on the right of the columns to filter this table so that it's only "Paint car" tasks.

Then, go back to your first query and do it again (Manage... Reference) and rename this third query to "Quality Control". Click the filter options on the Task column header to add a filter to only show Quality Control tasks.

Merge them

Still in the query editor window, in the list of queries on the left side, click on the "Paint car" query and then create a new query from that (using Manage... Reference), and then, somewhere in one of the ribbons, there's a button to merge queries. Find it, click it, and merge in the "Quality Control" query. In the dialog box that appears, click the project # column for both tables, and use full outer join.

Then click the button on the right of the "Quality control" column we just added to replace that column with columns from matching Quality control rows.

Rename your column names and you can have a table with columns of

• Project #
• Painter
• Is painter internal employee?
• Quality technician
• Is quality technician internal employee?

Play time!

Save and load to your worksheet or data model and you could pivot table from there. Another option you now have is to keep going in power query. For example, from that query you could create yet another query and filter for only paint car tasks done by non-internal employees where no quality control was done, and load that into a separate worksheet. Within power query you can pivot, merge, ... heck, you can even *un"pivot!

Once set up, change the source data, click "data - refresh all" in the Excel ribbon, and all the queries recalculate.