Pivot table for a survey (How to select count of "Agree", "Disagree" for a certain subset of population?)

G

gowthamj

New Member
Joined
Feb 10, 2010
Messages
6
Hope you are all well and that you are gearing up for a wonderful holiday season.

There is just one thing between me and a few days of rest - and I am a bit stuck and would like some input from you kind people.
------------------------------

Aim: To show survey results (table or graph) based on filter (sub-set of the population surveyed)
Input: A huge table full of survey results indicated by the table below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Division[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[TD]Q5[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Sales[/TD]
[TD]Agree[/TD]
[TD]Disagree[/TD]
[TD]Agree[/TD]
[TD]Neither[/TD]
[TD]Highly Agree[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Finance[/TD]
[TD]Highly Agree[/TD]
[TD]Disagree[/TD]
[TD]Highly Agree[/TD]
[TD]Agree[/TD]
[TD]Highly Disagree[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]Sales[/TD]
[TD]Agree[/TD]
[TD]Neither[/TD]
[TD]Disagree[/TD]
[TD]Highly Agree[/TD]
[TD]Disagree[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]HR[/TD]
[TD]Disagree[/TD]
[TD]Agree[/TD]
[TD]Agree[/TD]
[TD]Highly Disagree[/TD]
[TD]Neither[/TD]
[/TR]
</tbody>[/TABLE]

1. How do I build a pivot table which can let me find out number of "Agree", "Disagree" etc. for Sales people, for Sales people in the UK, people in the USA etc.?

2. I want to give 'weighting' to Agree, Disagree scale (so Highly Disagree is 1, Highly Agree is 5) and show me the average score for each question for the subset.



Can anyone please help with this? I would really appreciate it if you could give some pointers!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Hi

I think you'll be able to get what you want from Pivot tables and slicers if you can arrange your data in a slightly different way.

Can you transpose your data into this format?

I just created the rank from a simple Vlookup for the response 1 - 5.

Then you can adjust the fields as required.

HTH
G12


[TABLE="width: 433"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Division[/TD]
[TD]Question[/TD]
[TD]Answer[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Sales[/TD]
[TD]Q1[/TD]
[TD]Agree[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Sales[/TD]
[TD]Q2[/TD]
[TD]Disagree[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Sales[/TD]
[TD]Q3[/TD]
[TD]Agree[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Sales[/TD]
[TD]Q4[/TD]
[TD]Neither[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Sales[/TD]
[TD]Q5[/TD]
[TD]Highly Agree[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Finance[/TD]
[TD]Q1[/TD]
[TD]Highly Agree[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Finance[/TD]
[TD]Q2[/TD]
[TD]Disagree[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Finance[/TD]
[TD]Q3[/TD]
[TD]Highly Agree[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Finance[/TD]
[TD]Q4[/TD]
[TD]Agree[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Finance[/TD]
[TD]Q5[/TD]
[TD]Highly Disagree[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Then you can pivot the answers as your column heading and the Average of Rank as you Value.

[TABLE="width: 565"]
<tbody>[TR]
[TD]Average of Rank[/TD]
[TD]Column Labels[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels[/TD]
[TD]Highly Agree[/TD]
[TD]Agree[/TD]
[TD]Neither[/TD]
[TD]Disagree[/TD]
[TD]Highly Disagree[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2.8[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Q3[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Q4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Q5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]3.2[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Q3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Q4[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Q5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD]Q1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.5[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Q3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.5[/TD]
[/TR]
[TR]
[TD]Q4[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD]Q5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3.25[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
G12 - thank you so much for your reply.

Unfortunately, in my actual data we have several questions (upwards of 20) so it would be very difficult to fill out the data this way - unless there was a way to automate tranpose.

Is there any other way to use the same Column labels (Agree, Disagree etc.) for the Pivot Table? The problem now is that it doesn't see "Agree, Disagree" as standard labels but rather ones associated with the particular "value" list

G12 said:
Hi

I think you'll be able to get what you want from Pivot tables and slicers if you can arrange your data in a slightly different way.

Can you transpose your data into this format?

I just created the rank from a simple Vlookup for the response 1 - 5.

Then you can adjust the fields as required.
Click to expand...
 
Upvote 0
May be I am not explaining myself correctly.

I want to run a pivot to show number of Agree and Disagree against each question, but because the "Row labels" (Agree, Disagree) are taken individually for each question, I can't use them as labels against which all questions show the count.

Any help?
 
Upvote 0
I'm thinking it's still easiest to change the format of the data to get it into a proper table. Is the number of questions that have been answered always the same (even if it is >20 questions)?

If you can't manually transpose, then a simple macro should do the trick. I can help with this but need a bit more on the actual data sets.

Let me know
G12
 
Upvote 0
Is this required even if we forget the weighting and averages for now?

I want the user to be able to select more than 1 country and 1 OS - hence why I want to stick to Pivot rather than Macros for everything.

Number of question is always the same, but they don't all have to be answered
 
Upvote 0
The issue is getting the data into a format so that you can use all the tools of a pivot table. To this end you can either work through it manually, or some kind of macro. Once done, you should be able to achieve what you are looking for using only the pivot table tools.

Try adding this code to a module and running (on a back up copy first) on your data. It's not the prettiest but does the job. I've tried it with your sample info and had no issues. You didn't say how many questions there actually were so I've added a variable on the assumption that these continue across the columns.

Best of luck
G12



Code: 
Sub TransposeOMatic()


Dim WsData As Worksheet, WsNew As Worksheet
Dim WsDataRi As Integer, WsNewRi As Integer, EPRi As Integer, Ci As Integer, EPCi As Integer


Set WsData = ActiveSheet
Set WsNew = Sheets.Add


EPRi = WsData.Range("A" & Rows.Count).End(xlUp).Offset(0, 0).Row
EPCi = WsData.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 0).Column


For WsDataRi = 2 To EPRi
    WsNew.Range(Cells(WsNewRi + 1, 1), Cells(WsNewRi + EPCi - 2, 1)).Value = WsData.Cells(WsDataRi, 1).Value
    WsNew.Range(Cells(WsNewRi + 1, 2), Cells(WsNewRi + EPCi - 2, 2)).Value = WsData.Cells(WsDataRi, 2).Value
        For Ci = 3 To EPCi
            WsNew.Cells(WsNewRi + 1 + Ci - 3, 3).Value = WsData.Cells(1, Ci).Value
            WsNew.Cells(WsNewRi + 1 + Ci - 3, 4).Value = WsData.Cells(WsDataRi, Ci).Value
            Select Case WsData.Cells(WsDataRi, Ci).Value
                Case Is = "Highly Agree"
                    WsNew.Cells(WsNewRi + 1 + Ci - 3, 5).Value = 5
                Case Is = "Agree"
                    WsNew.Cells(WsNewRi + 1 + Ci - 3, 5).Value = 4
                Case Is = "Neither"
                    WsNew.Cells(WsNewRi + 1 + Ci - 3, 5).Value = 3
                Case Is = "Disagree"
                    WsNew.Cells(WsNewRi + 1 + Ci - 3, 5).Value = 2
                Case Is = "Highly Disagree"
                    WsNew.Cells(WsNewRi + 1 + Ci - 3, 5).Value = 1
                Case Else
                    WsNew.Cells(WsNewRi + 1 + Ci - 3, 5).Value = 0
            End Select
        Next Ci
WsNewRi = WsNew.Range("A" & Rows.Count).End(xlUp).Offset(0, 0).Row
Next WsDataRi
Rows(1).Insert
Cells(1, 1).Value = "Location"
Cells(1, 2).Value = "Division"
Cells(1, 3).Value = "Question"
Cells(1, 4).Value = "Answer"
Cells(1, 5).Value = "Rank"
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Counting the number of Agrees and Disagrees depending on what department someone belongs to
Replies
3
Views
4K
Soni123456
S
Z
  • Question Question
Survey Table
Replies
3
Views
721
Steph77
S
D
  • Question Question
Pivottable: How to use Likert-type Answers as Cols, Questions as Rows, count of Question responses as Values?
Replies
1
Views
6K
shg
shg
M
  • Question Question
Complicated transpose of data with conditions
Replies
3
Views
1K
mahmed1
M
S
  • Question Question
Pivot tables
Replies
4
Views
482
Sapell
S

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top