SOLVED

Table Summary Sheet Organization Help!

Copper Contributor

I need some help creating a summary page for a spreadsheet!

 

Here's the basic information:

- Project Names and Employee Names have been changed for privacy

- Project Names that are similar (i.e., two "Ecuador"s) are the same project, just different phases

- Each project has at least ONE employee assigned to it

- The employee has ranked each project by a priority rating (Priority 1 (50%), Priority 2 (50%), Flex Priority 1, Flex Priority 2, and FUTURE)

- Each employee has an assigned role for each project (as of right now, I haven't assigned them yet, but I have provided a list of the different roles each employee may have; feel free to randomly assign roles to employees)

 

Goal:

I would like to summarize all of this information into a summary page (open to pivot table help) where I can choose which employees to show, which projects their assigned to, the priority of the project for that employee, and their role on that project.

 

 

1 Reply
best response confirmed by YoloSpaceMuffin (Copper Contributor)
Solution

Hi @YoloSpaceMuffin 

 

with the current structure of the data table, a Pivot is not possible. So my recommendation is to load the data table into Power Query, transform it with "Unpivot" function and load it directly into a new Pivot table in the Summary sheet. There you can use slicers to select the employes.

 

In the attached file I have prepared that. You just need to refresh the pivot whenever something changes in the data table.

1 best response

Accepted Solutions
best response confirmed by YoloSpaceMuffin (Copper Contributor)
Solution

Hi @YoloSpaceMuffin 

 

with the current structure of the data table, a Pivot is not possible. So my recommendation is to load the data table into Power Query, transform it with "Unpivot" function and load it directly into a new Pivot table in the Summary sheet. There you can use slicers to select the employes.

 

In the attached file I have prepared that. You just need to refresh the pivot whenever something changes in the data table.

View solution in original post