Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    249
    Thanks
    0
    Thanked 0 Times in 0 Posts

    creating a pivot table field for multiple information

    Hello-I have an excel spreadsheet with over 10,000 rows of information. In the rows I have the following columns: Name, position title, salary. I create a pivot table by using Name as one row and position title in the rows and salary as values. I would like to have the pivot table only display the names that have multiple titles. What is the best way to do this?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    316
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Are your employees with more than one job reported as more than one row? Or do they appear in a second column?
    If your list reports two rows for an employee with two jobs, then you can add a column to the data (I like to insert to the left of the range). Then insert a column title like "DualRated" (in cell A1 in my case) and this formula =COUNTIF($B$2:$B$154,B2), where all the EmplID numbers are in col B. The formula counts the number of times that an EmplID occurs in the list (you'll need to adjust the range). Then include the new column in the pivot table range and use the new field "DualRated" as the ReportFilter in your pivot table. Click on the drop-down filter button and select 2 (or all occurrances of more than 1).

  3. #3
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    249
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Every employee has a new row with information if they change job titles. Basically, what I want to do is to have the pivot table display only the names that have multiple titles and not the ones that only have one.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You could add a new column with a formula like (assuming Col a is names, B is titles):
    =COUNTIF($A$2:$A$10000,A2)<>SUMPRODUCT(($A$2:$A$10 000=A2)*($B$2:$B$10000=B2))

    This will give TRUE if the number of cells of a name does not match the number of name/title combinations, False if they are the same. You can give this field a name and use it in the pivot as a "report filter" / "page field". If True is selected only the ones with multiple title will be displayed.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    316
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Good. Give my suggestion a try. By using the COUNTIF formula you will count any employee that appears more than once in your list.

    Remember to use the new column header (I used DualRated) as your Report Filter and change the filter to select 2 or more.

    Columns in my sample sheet are Last Name, First Name, PositionID, PositionTitle. My sample pivot sorts the results by the first three fields and displays every name twice, showing the first PositionID (numerically) then the second.

  6. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 Posts
    The simplest way is probably not to use a pivot table at all.
    Sort the data by Name and by Position.
    Then use a simple formula in a spare column to check whether the entry in the Name column is the same as the previous record entry or the same as the Next record entry (e.g. if you have headers in row 1, then in first spare column, say [D], put =OR(a2=a1,a2=a3) in cell [D2]. (This formula asks whether the name in [A2] is the same as the previous entry or same as next entry). Copy this formula down to all the records (quickest way to do this is to put the cellpointer on the formula cell, and then carefully double-click the little black square in the bottom-right corner of this cell).
    You can then filter on this column [D] to show all the records where the name is repeated.
    Note that this will display persons which have multiple titles (but they could be the same title, with a different salary).
    If you specifically want only those records where the name is the same AND the job titles are definitely different, then a slightly different formula could be used to check for this.

    zeddy

  7. #7
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    249
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I would like to do the have a formula or pivot table that displays only information where the name is the same, but the titles have change. This way I can tell who got promoted or not. I would like to see both rows of information to see what their old position was and what their new position is.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    I would like to do the have a formula or pivot table that displays only information where the name is the same, but the titles have change
    The method I list above should do that, even if there are multiple names. Arcturus16a's method will work if the names are only in the list once with any given title.

    Steve

Posting Permissions

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