Quick Excel Surveys

Over the years, I have often been asked to run a survey, or been asked to help someone else who has already run the survey.

My first peice of advice with surveys, is that they can be fun, but preparation up-front pays off in spades! Ensuring that the design of the survey is suitable, that you’ve thought about what you’re going to do with the responses is critical.

Firstly, my advice. Don’t use Excel for Surveys, if you can help it. SurveyMonkey, Typeform are already great tools for collecting information in a user friendly, accessible and quick way.

You might have reasons however, not to use them, and this might include that you want to send customised data to each participant, and you don’t want this information ‘in the cloud’. And you don’t have time or resources to setup your own platform using something like limeSurvey.

Then, people usually fall back to either email reply surveys, or excel or word. Word does have forms, which can be automated to great effect, but then, excel can do calculations etc on the fly, which means you can show people results to somethings that they’re answering, and creating something of value to the participants in the survey.

But.

When you get the spreadsheets back from your participants, how do you aggregate all of the data into a research ‘database’ ?

Well, this is my relatively quickly built solution:

Master Extract Macro

I’ll spend some time walking through my idea for this.

It does require some thinking about the survey design in excel.

Firstly, every spreadsheet that is returned to you via email (or dropbox, or whatever) must be saved in the same directory (I’d suggest a naming convention when you send them out, but don’t expect it to have the same name when it’s returned!).

Secondly, every spreadsheet returned should only be for one user, and have a ‘primary ID’ somewhere in the document, this could be editable by the participant, but it is your key identifier (you could create your own hidden one if you wished though, and I’d suggest this is good practice).

Once you’ve laid out your ‘survey’ spreadsheet in order, you’ll be expecting certain questions to be located in certain cells. Lock this down!!!

Then, in my master extractor, you need to tell it where to expect to find data, what it’s called, and where to put it.

Then you run the macro, and you should get a new workbook, populated with all the data from your input/source/survey spreadsheets.

Feel free to use /edit / adapt this for yourself, but feel free to leave a comment if you do!

Details

On the first sheet 4 columns that represent the data to be collected. eg.

ExtractView1

IMPORTANT! The first row, represents the primary ID, or the thing that is going to separate your results from each file. Forgetting this will mean you cannot track the data to a source spreadsheet/user/survey participant.

Sheet Name (Source) is the name of the sheet in your survey workbook from where you are picking up the data (as the program loops over all the surveys in the directory).

Cell Ref (Source) is the cell reference where the value is being picked up from in the source survey

Data Name (Dest) is what you want to call the data, eg. “question 8”, this is limited only by your imagination, but descriptive names will make your analysis easier

Destination Sheet (Dest) is the sheet where you want the output data to go. You might just put all of your data onto the one page, but sometimes you want to separate your data into different tables / groups.

An example of this is that you might want to separate demographic data from survey data in the survey.

In relational database design, normalising tables so that data of one type sits in it’s own table, but is linked by a primary key, is quite common, this will help you use MS Access, or SQLite, or PostGreSQL, or similar to analyse your data – because you could link the Age of all survey participants (from Table A) with the answers to their survey (from table B) with a simple query. This would be much more difficult if you put all of the information in the same table.

Macros

The macro follows a pretty basic pattern:

  1. Get the directory for the files
  2. Read in the things we need to grab into a dictionary object
  3. Open a new ‘write’ workbook
  4. Loop over all the files in the directory, looking for each item in the dictionary, finding the value, and placing it into the ‘write’ workbook, keeping track of what row we’re up to
Option Explicit
Function getDirName()
' This function pops up a file dialog for picking which folder you want to do your extract from
Dim bob As Object
Set bob = Application.FileDialog(msoFileDialogFolderPicker)
bob.AllowMultiSelect = False
bob.Show
getDirName = bob.SelectedItems(1)

End Function


Sub Main()
'This is the main routine, it grabs all of the surveybooks, searches through them for the data as outlined on sheet 1 in this book
' and then pushes them into a new workbook


Dim OutputBook As Workbook
Dim SurveyBook As Workbook
Dim MainBook As Workbook
Dim ReadItems As Range
Dim SheetNames As Dictionary
Dim ShtNmeList As Range
Dim ShtNmes2 As Variant
Dim x As Integer
Dim item As Variant
Dim NameVar As String
Dim TempVar As String
Dim fs As FileSystemObject
Dim fldr As Folder
Dim f As File
Dim folderspec As String
Dim y As Integer

'Define SurveyBook Directory
folderspec = getDirName()
If folderspec = "-1" Then GoTo Finish
Set fs = CreateObject("SCripting.filesystemobject")
Set fldr = fs.GetFolder(folderspec)

'Make sure we track the current workbook, for readitems
Set MainBook = ActiveWorkbook


'Define Extract Details for iteration (in readItems), uses ranges + selection end points
MainBook.Activate
MainBook.Sheets(1).Range("A2").Select
MainBook.Sheets(1).Range(Selection, Selection.End(xlDown)).Select
MainBook.Sheets(1).Range(Selection, Selection.End(xlToRight)).Select
Set ReadItems = Selection

'Define Sheetnames from Mainbook inside OutputBook
'Uses the dictionary object from the scripting reference. Enables quick checking if you've seen something before, a bit like a collection
Set SheetNames = CreateObject("Scripting.Dictionary")
For Each ShtNmeList In ReadItems.Rows
    If Not (SheetNames.Exists(ShtNmeList.Cells(1, 4).Value)) Then
        SheetNames.Add ShtNmeList.Cells(1, 4).Value, 1
    End If
Next


'Define NewBook for extract details
Set OutputBook = Workbooks.Add

'after getting the required sheet names above, creates (or renames if a sheet already exists) those sheets in a new book
x = 1
For Each ShtNmes2 In SheetNames.Keys
   If x <= 3 Then
        OutputBook.Worksheets(x).Name = ShtNmes2
        x = x + 1
   Else
        OutputBook.Worksheets.Add().Name = ShtNmes2
    End If
Next

'Now do the extraction, loop through the files in the directory
'and for each file, get all the details as outlined in readItems (this books lookup reference for all surveys)
x = 1
For Each f In fldr.files
    Set SurveyBook = Workbooks.Open(f.Path)
    SurveyBook.Activate
    For Each item In ReadItems.Rows
            If x = 1 Then
                'define the primary key / unique ID for each survey sheet
                NameVar = SurveyBook.Sheets(item.Cells(1, 1).Value).Range(item.Cells(1, 2).Value).Value
                x = x + 1
            Else
                'get variable from survey
                TempVar = SurveyBook.Sheets(item.Cells(1, 1).Value).Range(item.Cells(1, 2).Value).Value
                'put variable into output
                y = SheetNames(item.Cells(1, 4).Value)
                OutputBook.Sheets(item.Cells(1, 4).Value).Range("A" & y).Value = NameVar
                OutputBook.Sheets(item.Cells(1, 4).Value).Range("B" & y).Value = item.Cells(1, 3).Value
                OutputBook.Sheets(item.Cells(1, 4).Value).Range("C" & y).Value = TempVar
                x = x + 1
                'conveniently track the row number that we're up to in the output sheet, using the dictionary, so we don't have to search the sheet each time
                SheetNames.item(item.Cells(1, 4).Value) = SheetNames(item.Cells(1, 4).Value) + 1
            End If
    Next
    x = 1
    SurveyBook.Close (False)
Next
Finish:
Set OutputBook = Nothing
Set SurveyBook = Nothing
Set SheetNames = Nothing
Set item = Nothing
Set ReadItems = Nothing
Set fldr = Nothing
Set f = Nothing
Set fs = Nothing
End Sub
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: