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 |
