+ Reply to Thread
Results 1 to 13 of 13

Adding text to pivot table values

  1. #1
    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.

  2. #2
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Old Country
    MS-Off Ver
    PC: Best
    Posts
    8,662

    Re: Adding text to pivot table values

    The order of columns must be like on the second picture?
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Excel can do everything but still doesn't read user's wishful thinking
    The ready made solution will not teach anyone anything
    A logical description of the problem is the basis of dialogue and a rapid solution. Nobody sits in your head to understand your way of thinking

  3. #3
    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

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Old Country
    MS-Off Ver
    PC: Best
    Posts
    8,662

    Re: Adding text to pivot table values

    something like this one? (but which one)

    pt.jpg

  5. #5
    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?

  6. #6
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Old Country
    MS-Off Ver
    PC: Best
    Posts
    8,662

    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)

  7. #7
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Old Country
    MS-Off Ver
    PC: Best
    Posts
    8,662

    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.

  8. #8
    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

  9. #9
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Old Country
    MS-Off Ver
    PC: Best
    Posts
    8,662

    Re: Adding text to pivot table values

    No problem
    Thanks for rep

  10. #10
    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

  11. #11
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Old Country
    MS-Off Ver
    PC: Best
    Posts
    8,662

    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)

  12. #12
    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

  13. #13
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Old Country
    MS-Off Ver
    PC: Best
    Posts
    8,662

    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 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