+ Reply to Thread
Results 1 to 13 of 13

Want to find out # of people with job title when name/title is repeated multiple times

  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    Vancouver
    MS-Off Ver
    Excel 2010
    Posts
    5

    Want to find out # of people with job title when name/title is repeated multiple times

    Hello,

    I have a list of employees and all of the pay types that have been paid to them over the year, and their job title.

    i.e.
    Col:A; Col:B; Col:C
    Joe s Mechanic Reg hrly
    Joe s Mechanic OT 1.5x
    Joe s Mechanic OT 2x
    Joe s Mechanic Stat

    Kya m Mechanic Reg hrly
    Kya m Mechanic OT 1.5x
    Kya m Mechanic OT 2x
    Kya m Mechanic Stat

    Susie N Operator Reg Hrly
    Susie N Operator OT 1.5x
    Susie N Operator OT 2x
    Susie N Operator Stat

    John B Electrician Reg Hrly
    John B Electrician OT 1.5x
    John B Electrician OT 2x
    John B Electrician Stat
    John B Electrician Call-in
    John B Electrician Banked $ pd


    I have a pivot table summarizing the totals per pay type, by job title, and that is working fine. What I need to do is calculate how many individuals there are per title.

    i.e.

    Mechanic - 2
    Operator - 1
    Electrician - 1
    Last edited by HRLISA; 08-10-2012 at 05:13 PM.

  2. #2
    Forum Contributor
    Join Date
    01-09-2012
    Location
    Rochester Hills, United States
    MS-Off Ver
    Excel 2010
    Posts
    161

    Re: Want to find out # of people with job title when name/title is repeated multiple times

    Could you upload an example of your workbook (pivot table and all)?
    Nothing is absolute - a paradox in itself.

    Indirect Dynamic Data Validation (scroll to the bottom of the page)

  3. #3
    Registered User
    Join Date
    08-10-2012
    Location
    Vancouver
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Want to find out # of people with job title when name/title is repeated multiple times

    Unfortunately since this is a work document I cannot upload it. Is there a specific feature/item you wanted to view? I can provide further examples if you would like. Essentially there is 1 row for each pay code type (reg hrly, OT, stat etc..) for each person, and on each line there is also the employee's name and job title.

    My example above shows the employee name, title, and pay code type.


    This results in the name and job title being repeated 8-12 times(because of the number of possible pay code types). There are 15 possible job titles, and 97 employees - 1040 rows total.

    My pivot table has the Pay Code Type as the row label, the column labels are the job titles, and the values are the dollar figures actually paid and the hours worked.

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Want to find out # of people with job title when name/title is repeated multiple times

    With your data in A1:C22 (Row_1 has column headings)
    and
    F1: a job title to match....Mechanic
    F2: another job title.......Operator
    etc

    This regular formula returns the count of unique names for that title
    Please Login or Register  to view this content.
    Copy that formula down as far as you need.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Want to find out # of people with job title when name/title is repeated multiple times

    if you go into your PT options, you can change the "sum" to "count". not sure if this is something you could use?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Want to find out # of people with job title when name/title is repeated multiple times

    Please reply if this pivot table is what you're up to.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  7. #7
    Registered User
    Join Date
    08-10-2012
    Location
    Vancouver
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Want to find out # of people with job title when name/title is repeated multiple times

    PERFECT!!!! Thank you so much!

    Now I just wish I knew what all of that was doing so I could replicate it in the future and actually understand why it worked.

    My beautiful results:

    Front End Loader Opr - 3
    Machinist w Steam Ticket - 1
    Machinist with TQ - 1
    Machinist wTQ w Steam Ticket - 1
    Machinist wTQ wSteamTicket wFA - 1
    Machinist wTQ wStmTicket wLead - 1
    Mechanic Lead Person - 1
    Mechanic w TQ - 3
    Mixer Plant Opr FA - 1
    Mixer Plant Opr Lead FA - 5
    Truck Driver 5 Axle - 69
    Truck Driver 5 Axle Lead - 3
    Truck Driver 6 Axle - 1
    Truck Driver 7 Axle - 5

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Want to find out # of people with job title when name/title is repeated multiple times

    @HRLisa

    Please add in your reply to whom your responding.

  9. #9
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Want to find out # of people with job title when name/title is repeated multiple times

    Quote Originally Posted by HRLISA View Post
    PERFECT!!!! Thank you so much!

    Now I just wish I knew what all of that was doing so I could replicate it in the future and actually understand why it worked.

    My beautiful results:

    Front End Loader Opr - 3
    Machinist w Steam Ticket - 1
    Machinist with TQ - 1
    Machinist wTQ w Steam Ticket - 1
    Machinist wTQ wSteamTicket wFA - 1
    Machinist wTQ wStmTicket wLead - 1
    Mechanic Lead Person - 1
    Mechanic w TQ - 3
    Mixer Plant Opr FA - 1
    Mixer Plant Opr Lead FA - 5
    Truck Driver 5 Axle - 69
    Truck Driver 5 Axle Lead - 3
    Truck Driver 6 Axle - 1
    Truck Driver 7 Axle - 5
    Glad to help...If you have questions about the formula structure, just ask.

  10. #10
    Registered User
    Join Date
    08-10-2012
    Location
    Vancouver
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Want to find out # of people with job title when name/title is repeated multiple times

    To Oeldere:

    Not quite, attached below is a sample of the pivot table, but I have removed the data and just threw in a couple fake numbers.
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Want to find out # of people with job title when name/title is repeated multiple times

    If you post an example of the data with non confidentional information, we can look if this is possible.

    Please Login or Register  to view this content.
    To whom you're replying this, it looks to me you find an solution.

  12. #12
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Want to find out # of people with job title when name/title is repeated multiple times

    In reply to your PM...
    Regarding this formula:
    =SUMPRODUCT((MATCH($A$2:$A$22&$B$2:$B$22&"_",$A$2:$A$22&$B$2:$B$22&"_",0)=(ROW($A$2:$A$22)-ROW($A$1)))*($B$2:$B$22=F1))

    This section: MATCH($A$2:$A$22&$B$2:$B$22&"_",$A$2:$A$22&$B$2:$B$22&"_",0)
    concatenates each of the Col_A and Col_B values and appends an underscore character....example for Row_2: "Joe sMechanic_"
    and tries to find that combination in the list of all concatenated Col_A and Col_B values.

    The result is the position in the list that the match was first found. So for "Joe sMechanic_" the MATCH function always returns 1 for all of those combinations.

    The result of the MATCH function for each cell is compared to each cells row number (minus 1, because the list starts on Row_2)

    So for the "Joe sMechanic_" section the results would be:
    1 compared to 1 (row 2 minus 1)
    1 compared to 2 (row 3 minus 1)
    1 compared to 3 (etc)
    1 compared to 4

    Only the first one is a match for the row

    This whole section:
    ((MATCH($A$2:$A$22&$B$2:$B$22&"_",$A$2:$A$22&$B$2:$B$22&"_",0)=(ROW($A$2:$A$22)-ROW($A$1)))
    returns a series of TRUE/FALSE values...which become 1's and 0's, respectively when used mathematically.
    Again, using our buddy Joe, the results would be
    (1 = 1) = 1
    (1 = 2) = 0
    (1 = 3) = 0
    (1 = 4) = 0

    The final section tests if the Title matches the one we're looking for.

    So, for each matched item that results in 1...If the Title matches our title, the result is 1.
    Everything else resolves to 0

    The SUMPRODUCT sums the results...which are the count of unique items.

    I hope that helps.
    Last edited by Ron Coderre; 08-10-2012 at 07:01 PM. Reason: Corrected the typos and grammatical errors

  13. #13
    Registered User
    Join Date
    08-10-2012
    Location
    Vancouver
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Want to find out # of people with job title when name/title is repeated multiple times

    Quote Originally Posted by Ron Coderre View Post
    In reply to your PM...
    Regarding this formula:
    =SUMPRODUCT((MATCH($A$2:$A$22&$B$2:$B$22&"_",$A$2:$A$22&$B$2:$B$22&"_",0)=(ROW($A$2:$A$22)-ROW($A$1)))*($B$2:$B$22=F1))

    This section: MATCH($A$2:$A$22&$B$2:$B$22&"_",$A$2:$A$22&$B$2:$B$22&"_",0)
    concatenates each Col_A and Col_B values and appends and underscore character....example for Row_2: "Joe sMechanic_"
    and tries to find that combination in the list of all concatenated Col_A and Col_B values.

    The result is the position in the list that the match was first found. So for "Joe sMechanic_" the MATCH function always returns 1 for all of those combinations.

    The results of the MATCH function are compared to each cells row number (minus 1, because the list starts on Row_2)

    So for the "Joe sMechanic_" section the results would be:
    1 compared to 1
    1 compared to 2
    1 compared to 3
    1 compared to 4

    Only the first one is a match for the row

    This whole section:
    ((MATCH($A$2:$A$22&$B$2:$B$22&"_",$A$2:$A$22&$B$2:$B$22&"_",0)=(ROW($A$2:$A$22)-ROW($A$1)))
    returns a series of TRUE/FALSE values...which become 1's and 0's, respectively when used mathematically.
    Again, using our buddy Joe, the results would be
    (1 = 1) = 1
    (1 = 2) = 0
    (1 = 3) = 0
    (1 = 4) = 0

    The final section test if the Title matches the one we're looking for.

    So, for each matched item that results in 1...If the Title matches our title, the result is 1.
    Everything else resolves to 0

    The SUMPRODUCT sums the results...which are the count of unique items.

    I hope that helps.

    Wow - fantastic thanks. Definitely keeping for future use.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1