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!
--
Thank you! - Jennifer

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

"Jennifer Cali" wrote in message
...
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!
--
Thank you! - Jennifer

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


"Mark Ivey" wrote:

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

"Jennifer Cali" wrote in message
...
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!
--
Thank you! - Jennifer

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?


"Jennifer Cali" wrote in message
...
How would I do that? I have each survey (matrix style) on a different
workbook. Please let me know your thoughts.
--
Thank you! - Jennifer


"Mark Ivey" wrote:

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

"Jennifer Cali" wrote in message
...
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!
--
Thank you! - Jennifer

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 DATAPIVOT 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" wrote:

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

"Jennifer Cali" wrote in message
...
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!
--
Thank you! - Jennifer

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
















"Jennifer Cali" wrote in message
...
8Hi Mark,
1. The workbooks are all in the same directory: C:\My Documents\Survey
Results. They are named: Survey1, Survey2, Survey3, etc.
2. The worksheets are all on Sheet1. The matrix, on each sheet, is 5
across
and 20 down filling the cell range B8:G28.

Hope this helps, and thank you VERY MUCH for your help!
--
Thank you! - Jennifer


"Mark Ivey" wrote:

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?


"Jennifer Cali" wrote in message
...
How would I do that? I have each survey (matrix style) on a different
workbook. Please let me know your thoughts.
--
Thank you! - Jennifer


"Mark Ivey" wrote:

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

"Jennifer Cali" wrote in
message
...
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!
--
Thank you! - Jennifer