Pivot Table Survey Data Multiple Questions, 5 Responses, Calculated Feilds

[MysticGenius]

Hi,

I have been trying for a few days to work out how to do this and i cannot figure it out, I have attached a sample.

On the DATA tab there is a sample of information pulled out of the survey system, there are 11 questions, and 5 possible answers for each question.

I am looking to create a table to count the number of each answers for each question.

I have put in a pivot table with what i am looking for, but i can only get it to work with a single question, and i dont really want to do 11 pivot tables!

Also, i have added a manual calculation down the side of the pivot table calculating the % Agree, is there a way to get excel to automatically to this itself in the pivot table?

Thanks in advance for help!

[dilipandey]

Hi Mystic,

You can put count of all questions in the "Values" field of the same pivot table.. or you are looking for some other thing here ?


Regards,
DILIPandey
<click on below * if this helps>

[MysticGenius]

Hi Mystic,

You can put count of all questions in the "Values" field of the same pivot table.. or you are looking for some other thing here ?


Regards,
DILIPandey
<click on below * if this helps>

Hi,

Thanks for the reply, what i am looking for is, for each question i need a count of how many agree, how many disagree etc split by campus and course.

In the sample file i have created a pivot with the rows as campus, then course name, then the column labels as one of the questions and the values as a count of the same question.

what i am wanting to do is add in the other questions and a count of their responses so that after the "Grand Total: column, the count of the next question wound start ans so on.

Thanks

[dilipandey]

Hi MysticGenius,

See the attached file and let me know if this helps:_


Sample.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[MysticGenius]

Hi MysticGenius,

See the attached file and let me know if this helps:_


Attachment 245849

Regards,
DILIPandey
<click on below * if this helps>

that is fantastic! how did you get the pivot table to do that?

I notice you have the SUM-Values in the column heading box in the pivot field list....i have never seen that!

Also i noticed that the counts are exactly the same for each question, the count for the first question is forrect, but the count on the second question is returning the values from the first count.




Thanks for your help

[dilipandey]

Hi MysticGenius,

Counts are same .. see the highlighted area in the attachment

Sample.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[MysticGenius]

Sample-All Questions.xlsx

Hi MysticGenius,

Counts are same .. see the highlighted area in the attachment

Attachment 246113

Regards,
DILIPandey
<click on below * if this helps>

But from your pivot table Every single question has exactly the same count

Sample-All Questions.xlsx

See this sheet, I have added all questions in and the counts are exactly the same for each question..

It would be a statistical impossibility for these questions to have been answered exactly the same for all 11 questions.


For example, if you look at the data for Campus "AAP" and Course Name "Commercial Law" in the data then compare to the counts on the pivot table they only match for the first question....do you see what i mean?

The in the data, the count for the first question is 3 Agree, and the second has 2 agree and 1 unable to judge, but the pivot table shows 3 agreeing for both questions.

[dilipandey]

Ohkk.. yes.. got your point.

Actually, since question 1 is there on column labels hence we are getting counts based on that headers only.

Now, see the below attachment where I have used the formulas in the data structures obtained from pivot after copy - pasting that as values

Sample-All Questions(1).xlsx

Regards,
DILIPandey
<click on below * if this helps>

[MysticGenius]

Ohkk.. yes.. got your point.

Actually, since question 1 is there on column labels hence we are getting counts based on that headers only.

Now, see the below attachment where I have used the formulas in the data structures obtained from pivot after copy - pasting that as values

Attachment 246148

Regards,
DILIPandey
<click on below * if this helps>

Now that looks more like it!! im off to bed, ill check this method out tomorrow in work!

thanks so much for your help!

[MysticGenius]

Ohkk.. yes.. got your point.

Actually, since question 1 is there on column labels hence we are getting counts based on that headers only.

Now, see the below attachment where I have used the formulas in the data structures obtained from pivot after copy - pasting that as values

