Nov 29 2021 11:28 AM
Apologies- this may be the wrong forum for this type of question but I'm tearing my hair out.
The problem - I have a flat excel workbook that has within it data similar to the structure below:
ID | Statement 1 | Statement 2 | Statement 3 | Statement 4 |
1 | Agree | Strongly Agree | Neutral | Disagree |
2 | Disagree | Strongly Disagree | Neutral | Neutral |
3 | Strongly Agree | Neutral | Disagree | Agree |
I'm able to easily generate pivot tables for each statement ( 1,2,3 etc) to show how many "agree's", "disagree's" etc there are.
However, what I want is a pivot table that counts the number of "Strongly agree's","agree" etc against each statement so that I can get a result something like below:-
Statement 1 | Statement 2 | Statement 3 | |
Strongly Agree | 1 | 1 | 0 |
Agree | 1 | 0 | 0 |
Neutral | 0 | 1 | 2 |
Disagree | 1 | 0 | 1 |
Strongly Disagree | 0 | 1 | 0 |
I thought this would be a very simple thing to do, but I cant get it to work. Can anyone help at all, or point me in a direction where I could get help?
Thanks in advance
Phil
Nov 29 2021 02:19 PM
Your data is already in a crosstabular structure. You have to unpivot first before you can do the pivot table.
Nov 29 2021 02:27 PM
Nov 29 2021 03:57 PM - edited Nov 29 2021 04:00 PM
Solution@philbloggs
@Detlef Lewin is rereferring to using Power Query to Unpivot the data first, this then converts your multiple columns into a 2 columns as per the attached file. This is then ideal for Pivot Table use.
Click inside your data and turn it into a Table (using Ctrl T) then go to Data - From Table/Range (or it may say from Sheet depending on your version of Excel)
That opens Power Query
Then Right CLick the first column and choose Unpivot Other then click Close and Load To... drop down and choose Data Model connection only
Nov 30 2021 02:48 AM
@Wyn Hopkins That's really helpful. Thanks for taking time out to answer such a rookie question.
Phil
Nov 29 2021 03:57 PM - edited Nov 29 2021 04:00 PM
Solution@philbloggs
@Detlef Lewin is rereferring to using Power Query to Unpivot the data first, this then converts your multiple columns into a 2 columns as per the attached file. This is then ideal for Pivot Table use.
Click inside your data and turn it into a Table (using Ctrl T) then go to Data - From Table/Range (or it may say from Sheet depending on your version of Excel)
That opens Power Query
Then Right CLick the first column and choose Unpivot Other then click Close and Load To... drop down and choose Data Model connection only