• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Table or something else?

estillbham

New Member
I have employment data from a government employer with about 50 work sites. At each work site, there is a "Boss," sometimes an "assistant Boss," and several "Workers" (in the category I am analyzing). I have five years of data -- one year per sheet in each workbook.

In the Worker workbook, I have 200+ workers on each sheet. In the Boss workbook, I have 160 or so rows (remember, 1 or 2 rows per work site) per sheet. The work sites are designated by a number which can be used to match works ite data from one workbook to the other.

I want to analyze these data for the differences in the racial composition of the workers and the Boss/asst -- comparing the various work sites.

Should I create a Pivot Table of some sort?

Or should I figure out a way to break out the data from the existing tables so that I have one sheet per work site and all the data for the Bosses and Workers at each site on one sheet?

Your help will be appreciated.
 
Depends on your data structure. In general I'd use PowerQuery and/or MS Query to load data to data model and use PivotTable to summarize data.

After single PivotTable has been set up, just copy and paste as needed. Thus, creating multiple pivots using same cache. Then adjust fields as needed. Creating Pivot Chart when appropriate.

That way, you can easily control all Tables and charts via Slicer connection.
 
I have attached a sample sheet with the payroll accounting data for the Workers. The Race Code and the Cost Center (the location) are the important ones. There is another sheet with almost the same structure, except that it has data for the Bosses.

An obvious solution I had overlooked is to put the data for Workers and Bosses on the same table, then sort by Cost Center
 

Attachments

  • Sample data for Pivot Table or something else.xlsx
    39.7 KB · Views: 4
Looking at your data, I'd definitely go with loading it to data model and pivot.

I'd recommend adding another column (ex: Type) that tracks Worker/Boss for each line item and then append all table into single data model.
 
Dear estillbham, While going through your sheet, i think column B,C,D is a worker name : Last Name / First Name / Middle Name respective. But where is "Boss" name?
Secondly, you have prepared separat yearly sheets, my opnion is instead of that, kindly maintain all data in single sheet with addion one column "Financial Year" & mentioned particular FY in that column i.e. 1617.
 
AVK, The "Boss" data is on similar spreadsheets with the same format. I only sent one sample spreadsheets.

Thanks to everyone for the good suggestions.
 
Back
Top