Import of 50+ Survey Data Results

[Jennifer Cali]

I have data results (20 questions) on 50 different spreadsheets. I want to
consolidate the result of the 20 questions into a meaningful report, within
Excel. Is there a good way to find out, for example, how many of the 50
respondents rated question number 1 as a 1, 2, 3, 4, or 5? My final result is
to find out something like:

Question 1:

1: Strongly Agree (10 responses)
2: Agree (20 responses)
3: Neutral (10 responses)
4: Disagree (10 responses)
5: Strongly Disagree (0 responses)

....and so on throughout the 20 questions. The surveys are all identical and
in an Excel form, so each cell on each survey is the same (with the exception
of the response that the associate has marked). I'm comfortable with most
macros, so if that's the way to go I'm open!

 

[Mark Ivey]

Macros might the way to go, but have you thought about Pivot Tables?

 

[Jennifer Cali]

How would I do that? I have each survey (matrix style) on a different
workbook. Please let me know your thoughts.

 

[Mark Ivey]

I must have misunderstood you. If they are all in different workbooks, then
a macro is probably the best way to go.

Can you give some more details on these workbooks:
1. Are the workbooks named the same or different? Do they have one common
location (directory)?
2. What are the names of the worksheets you need data from and from which
cells?

 

[Rafael Guerreiro Osorio]

Mark is right, the best way to accomplish your task is to make use of the
pivot tables, but first, you have to organize your dataset.

As far as I understood you applied a 20 question schedule to 50 people and
got the answers each person responded in a separate worksheet, creating a
workbook with 50 worksheets. To organize your dataset in order to use it with
pivot tables, you have to put everything in a single worksheet in which the
columns are variables (responses to each question) and rows are respondents
(people interviewed):

A B C
1 SEX AGE ADDRESS ...
2 male 32 Survey Street, n234
3 female 21 Under the bridge

After you organize your data in this manner, select the whole range with
data, and click on DATA>>PIVOT TABLE AND PIVOT CHART REPORT... to call the
wizard and make all the frequency tables you want.

You will only need a macro to organize the data. I can help you with that if
you further clarify how the original data is organized.

Best,

Rafael

 

[Mark Ivey]

Jennifer,

Not too sure if this is exactly what you were wanting, but it will put it
all into one workbook. Try it out and let me know...



Sub GatherSurveyData()
Dim FolderName As String, wbName As String, r As Long, cValue As Variant
Dim wbList() As String, wbCount As Integer, i As Integer
FolderName = "C:\My Documents\Survey Results\"
wbCount = 0
wbName = Dir(FolderName & "" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
ColArray = Array("B", "C", "D", "E", "F", "G")
For q = 1 To 20
Sheets.Add
r = 8
Cells(1, 1).Value = "Question " & q
Cells(7, 2).Value = "Strongly Agree"
Cells(7, 3).Value = "Agree"
Cells(7, 4).Value = "Neutral"
Cells(7, 5).Value = "Disagree"
Cells(7, 6).Value = "Strongly Disagree"
shName = ActiveSheet.Name
Sheets(shName).Name = "Question " & q
ca = 0
cb = 8
For col = 2 To 7
For i = 1 To wbCount
r = r + 1
cValue = GetInfoFromClosedFile(FolderName, wbList(i),
"Sheet1", _
ColArray(ca) & cb)
Cells(r, 1).Formula = wbList(i)
Cells(r, col).Formula = cValue
Next i
ca = ca + 1
r = 8
Next col
ca = 0
cb = cb + 1
Columns("A:G").Select
Selection.ColumnWidth = 15
Cells(1, 1).Select
Next q

End Sub

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String,
cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""
If Dir(wbPath & "" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function