Turning Survey Data Into Table

[llll93]

Hi Everyone,

I am looking for some help turning data from a survey into a count table.
The original survey question looked something like this:
Screen Shot 2017-06-06 at 5.22.05 PM.png
Where the responders click one choice for each item.

The excel sheet that was generated looks something like this:
Screen Shot 2017-06-06 at 5.13.22 PM.png
With the responses coded as numbers.

How would I go about turning this into a data table that looks like this:
Screen Shot 2017-06-06 at 5.18.45 PM.png
With a count of the responses for each item?

I am not very experienced with Excel so I would really appreciate it if someone could tell me step-by-step.

Thank you!

[Richard Buttrey]

Hi,

See the attached. Paste your data into A1:J??

The results will be in the table in L7:O9

If you have any influence over the design of the survey sheet then you should change the layout so that you have columns for

Response number
Category - Cells here would be data validation drop down cells from which you'd select A, B or C
Answer - Cells here would be data validation drop down cells from which you'd select Agree, Neutral, Disagree
Score

With the data captured in this way analysis is immensely simplified since you can turn on a Pivot Table

I've added this 2nd data layout and a Pivot Table to show you the idea.

Would you also update your profile so that we know your location and what version of Excel you have. Knowing this stuff is often very helpful.

[llll93]

Hi Richard,

Thank you for the response. I'm not quite sure that I'm understanding correctly.

The tables I posted are not my actual data, just simplified examples. The questions in my survey have about 8 items and 5 choices for each item. I only have a PDF of the actual survey along with the generated Excel sheet. In the sheet the numbers 1, 2, 3 don't correspond to the number of people who checked each option but are codes for Agree, Disagree, etc. For example, in the "disagree" column a 1 appears if the responder checked that box, otherwise there is a blank. In the "neutral" column a 2 appears if the box was checked. So I am not tallying the totals for each column but the number of 1s, 2s, 3s, etc.

Does that make sense? I don't want to post my actual data, but I can send a chunk of it if I am not describing it clearly. I am using Excel 2017 on Mac.

Thank you!

[Richard Buttrey]

The principles are still the same. If you don't want the DSUM formula then change it to a DCOUNT.

If the response matrix is larger than your original 9 columns then just extend it and add the extra rows/columns to the DCOUNT formula.

As I suggested, for any sort of analysis like this the data really should collected in the format I showed otherwise you just make life difficult for yourself. For one thing the results are not easily scaleable as you're discovering, whereas with the layout I suggested you just add new rows as data comes along.

Since this is clearly a much simplified example and apparently doesn't represent the size of your actual data we often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see.

[llll93]