Attachment 246148

Regards,
DILIPandey
<click on below * if this helps>

Hi,

I tried this sheet with my complete data set and i get the warning:

"Excel ran out of resources while attempting to calculate one or more formulas"

I dont want to post the complete data set on the web, do youthink there is any other way i could send you this data to see what the issue is?

thanks

[MarvinP]

Hi,

I took your original data and ran a macro on it to convert it from a CrossTab Table format to a flat table format. Then I did a Pivot on the result. Find the VBA code to convert your data to flat file in the attached. You will need to answer the message box with "7" as there are 7 fixed columns and the answers are after that.

Hope this is what you want...

[MysticGenius]

Hi,

I took your original data and ran a macro on it to convert it from a CrossTab Table format to a flat table format. Then I did a Pivot on the result. Find the VBA code to convert your data to flat file in the attached. You will need to answer the message box with "7" as there are 7 fixed columns and the answers are after that.

Hope this is what you want...

Ideally what i am looking for is a way to summarize the data into the format found here,

Attachment 246148

my problem is when i use the example above on my complete data set i get an "Excel out of resources" error!!

[dilipandey]

Since you are using Excel 2003 - may be on low configured system, hence you are facing resource error ... can you try once in any high end system ?


Regards,
DILIPandey
<click on below * if this helps>

[MysticGenius]

Since you are using Excel 2003 - may be on low configured system, hence you are facing resource error ... can you try once in any high end system ?


Regards,
DILIPandey
<click on below * if this helps>

Hi there,

I have a very high end system and i am running excel 2010.

My system is a quad core 1.9GHz, 16Gb Ram and 128Gb SSD.

[dilipandey]

Your profile says "Excel 2003" and you got resource error hence I thought like that



Regards,
DILIPandey
<click on below * if this helps>

[MysticGenius]

Your profile says "Excel 2003" and you got resource error hence I thought like that



Regards,
DILIPandey
<click on below * if this helps>

ahh!! i didnt see that, do you think i could send you the workbook so you can see if there is anything else wrong with it?

thanks

[dilipandey]

Please zip (compress) the workbook and upload here only

Regards,
DILIPandey
<click on below * if this helps>

[Akay4]

Ohkk.. yes.. got your point.

Actually, since question 1 is there on column labels hence we are getting counts based on that headers only.

Now, see the below attachment where I have used the formulas in the data structures obtained from pivot after copy - pasting that as values

Attachment 246148

Regards,
DILIPandey
<click on below * if this helps>

Hello I was following this posts because I am experiening the same issue of trying to analyze Surveys and trying to pivot the info.
The info that you gave so far has helped me to figure out what I may need to do.
But then I got stuck, because the data or the answers are showing the same answers for each question.I was trying to do as you said, which was to use the formula that was inyour excel documnet into the pivot but of course the pivot does not allow the changes. How and where did you insert the formulas into the pivot so that the answers will not be identical, but be accurate.

Your help is surely appreciated,

AKay

[MysticGenius]

Hello I was following this posts because I am experiening the same issue of trying to analyze Surveys and trying to pivot the info.
The info that you gave so far has helped me to figure out what I may need to do.
But then I got stuck, because the data or the answers are showing the same answers for each question.I was trying to do as you said, which was to use the formula that was inyour excel documnet into the pivot but of course the pivot does not allow the changes. How and where did you insert the formulas into the pivot so that the answers will not be identical, but be accurate.

Your help is surely appreciated,

AKay

hi,

you cannot do this with a pivot, i changed my solution and used a database!!

much faster and easier

thanks

[Esa Jokinen]

Same problem. Could you please explain, what do you mean by using database instead of Pivot? And shouldn't pivot allow several questions combined in one chart in the first Place? Otherwise pivot is not very helpful with surveys with lots of questions that need to be observed with one glimpse..
E

[FDibbins]

Administrative Note:

Esa, welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread