+ Reply to Thread
Results 1 to 11 of 11

Can i display my data in a pivot table?

  1. #1
    Registered User
    Join Date
    09-18-2017
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Can i display my data in a pivot table?

    Hi Guys,

    Could someone help me out with pivot tables - I have never used them but I think they might be suitable for my situation.

    I have some names of listed companies and their majority shareholders. At the moment the data is in a series of tables arranged by company. I would like to create a new table with all the shareholder names in one column and then the company names in one row so I could easily show which shareholders hold stakes in multiple companies and also be able to filter by cap size. I have made a small mock up at the bottom of my spreadsheet attached..... is this possible with pivot table?

    Appreciate the help,

    Chris
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Can i display my data in a pivot table?

    You can use PowerQuery then PivotTable (if necessary)
    append all shareholders with their names in column. if you do that correctly PivotTable is not necessary, I think
    Last edited by sandy666; 04-22-2018 at 02:02 PM.

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Can i display my data in a pivot table?

    here is an example only for few columns

    it doesn't work without PowerQuery

    Power Query for
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-18-2017
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Re: Can i display my data in a pivot table?

    H sandy,

    Thanks for that - your sheet is pretty close to what im aiming for. On sheet 3 is it possible to have every single shareholder name in the first column ie it is populated with every named shareholder in every company and then all of the different companies are shown in one row at the same time so that for a given shareholder like Aberdeen Asset Management you could look across the rows and see every company that they have shares in? And then your group section could classify instead filter the results by cap size (small, mid, large). Im trying to see if i can change your table but im a bit new to this!

    thanks,

    Chris

  5. #5
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Can i display my data in a pivot table?

    move Group field from Filters Area at the first place in Rows Area, then set tabular form (no subtotals, no totals)

    group.jpg
    Last edited by sandy666; 04-22-2018 at 02:51 PM.

  6. #6
    Registered User
    Join Date
    09-18-2017
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Re: Can i display my data in a pivot table?

    Ah ok thank you thats helpful - it is now the way I want it to lookREITs.PNG

    Final thing - could you tell me how I now add the remaining companies data into the table?

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Can i display my data in a pivot table?

    Like I said before - you'll need PowerQuery add-in
    You didn't say you've or not...

  8. #8
    Registered User
    Join Date
    09-18-2017
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Re: Can i display my data in a pivot table?

    Ah yes sorry - I do have it... Do I need to do something on the power query editor? I can see the tables you added on there.

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Can i display my data in a pivot table?

    ok,
    in Query Editor you've one table1 then table1 (2)..table1 (3) etc...
    if you click on each step on the right side you will see how it was done

    so
    delete Append1
    duplicate the table1 as many as you've groups
    then check steps in table1 (2) then repeat all steps for each duplicated tables
    when you will do that, append all tables and create PivotTable from Query - Append1

    and No, actual Pivot Table doesn't work with old append1 or new append1. You need to delete PT and start from the begining.
    Last edited by sandy666; 04-22-2018 at 03:49 PM.

  10. #10
    Registered User
    Join Date
    09-18-2017
    Location
    london
    MS-Off Ver
    2013
    Posts
    9

    Re: Can i display my data in a pivot table?

    Hi,

    Sorry for the delay.

    Thats perfect thank you - I got it all sorted exactly as I wanted it.

    Really appreciate the help.

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Can i display my data in a pivot table?

    You are welcome

    If that takes care of your original question, & to say Thanks, please
    1. click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
    If you did it already - ignore it.
    Thank you.

+ 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. Replies: 2
    Last Post: 11-02-2016, 08:02 PM
  2. pivot table help, display data as
    By pg300 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-27-2014, 03:18 PM
  3. [SOLVED] Display data in pivot table
    By carrielynne in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-17-2014, 10:55 AM
  4. Can Pivot Table display 0 if there's no data?
    By Dan.Reynolds in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-08-2013, 03:59 AM
  5. [SOLVED] Pivot Table Get & Display Data Help...
    By sagar007 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-25-2012, 06:57 AM
  6. Pivot Table to roll up data and display in pivot chart
    By Dawson64 in forum Excel General
    Replies: 3
    Last Post: 09-26-2011, 05:06 PM
  7. Display data from pivot table
    By noelcass in forum Excel General
    Replies: 2
    Last Post: 07-16-2010, 04:01 AM

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