Turning Survey Data Into Table

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.

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.

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?

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.

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

Sub OrganiseData()
    Dim lColumns As Long, lRows As Long, stItem As String, stScore As String, lCount As Long, x As Long
    lRows = Sheet1.Range("A6").CurrentRegion.Rows.Count - 1
    lColumns = Sheet1.Range("A6").Cells(1, Columns.Count).End(xlToLeft).Column - 1
    GoTo jump
    Sheet1.Range("A1:A5").EntireRow.Insert

jump:

    For x = 1 To lColumns
        With Sheet1
            stItem = .Range("B2").Cells(1, x)
            stScore = .Range("B3").Cells(1, x)
            lCount = .Range("B4").Cells(1, x)
        End With

        With Sheet3.Range("A" & Rows.Count).End(xlUp)
            .Cells(2, 1) = stItem
            .Cells(2, 2) = stScore
            .Cells(2, 3) = lCount
        End With

    Next x


End Sub

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.

Originally Posted by 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?

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?

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?