What's the format of your survey results?
My script "assumes" that the current sheet has three columns:
Survey#, Question, Answer in columns A, B, and C.
It places the summary in columns F:Kwith the header of: Question#, Ans_1,
Ans_2, Ans_3, Ans_4, Ans_5
Sub CountArray()
Dim Results(1 To 4, 1 To 5)
Dim nRow, nRows
Dim qNo As Integer, qVal As Integer
nRows = Application.WorksheetFunction.CountA(Range("A:A"))
For nRow = 2 To nRows
qNo = Cells(nRow, "B").Value
qVal = Cells(nRow, "C").Value
Results(qNo, qVal) = Results(qNo, qVal) + 1
Next nRow
For qNo = 1 To 4
For qVal = 1 To 5
Cells(qNo + 1, qVal + 6).Value = Results(qNo, qVal)
Next qVal
Next qNo
MsgBox "Fin"
End Sub
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
On Monday, February 20, 2017 8:43 AM, Greg Della-Croce
<greg_della-cr...@sil.org> wrote:
Paul, That is an excellent idea! All my survey results are on one page of on
workbook. So this should be fairly simple to write. Thanks!!
Greg Della-CroceSkype: gdellacroce55Cell:
407-408-2572greg_della-cr...@sil.org"If you assume you know the answer, you
will miss having a breakthrough. It’s okay to do what you did yesterday, but it
will never be amazing again.". - Rod Favarod, CEO of Spredfast
On Mon, Feb 20, 2017 at 7:55 AM, Paul Schreiner <schreiner_p...@att.net> wrote:
How are your survey results stored?Are they separate files?or have you already
combined them into one workbook?
What *I* would do would define an array:Results(QuestionNo, QuestionVal)
Dim Results(1 to 4,1 to 5)
Then, go through your surveys and increment the array value for each
questionThat is: Question 1, value 1, increment Results(1,1) by 1:
Results(qNo, qVal) = Results(qNo, qVal) + 1
Once you've populated the array, you can use nested loops to write the values:
For qNo = 1 To 4
For qVal = 1 To 5 Cells(qNo + 1, qVal).Value = Results(qNo,
qVal)
Next qVal
Next qNo Since I don't know how your data is formatted, it is difficult
to write sample code.
I created a worksheet of 1000 surveys, 4 questions each, and random values 1-5.
The entire subroutine to count the values and write out the results was 20
lines and took less than a second to run!
If you have to open each survey separately, it will take significantly longer :)
let me know if you need additional assistance.
Paul------------------------------ -----------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
------------------------------ -----------
On Saturday, February 18, 2017 6:28 PM, Greg Della-Croce
<greg_della-cr...@sil.org> wrote:
I have a sheet of responses to a short survey. The survey asked 4 questions
with range answers (1 to 5, 5 being best), and then two open-ended questions.
I would like to count up the number of each combination of answers with the
range answers. So for all of the surveys, I would like to know how many people
that answered 1 to question 1 answered question 2 - 1, 3 -1, 4-1, and how many
of those people answered 2-2, 3-1, 4-1, and how many answered 2-1, 3-2, 4-1,
and so forth for all of the combinations of answers.
I just can not figure out the right algorithm to do this without hard-coding
ever possible combo. Would someone know where I could get some help doing this
in Exel, or suggest a better program?
Thanks--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/ discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscribe@ googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/ group/excel-macros.
For more options, visit https://groups.google.com/d/ optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/ discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to a topic in the Google
Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this topic, visit https://groups.google.com/d/
topic/excel-macros/yLt6- N1O040/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
excel-macros+unsubscribe@ googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/ group/excel-macros.
For more options, visit https://groups.google.com/d/ optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
