Forum Discussion
Solved
Pivot Table - 2 criteria but show only one
I have a labor file with data source sorted by several criteria including Employee ID and then by Pay Code (Regular Hours, OT hours, PTO, etc.). I have a Pivot Table that pulls from that based on Labor Assignment first - A Shift, B Shift, A Shift 2nd Location, B Shift 2nd Location, ... And then pulls the Employee Name and total regular hours (Pivot Table has a manual filter for that Pay Code).
My problem is I have two employees with the same name, but different Employee ID's (actually have a few of these "duplicates") who work under the same Labor Assignment. I want the table to sort them out by Employee ID, but only show the name in the results.
for example:
A Shift - Employee ID 1234 - John Smith 8 hours
A Shift - Employee ID 2345 - John Smith 8.5 hours
I want the table to output:
A Shift
John Smith 8 hours
John Smith 8.5 hours
Right now it is showing
John Smith 16.5 hours
or
John Smith 1234 8 hours
John Smith 2345 8.5 hours
Is it possible to sort on a field but not show it in the table? Perhaps, do I need to do something with the source data as far as sorting or adding a column?
Thanks in advance.
p.s. - don't want / can't use Power Query due to constraints at work.
Hi RandomPanda
in a regular pivot table you can only sort by fields which are visible in the pivot table. In Power Pivot / datamodel there is an option to sort a column by another column.
But even then this would not help in your case, because since the name of the employee is the same for all ID's, it will group the result by the name. So the output in the pivot table will be just one line with the name and the sum of all hours, as you experienced already.
The only solution I can think of is keeping the ID in the pivot table and just hide the ID column (I know this is not an ideal solution).
3 Replies
Hi RandomPanda
in a regular pivot table you can only sort by fields which are visible in the pivot table. In Power Pivot / datamodel there is an option to sort a column by another column.
But even then this would not help in your case, because since the name of the employee is the same for all ID's, it will group the result by the name. So the output in the pivot table will be just one line with the name and the sum of all hours, as you experienced already.
The only solution I can think of is keeping the ID in the pivot table and just hide the ID column (I know this is not an ideal solution).
- Thanks, Martin -
This was pretty much the answer I was expecting. Even hiding the ID column, if I sort by Name first, it consolidates the duplicate Names. So, I'm back to where I started. For now, I'm waiting to hear from HR and those to whom I send the report if sending only employee ID's or both ID and Name (sorted by EID first) is a viable solution.
It sounds like we may need to update the employee records to avoid duplicates in the first place, but I don't know how that will work (I don't maintain that system). Plus, we have over 50k employees company-wide and a lot of possible duplicates. So, that would be a lot of work...again, for someone else.Hi RandomPanda
one additional hint concerning the hidden column and the consolidation of the names:
You can adjust the field settings for the name field, so every name gets repeated (if this is what you really need).
Here is an example:
