SOLVED

Pivot table issue

%3CLINGO-SUB%20id%3D%22lingo-sub-3015448%22%20slang%3D%22en-US%22%3EPivot%20table%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3015448%22%20slang%3D%22en-US%22%3E%3CP%3EApologies-%20this%20may%20be%20the%20wrong%20forum%20for%20this%20type%20of%20question%20but%20I'm%20tearing%20my%20hair%20out.%3C%2FP%3E%3CP%3EThe%20problem%20-%20I%20have%20a%20flat%20excel%20workbook%20that%20has%20within%20it%20data%20similar%20to%20the%20structure%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3E%3CSTRONG%3EID%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%3CSTRONG%3EStatement%201%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%3CSTRONG%3EStatement%202%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%3CSTRONG%3EStatement%203%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%3CSTRONG%3EStatement%204%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3E%3CSTRONG%3E1%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EAgree%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EStrongly%20Agree%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3ENeutral%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EDisagree%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3E%3CSTRONG%3E2%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EDisagree%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EStrongly%20Disagree%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3ENeutral%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3ENeutral%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3E%3CSTRONG%3E3%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EStrongly%20Agree%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3ENeutral%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EDisagree%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3EAgree%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20able%20to%20easily%20generate%20pivot%20tables%20for%20each%20statement%20(%201%2C2%2C3%20etc)%20to%20show%20how%20many%20%22agree's%22%2C%20%22disagree's%22%20etc%20there%20are.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20what%20I%20want%20is%20a%20pivot%20table%20that%20counts%20the%20number%20of%20%22Strongly%20agree's%22%2C%22agree%22%20etc%20against%20each%20statement%20so%20that%20I%20can%20get%20a%20result%20something%20like%20below%3A-%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E%3CSTRONG%3EStatement%201%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E%3CSTRONG%3EStatement%202%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E%3CSTRONG%3EStatement%203%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3E%3CSTRONG%3EStrongly%20Agree%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3E%3CSTRONG%3EAgree%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3E%3CSTRONG%3ENeutral%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3E%3CSTRONG%3EDisagree%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%3E%3CSTRONG%3EStrongly%20Disagree%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20this%20would%20be%20a%20very%20simple%20thing%20to%20do%2C%20but%20I%20cant%20get%20it%20to%20work.%26nbsp%3B%20Can%20anyone%20help%20at%20all%2C%20or%20point%20me%20in%20a%20direction%20where%20I%20could%20get%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPhil%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3015448%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3016026%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3016026%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1232785%22%20target%3D%22_blank%22%3E%40philbloggs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20data%20is%20already%20in%20a%20crosstabular%20structure.%20You%20have%20to%20unpivot%20first%20before%20you%20can%20do%20the%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3016039%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3016039%22%20slang%3D%22en-US%22%3EThanks%20for%20getting%20back%20to%20me%20so%20quickly.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20raw%20data%20isn%E2%80%99t%20in%20a%20pivot%20chart-%20it%E2%80%99s%20a%20the%20output%20from%20a%20MS%20forms%20questionnaire%20as%20an%20excel%20spreadsheet.%3CBR%20%2F%3E%3CBR%20%2F%3EPhil%3C%2FLINGO-BODY%3E
New Contributor

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:

 

IDStatement 1Statement 2Statement 3Statement 4
1AgreeStrongly AgreeNeutralDisagree
2DisagreeStrongly DisagreeNeutralNeutral
3Strongly AgreeNeutralDisagreeAgree

 

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 1Statement 2Statement 3
Strongly Agree110
Agree100
Neutral012
Disagree101
Strongly Disagree010

 

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

 

5 Replies

@philbloggs 

Your data is already in a crosstabular structure. You have to unpivot first before you can do the pivot table.

 

Thanks for getting back to me so quickly.

The raw data isn’t in a pivot chart- it’s a the output from a MS forms questionnaire as an excel spreadsheet.

Phil
It is still a crosstabular structure.
best response confirmed by philbloggs (New Contributor)
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 

 

WynHopkins_0-1638230131052.png

 


 

@Wyn Hopkins  That's really helpful. Thanks for taking time out to answer such a rookie question.

 

Phil