+ Reply to Thread
Results 1 to 5 of 5

Combining columns in pivot table results.

  1. #1
    Registered User
    Join Date
    02-19-2013
    Location
    West Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Combining columns in pivot table results.

    Very new to pivot tables!!! I have looked, read, and searched and to no avail I cannot seem to find the solution. So, now I turn to the experts.

    The situation:

    I have a table that shows: Column A = Date; Column B = Tag Number; Column C = Full Time Equivalent 1; Column D = Full Time Equivalent 2; additional data to the right of Column D.

    Employees have been placed in both FTE 1 and FTE 2 columns. For example, Mickey Mouse on one day is placed in FTE 1 position, while on another day Mickey Mouse is placed in the FTE 2 position.

    How can I get the number of times Mickey Mouse is in both FTE 1 and FTE 2 positions combined? Is there an easy way to do this? For example, I want to create a pivot table based on a value in Column F, but I want the total times Mickey Mouse was listed in both columns.

    Any help is greatly appreciated.
    Attached Files Attached Files
    Last edited by Mr. H2; 01-03-2017 at 10:38 AM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,067

    Re: Combining columns in pivot table results.

    Sometimes the best solution to a pivot table issue is to use helper columns in the source data. In this case, =COUNTIF([FTE 1],[@[FTE 1]])+COUNTIF([FTE 2],[@[FTE 1]]) - it counts the number of time Mickey is in FTE1 and adds it to the number of times he's in FTE2.

    A word of caution: if Goofy were to join the organization and appears only in FTE2, he's not accounted for. The formula only works if everyone in the organization appears in at lest FTE1 at least once.

    Also since the formula has the total everywhere it appears, if you want to display the value on a pivot table, use average otherwise you get the totals total. Unfortunately, this does screw up the Grand Total for the columns.

    To do this really right, you would have to normalize the data which will take VB Code. And that will await some free time tomorrow.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,067

    Re: Combining columns in pivot table results.

    Free time came. I renamed your original table from Table1 to Table_Original and added a second table, Table_Normalized.

    Usually, pivot tables are most powerful when based on normalized data. That's what this code does; it changes the layout of the data. In this case, instead of having two separate columns each with a name, we have a name and associated with the name is either FTE 1 or FTE 2.

    Running the macro converts the original table into a normalized table. The results are shown on the FTE Totals sheet.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    02-19-2013
    Location
    West Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Combining columns in pivot table results.

    Thank You! I hadn't even considered taking that approach. Your solution gave me what I needed, now I can move on with my report.

  5. #5
    Registered User
    Join Date
    02-19-2013
    Location
    West Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Combining columns in pivot table results.

    dflak,

    Thank you, for all your help.

    Mr. H2

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Pivot Table Results to Secondary Table with Horizontal Results
    By Pony08 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-07-2015, 04:18 PM
  2. Replies: 3
    Last Post: 05-24-2013, 06:24 PM
  3. get columns in pivot table results page to auto fit
    By skinpup in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 03-24-2013, 02:45 AM
  4. Combining value count from multiple columns in pivot table
    By AFsimRA in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 02-05-2013, 11:29 AM
  5. Replies: 2
    Last Post: 05-21-2012, 10:56 AM
  6. Replies: 2
    Last Post: 05-03-2012, 01:18 PM
  7. [SOLVED] Pivot Table combining multiple columns
    By Pete Petersen in forum Excel General
    Replies: 1
    Last Post: 01-13-2005, 04:06 PM

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