The data collection was finished before I came on to the project so unfortunately I have no control over that, I was only given the output Excel file. Here is part of my data and what I would like the table to look like (column and row names don't really matter).

[Richard Buttrey]

OK, I'm out most of today. I'll revert later.

Would you add your location to your profile please. It often helps when it comes to stuff like understanding regional settings.

Is the data from a Survey Monkey system?

[davsth]

Whilst I fully agree with Richard, and each question could be set up as 2 columns, 1 for the comfort and a second for the availability, as the responses are mutually exclusive, I know you did not design the data capture.

The following should work in cell b3 on the table tab, copy down and accross
=COUNT(OFFSET(Data!$A$2,0,ROW(A1)+COLUMN(A1)-1,300,1))

[Richard Buttrey]

Here's a VBA approach which will adjust to any range of data.

It reformats the original data into a 3 column data range that can be used by a Pivot Table See sheets Data2 and the PT sheet.

[llll93]

Thanks Richard, this looks exactly like what I need. I need replicate this format for many of the other questions in the survey, with variable numbers of possible responses. What would be the best way of doing that? I am thinking I should try to learn the basic language so I can produce these formulas myself, if that is feasible to do in a short time with zero programming background.

[llll93]

Thank you Davsth, when I put the formula in B3 and drag it across it works but when I drag it down (I can't seem to do both at once) it returns the same numbers as going across so that only the first row is correct. I'm probably missing something simple?

[llll93]

Actually I see that you have made it so I can keep dragging the formula across for questions with more options. To someone who has no idea what a VBA is that is really neat! Do you have any suggestions for beginners to start learning how to do things like this?

*Yes I'm pretty sure it was from SurveyMonkey.

[Richard Buttrey]

Hi,

The first thing to do would be to look at the code in the workbook - see also below and see whether you can get the basic gist of what it's doing. I think it should be reasonably intuitive but thereagain I've been doing this stuff for more years than I care to remember.

However I wrote the code so that it could automatically cater for any number of questions and responses. There are two things I HAVEN'T done yet and which would be needed to completely generalise the system.

1. You can see I jump over the line in the code that inserts 5 rows at the top. That's not necessary now that they have been added and I should perhaps have deleted that code altogether. (I tend to use this approach to skip code as I'm developing something). I intended that in the real world system the formula in rows 1:4 of your data sheet would already be there and the macro would prompt you to select a file with a new set of data, and the macro would copy that data after clearing the old data into the range starting at A6.

2. The basic formula in B1:B4 is named "form1". The intention was that whenever a new set of data is loaded the macro would copy that formula across as many columns as necessary

Please Login or Register  to view this content.

[llll93]

Thanks, I'll take a look at some tutorials and try to figure out the different parts of the code.
One more question: I would like to have one sheet for each survey question, with the data, summary (data2) and pivot table all on one page. Will it work if I just copy and paste the contents of "data2" to the other sheet or does something in the code need to change?

[Richard Buttrey]

Thanks, I'll take a look at some tutorials and try to figure out the different parts of the code.
One more question: I would like to have one sheet for each survey question, with the data, summary (data2) and pivot table all on one page. Will it work if I just copy and paste the contents of "data2" to the other sheet or does something in the code need to change?

Do you mean you want an additional 40 sheets, one for each question. If so why? what's the advantage.

The Data2 sheet is the output sheet you get after the macro runs and the macro uses the VBA sheet code name. Unless the code is changed the data will always populate that sheet.

The PT is linked to the Data2 sheet so simply copying it to somewhere else won't make any difference to the PT. You'd have to CUT it and Paste so that the PT stayed linked, but as I said above when you run the macro with a new set of data and unless the code is change the Data2 will be populated again.

Yes you can of course put different things all on one page but that flies in the face of good workbook design and I can't see any advantages. Why would you want to do that?

[llll93]

So I have the data for about 45 questions, all on one sheet going across columns. The part I uploaded is data for one of the questions that I cut out. I'm trying to figure out how to copy your solution to my original file. Since it has the data, pivot table and summary on separate sheets and that's just for one question, how do I reconcile that with the rest of the data?

[Richard Buttrey]

Rather than copy the stuff in the file I paste it to your file do it the other way around.

In the file with the macro first delete all the data rows from row 6 down.
Then copy your new data to A6 on the Data Sheet
If there are more than 40 columns of questions copy the formula in B1:B4 across all the columns, make sure that the B4 COUNTIF formula covers all the rows.

Then run the macro and on the PT sheet use the Pivot Table tools to Change the PT Data source since there will be additional rows then refresh the PT.

Is this from a Survey Monkey questionnaire by any chance?

[llll93]

Yes I'm pretty sure it's from a SurveyMonkey questionnaire! The thing is the questions are in many different formats (yes/no, select one answer, select all that apply, etc.) so the data is all different too, and I think this would only work for questions in the same format as the one I posted.

[Richard Buttrey]

I was just interested since I wrote a quite detailed SurveyMonkey analysis tool in Excel a couple of years back and thought I recognised the basic structure.
As you say the output from SM can be as simple or complex as needed. And based on what I remember from the last one I did even small changes in the data input to a common structure can cause problems let alone a different structure.

[llll93]

Yeah, I think I will just do some simple counts for each column and manually arrange them to be able to make pivot tables out of them. Very time-consuming but probably still faster than trying to learn VBA from scratch. Thank you for all your help!