I'm helping a coworker with a problem and I've run into a bit of a wall myself. He receives a CSV file from a survey of student responses after a set of classes are completed, and he wants to generate reports showing the number of responses in each category in each question (i.e. # Agree, # Strongly Agree, etc).
The CSV file has a format similar to this:
DateTime School Class Question 1 Question 2 Question 3 ... etc ...
======== ====== ===== ========== ========== ==========
1/1/2012 A 1 Agree Strongly Agree Disagree
1/1/2012 A 1 Disagree Agree Strongly Disagree
1/1/2012 A 2 Agree Strongly Agree Slightly Disagree
1/1/2012 A 1 Agree Agree Disagree
1/1/2012 A 2 Disagree Disagree Disagree
... etc 8,000 rows ...
What he would like is a report that looks similar to this:
School Class Q1 Agree Q1 Disagree ... Q1 Strongly Agree ...
====== ===== ======== =========== =================
A 1 2 1 0
A 2 1 1 0
... etc ...
Obviously I'm looking at a pivot table, but I'm running into an issue with how to define the pivot table. I'm not an expert at Excel, but when we tried various options we came up with essentially non-sensical results.
Is it possible to set up a pivot table that will give this data in a meaningful way? Counting instances of non-numeric values and grouping the counts by school and class? What would be a good (easily repeatable) strategy to accomplish this?
Thanks for any help, much appreciated.
(We are using Office 2007)
Select your datarange as a pivottable.
- Drag
Question 1, Class, and School to the Row Labels box.
- Drag
Question 1, Question 2, and Question 3 to the Values box.
- If it doesn't say "Count of Question 1", click the downward arrow on
Value Field Settings.
- In the
Summarize by tab, select Count.
- Repeat as neccesary.
- On another sheet, link to the specific pivot table cells to display the data in whatever format you wish.
edit: I have attached an image as a visual aid.
http://i.imgur.com/2Emw3.jpg
Let me know if you have any questions.
Fill a second sheet with formulas that concatenate School and Class and Answer.
The table would afterwards look like this:
Q1 Q2 Q3
=================================================================
A1Q1:Agree A1Q2:Strongly Agree A1Q3:Disagree
A1Q1:Disagree A1Q2:Agree A1Q3:Strongly Disagree
A2Q1:Agree A2Q2:Strongly Agree A2Q3:Slightly Disagree
Where A1 means School A Class 1.
You could even work with VLookup to replace the text with numbers
You can get this by adding this formula to Cell D3 in the dummy table:
=sourcetable!$B3&sourcetable!$C3&D$1&":"&sourcetable!D3
and then copy it to every cell.
Now all you have to do is create a result table like this:
Q1 Q1 Q1
School Class Agree Disagree Strongly Disagree
========================================================================
A 1 formula
where formula is
=countif(dummytable!C3:z999,$A4&B4&C$1&":"&C$2)
repeat this formula for all cells.
What the first part does and how it does it should be clear. In a production-level application I would replace the text (Agree/Disagree) with numbers (google for VLookup) the you see when there's a typo in the data.
The second part counts all occurrences of the string (in this case A1Q1:Agree). This should do the trick.
If you want to publis it, it is a good Idea to "copy:paste values" the result into a new table...
You can reuse the dummytable and the result table as is. Just drop the new data into sourcetable...
The issue is that your data is all ready partially pivoted.
Ideally it would be in the format:
DateTime School Class Question Answer
======== ====== ===== ========== ==========
1/1/2012 A 1 Question 1 Strongly Agree
1/1/2012 A 1 Question 2 Agree
etc
That format would lend itself to better work with a pivot table.
Here is instructions on doing an unpivot with Excel 2016
