Pivot tables, more than one person/unit assigned to a task.

Copper Contributor

Hi, I have the example table below.  How do I create a pivot table that lists the "Tasks" each Unit is responsible for?  The "Unit" could be in the column "Unit" or "Unit 2" as more than one unit may work on a task. 

I am wondering how to combine the Columns "Unit" and "Unit2" to use in my pivot table.  Or how do I create a pivot table that would list "tasks" by each "owner", for example John is responsible for Task 1, and 9. 


I am stuck when a column response is not a single answer such as when a task is completed by more than one Unit or person?


#TaskUnit Unit2Task Owners
1Task 1PolicyOperationsJohn, Linda
2Task 2OperationsComplianceLinda, Mary
3Task 3Compliance Mary
4Task 4Enforcement Lydia, Steve
5Task 5Compliance Mary
6Task 6Operations Linda
7Task 7EnforcementComplianceLydia, Mary
8Task 8EnforcementPolicyLydia
9Task 9Policy John


I would like the pivot table to look like: 

Row LabelsCount of Task
Task 21
Task 31
Task 51
Task 71
Task 41
Task 71
Task 81
Task 11
Task 21
Task 61
Task 11
Task 81
Task 91
Grand Total13



1 Reply

@SueWhitt You would have to restructure the data so that you have only one column for the Unit, thus creating multiple rows for tasks that belong to multiple units. In the attached example I did it manually, but in real life you could do it with Power Query (unpivot) for example.