Using the Pivot Table to Identify who has a combination of jobs…

Use the Payroll Assignments Spreadsheet. We are interested in determining who has BOTH a Bus assignment and a Child Nutrition assignment.

Use the “Select Job Codes to Include” option on the settings tab.

 

Enter a “Y” beside all of the BUS and CN job codes and refresh.

 

Click on the Payroll Pivot Table at the bottom of the spreadsheet.

 

The Original View on the pivot table contains many fields that we will not need. 

 

Drag everything OFF of the table except Job Code, Full Name and Position Count.

Click on the header for the Job Code. Click and drag the Job Code ON TOP OF the Position Count. This will spread the Job Codes across the top.

 


 

Create New Headings for BUS? And CN?

We will use these columns to determine if a person has a Bus assignment or a CN assignment. We will create a formula in each column to calculate this.

 

Position your cursor in the row below the Bus? Heading. Click in the formula bar.

The formula for the Bus? column is shown here. The formula adds up all of the position counts for the BUS positions (which in our case are in columns B through E). If the sum is greater than 0, then we flag this person as having a BUS assignment.

 

 

 

 

Click on the cell with the formula. Click on the DOT located at the bottom right hand side of the cell. Drag it down to the bottom of the data in the pivot table. This will copy the formula down to the other rows. Notice that the formula adjusts for the row numbers.

 

Do the same under the CN? Column and copy the formula down

Highlight the headings for the Bus? and CN? Columns.

 

Click on Data, Filter. This will turn the filters on for the columns.

 

To find users who now have BOTH CN and BUS assignments, filter both the BUS? and CN? Columns for Y