+ Reply to Thread
Results 1 to 13 of 13

Adding text to pivot table values

  1. 01-12-2018, 07:35 PM #1
    jlt199
    jlt199 is offline
    Forum Contributor
    Join Date
    03-19-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2013, Office 365 Pro Plus
    Posts
    182

    Adding text to pivot table values

    I have a dataset, I've created a smaller, simpler, sample dataset to illustrate my question. But the attachment button appears to not be working, I'll persevere with pictures

    Capture.JPG

    What I want to achieve is this:

    Capture2.JPG

    The problem is that I have PowerPivot and can add measures (in this case ToolType:=FIRSTNONBLANK(Table1[type],"") ), but my friend who I'm helping out does not. Also, ideally, instead of FIRSTNONBLANK it should be the most frequently occuring.

    Is there a way I can reproduce this table without using PowerPivot? I've tried this:

    Capture3.JPG

    Which is a Pivot table with a vlookup next to it. But I was really hoping hoping for a more elegant, integrated solution. He is adamant he doesn't want this (or a variant thereof):

    Capture4.JPG

    Any ideas please?
    Last edited by jlt199; 01-15-2018 at 11:09 AM.
    Reply With QuoteRegister To Reply
  2. 01-12-2018, 07:50 PM #2
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Adding text to pivot table values

    The order of columns must be like on the second picture?
    Reply With QuoteRegister To Reply
  3. 01-12-2018, 07:52 PM #3
    jlt199
    jlt199 is offline
    Forum Contributor
    Join Date
    03-19-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2013, Office 365 Pro Plus
    Posts
    182

    Re: Adding text to pivot table values

    Quote Originally Posted by sandy666 View Post
    The order of columns must be like on the second picture?
    The last two columns can be switched, but otherwise yes please
    Reply With QuoteRegister To Reply
  4. 01-12-2018, 07:58 PM #4
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Adding text to pivot table values

    something like this one? (but which one)

    pt.jpg
    Reply With QuoteRegister To Reply
  5. 01-12-2018, 08:01 PM #5
    jlt199
    jlt199 is offline
    Forum Contributor
    Join Date
    03-19-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2013, Office 365 Pro Plus
    Posts
    182

    Re: Adding text to pivot table values

    Either would work great, but I think maybe 1. How did you do it?
    Reply With QuoteRegister To Reply
  6. 01-12-2018, 08:05 PM #6
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Adding text to pivot table values

    PivotTable from source table
    • drag serial to the row area
    • drag type to the row area
    • drag again serial to the values area (change from sum to count if necessary)
    • from PivotTableTools - Design change ReportLayout to Tabular Form (turn off subtotals)
    Reply With QuoteRegister To Reply
  7. 01-12-2018, 10:48 PM #7
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Adding text to pivot table values

    If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
    then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    If you did it already - ignore it.
    Thank you.
    Reply With QuoteRegister To Reply
  8. 01-15-2018, 11:09 AM #8
    jlt199
    jlt199 is offline
    Forum Contributor
    Join Date
    03-19-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2013, Office 365 Pro Plus
    Posts
    182

    Re: Adding text to pivot table values

    Thank you, that did it! :D Sorry for the delayed response
    Reply With QuoteRegister To Reply
  9. 01-15-2018, 11:44 AM #9
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Adding text to pivot table values

    No problem
    Thanks for rep
    Reply With QuoteRegister To Reply
  10. 01-15-2018, 07:19 PM #10
    jlt199
    jlt199 is offline
    Forum Contributor
    Join Date
    03-19-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2013, Office 365 Pro Plus
    Posts
    182

    Re: Adding text to pivot table values

    Just out of interest - because it's killing me trying to figure it out! How did you do number 2?

    Thanks
    Reply With QuoteRegister To Reply
  11. 01-15-2018, 07:33 PM #11
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Adding text to pivot table values

    Quote Originally Posted by jlt199 View Post
    Just out of interest - because it's killing me trying to figure it out! How did you do number 2?
    PowerQuery (another name: Get&Transform)
    Reply With QuoteRegister To Reply
  12. 01-15-2018, 07:46 PM #12
    jlt199
    jlt199 is offline
    Forum Contributor
    Join Date
    03-19-2010
    Location
    Calgary, Canada
    MS-Off Ver
    Office 2013, Office 365 Pro Plus
    Posts
    182

    Re: Adding text to pivot table values

    Ah, ok. I don't think he has that

    Thanks
    Reply With QuoteRegister To Reply
  13. 01-15-2018, 07:49 PM #13
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Adding text to pivot table values

    PowerQuery is a free add-in from MS site for Ex2010/2013 Pro Plus and built-in in Ex2016/365

    No prob, have a nice day
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Adding field to Sum of Values area in pivot table
    By cs0rpc in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-08-2017, 01:52 PM
  2. Pivot Table, Multiple Columns with Text Values, Count is Wrong when adding multiple column
    By jjsharep in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 08-19-2015, 07:46 PM
  3. Adding values through IF function in pivot table
    By viber52 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-07-2015, 07:23 PM
  4. Pivot Table & Adding free text column
    By oboegal21 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2015, 02:38 PM
  5. Pivot Table - Adding Rows with Zero Values
    By burncat in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 05-31-2014, 11:08 AM
  6. Text Values in/from Pivot Table
    By Celticguy in forum Excel General
    Replies: 0
    Last Post: 09-05-2012, 10:39 AM
  7. [SOLVED] Match row that contains text text string; list associated values from pivot table
    By ACurtis802 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-29-2009, 07:50 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1