+ Reply to Thread
Results 1 to 4 of 4

Pivot table with multiple questions with multiple answers

  1. #1
    Registered User
    Join Date
    05-19-2020
    Location
    Bosnia
    MS-Off Ver
    2016
    Posts
    4

    Exclamation Pivot table with multiple questions with multiple answers

    Howdy folks! I am a bit stuck.
    I created a pivot table but i am stuck..

    I have a question regarding Gender And I have multiple questions which have multiple answers on a scale from 1 to 5. I would like to present them all in one pivot


    Question a -> 1 - 2- 3- 4- 5
    Question b -> 1 - 2- 3- 4- 5
    Question c -> 1 - 2- 3- 4- 5
    Question d -> 1 - 2- 3- 4- 5
    Question f -> 1 - 2- 3- 4- 5

    I need to sort the answers first of all by gender and then by the questions.
    I can make pivot table for every question, but i was wondering can i do them all in one? (example attached)

    My pivot looks that I have Gender in Column
    Value in row, and question count in values
    and i get the number of answers in total, but i am trying to break it down by the answer option to have something like this


    Female Male
    1 2. 3. 4. 5 1. 2. 3 4. 5
    Question a -> total answers by option
    Question b ->
    Question c ->
    Question d ->
    Question f ->


    So I need to add something under columns which will get me a scale from 1 to 5 in the columns and then sort the answers below.. (i am not sure was i clear enough) but please find the example attached

    Hope someone can help out! Thanks in advance ..
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-11-2012
    Location
    Muscat, Oman
    MS-Off Ver
    Office 365
    Posts
    521

    Re: Pivot table with multiple questions with multiple answers

    Hi, Please check the attached file.

    Used a little bit of transformation of data to get to the result shown in the Pivot .

    Best Regards/VKS
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-19-2020
    Location
    Bosnia
    MS-Off Ver
    2016
    Posts
    4

    Re: Pivot table with multiple questions with multiple answers

    Thanks VKS!
    Can you explain me or tell what was the process for the prep table (i can google and figure it out) I just need a starting point.

    Thanks

  4. #4
    Forum Contributor
    Join Date
    11-11-2012
    Location
    Muscat, Oman
    MS-Off Ver
    Office 365
    Posts
    521

    Re: Pivot table with multiple questions with multiple answers

    Hi,
    I used Get and Transform tools to re-shape the data. If you are accustomed to it then you can see all the steps in advanced query editor.
    My Idea was to:
    1. Get Gender in one column
    2. Get Questions in one column
    3. Add one column to show 1 question in 5 rows with value of 1 to 5 in each row (each possible selection value in one row) and this column was used under gender in columns area of the pivot)
    4. Final selection column was created to ensure if weight assigned is 1 then it shows against 1 of the options columns else it leaves blank
    Once above was done it was easy to get it into the pivot.
    Hope this helps.

    Best Regards/VKS

+ 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 Survey Data Multiple Questions, 5 Responses, Calculated Feilds
    By MysticGenius in forum Excel Charting & Pivots
    Replies: 20
    Last Post: 07-17-2019, 11:34 PM
  2. Pivot table won't count answers to several questions
    By erobhurley in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 07-22-2017, 01:09 PM
  3. Pivot Table Survey Data Multiple Questions
    By ajayifemi in forum Excel General
    Replies: 0
    Last Post: 02-29-2016, 04:55 AM
  4. Replies: 7
    Last Post: 11-27-2015, 09:48 PM
  5. Pivot Table Survey Data Multiple Questions, 5 Responses
    By mydragonstalents in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-20-2013, 06:15 AM
  6. Several questions about Pivot Tables - can't find answers to
    By Autin in forum Access Tables & Databases
    Replies: 1
    Last Post: 02-20-2012, 02:55 PM
  7. Using Pivot Table to analyze multiple answers
    By wengyee in forum Excel General
    Replies: 1
    Last Post: 03-09-2005, 09:06 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