Pivot Table Creation

Copper Contributor

How do I create a pivot table of the data in the "data" tab to where it then created a clickable pivot table, as exampled in "example pivot table" tab. Here it will search the columns (Estimator PM Mktg Assist Acct Rep EMS), and if a persons name is in the "row", it counts as 1 towards that person.

 

Once I click on the name of the person, or number of "jobs", it opens up an organized file full of just the jobs (rows) with their name on it. 

 

I have included 2 "organized data" sheets to show you what I'm looking for. If I click on "Daniel" or "Larry" on the pivot table, it would generate those organized sheets.

2 Replies

@jlockerbie The structure of the data isn't suited to create a pivot table from. I've used Power Query to fix that. Also needed to fix the amounts as they were in fact texts and several names that looked the same were not as they had trailing spaces. For example "Daniel" and "Daniel ".

 

The attached file included a PT in the tab with your example and two extra tabs with the details created by double clicking inside the PT.

Hi @jlockerbie 

 

You can't do it with a PivotTable only because the Names you expect come from 3 columns (as I understand...): [Estimator], [PM] and [Mktg Assist] + you have names with extra spaces (i.e. Daniel)...

 

In attached file:

- Formatted your data as a Table

- Loaded that table to Power Query to:

  • Trim columns [Estimator], [PM] and [Mktg Assist]
  • Merge names as new column [Name]
  • Split [Name]s a new rows/records

- Built the PivotTable from the above query:

Sample.png

 

When you double-click on a 'Count of Job #' you get:

Untitled.png

(with the extra [Name] column created by Power Query)