Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Pivot Table Question

  1. #1

    Pivot Table Question

    Register for a FREE account, and/
    or Log in to avoid these ads!


    I was hoping someone could help me build a pivot table that would tell me how much headcount we have in a Practice Group based on an applicant's application status. So, for example, in the Raw Data tab Person 1 wants to be in two practice groups, so he would be a .5 headcount in one group and .5 headcount in a second group. Person 2 wants to be only in one group so he is 1 headcount in that practice group.

    What I am currently doing in the School_Practice Group tab is creating one pivot table to do a head count for practice group preference 1 and a second pivot table for practice group preference 2 and then adding those value together on a 3rd calculated table.

    Is there anyway to do this in just one pivot table? Right now this is simple but if slice this data by say: Practice Group by Law Schoo (vertical) and current status (horizontal) this will get very difficult.

    Thanks for your help!
    Attached Files Attached Files

  2. #2
    Could you just extend the pivot table source to include Raw Data column H, with a column heading, then you could pivot the total.

  3. #3
    You could just add a Calculated field with a formula of =PG1+PG2, and use that field alone

  4. #4
    Hi Bob,

    Unfortunately I can't do this, the biggest problem is the Practice Group Preference 1 and Preference 2 columns along with the PGP1 and PGP2 headcount columns. With the way my data is currently laid out the pivot tables create subsections and separate columns which is right but all the data should be combined. I would really like my final data too look like the 3rd table in the second tab but if I do it any other way it looks like the table in the 3rd tab I just created (New), I think I did what you mentioned. Thanks for your help again!
    Attached Files Attached Files

  5. #5
    I don't really understand your reason, but no matter, here is an example using a calculated field
    Attached Files Attached Files

  6. #6
    Here is what I am trying to do: every applicant has an current status and that candidate can you also pick the Practice Group they have an interest in. That being said, if a person has an interest in two Practice Groups they are a .5 headcount in one practice group and another .5 in the second practice group. Or if they have an interest in only ONE practice group they are a 1 headcount in that Practice Group. On a sit note, the raw list of Practice Group Preference 1 is the same as Practice Group Preference 2.

    At the end, what I am trying to see is how much headcount we have in each practice group (vertical) per current application status (horizontal). So for example, for Litigation Genera here is how the number should be summed per current status:

    Callback Declined = 3
    Callback Extended = 5
    Callback Hold = 1.5
    Callback Scheduled = 31.5
    Offer Under Discussion = 3

    Does this make more sense?

  7. #7
    I think you will struggle to do that in the pivot if it is even possible.

    Can we add extra columns to the Raw Data with calculations and work it out there?

  8. #8
    I am open to any other method, the content in the Raw Data is my export from our recruiting system. Is there anyway to copy what is in the Raw Data tab to another tab and then add the new column? If not, i can live with adding a new column at the end of the data in the Raw Data tab.

  9. #9
    Took some effort, but I think I've got it
    Attached Files Attached Files

  10. #10
    THANK YOU SO MUCH! for helping me out with this, I think this is exactly what I need.

    One last question, If i want to see by Law School, and within each laws what practice group have headcount and keep Current Status as my column heading should i add another columns Practice Group Preference call Law School and then use this formula: =IF(COUNTIFS($C:$C,C2,$D:$D,B2)=0,B2,D2) ?

    I have attached the example.
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts