+ Reply to Thread
Results 1 to 2 of 2

Pivot Table with multi-line and combined columns

  1. #1
    Registered User
    Join Date
    06-19-2015
    Location
    Guatemala
    MS-Off Ver
    2013
    Posts
    1

    Unhappy Pivot Table with multi-line and combined columns

    I hope I can explain myself correctly so I can get the correct solution from you all. We have made a poll to the students of the university I work at. We want to create a chart and a pivot table with the data. Thing is, not all our answers are entirely straight-forward and some have multiple choice. I hope this image can help you understand how the data looks like in my table:

    0b407750ae3c30b86c32990b1ea66a0c.png

    Now, as you can see, I have some combined cells describing the general topic, then I have the question, and then the possible answers, in some cases there's one question with just one answer (as in personal data) and in some cases I have one question with 2 or more possible answers. Now, take into consideration that this image shown is NOT the actual poll or size of the poll, I stretch out to 62 columns with data. How in the world can I creat a pivot table that can help me see the answers by clasification and the amount of answers for each of the specific questions so that I can then create a chart using the pivot table?

    I hope you guys can help me out, I DO need all the columns names, meaning I can't just use the line with the specific questions.

  2. #2
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Pivot Table with multi-line and combined columns

    Hello,

    in order to build a pivot table with this data, you first need to unpivot the data source into a flat table. Something like this:

    2015-06-20_12-34-39.gif

    Every answer is in its own row. If there are two answers to a question, there will be a row for each answer.

    With your Excel version, you can use the free Power Query add-in from Microsoft to help with transforming the data into the desired format. Then you can build pivots like this:

    2015-06-20_12-39-28.gif

    cheers, teylyn
    Last edited by teylyn; 06-19-2015 at 08:40 PM.

+ 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 with 2 colums combined--- displayed together
    By Istirling in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-22-2013, 04:30 PM
  2. Pivot Table & Vlookup Combined?
    By somebody113 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-16-2011, 03:18 AM
  3. Multiple stacked columns combined with a line graph
    By rkd1979 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-23-2010, 08:00 PM
  4. balances are combined,:Pivot table formula
    By jjjjj55555 in forum Excel General
    Replies: 1
    Last Post: 05-17-2007, 06:44 PM
  5. Can Multi WorkBooks Be combined in One Pivot Table?
    By dallin in forum Excel General
    Replies: 0
    Last Post: 09-28-2005, 11:05 AM

Tags for this Thread

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