Forum Discussion
Pivot tables, more than one person/unit assigned to a task.
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?
| # | Task | Unit | Unit2 | Task Owners |
| 1 | Task 1 | Policy | Operations | John, Linda |
| 2 | Task 2 | Operations | Compliance | Linda, Mary |
| 3 | Task 3 | Compliance | Mary | |
| 4 | Task 4 | Enforcement | Lydia, Steve | |
| 5 | Task 5 | Compliance | Mary | |
| 6 | Task 6 | Operations | Linda | |
| 7 | Task 7 | Enforcement | Compliance | Lydia, Mary |
| 8 | Task 8 | Enforcement | Policy | Lydia |
| 9 | Task 9 | Policy | John |
I would like the pivot table to look like:
| Row Labels | Count of Task |
| Compliance | 5 |
| Task 2 | 1 |
| Task 3 | 1 |
| Task 5 | 1 |
| Task 7 | 1 |
| Enforcement | 3 |
| Task 4 | 1 |
| Task 7 | 1 |
| Task 8 | 1 |
| Operations | 3 |
| Task 1 | 1 |
| Task 2 | 1 |
| Task 6 | 1 |
| Policy | 3 |
| Task 1 | 1 |
| Task 8 | 1 |
| Task 9 | 1 |
| Grand Total | 13 |
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.
