-------------------------------------------- On Mon, 2/20/17, martinez.david533 via MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> wrote:
Subject: Re: $$Excel-Macros$$ Counting up the combinations To: excel-macros@googlegroups.com Date: Monday, February 20, 2017, 7:11 PM -------------------------------------------- On Mon, 2/20/17, Paul Schreiner <schreiner_p...@att.net> wrote: Subject: Re: $$Excel-Macros$$ Counting up the combinations To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com> Date: Monday, February 20, 2017, 4:50 PM 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. iarul Tribuna din 30 martie 1918 despre unirea Basarabiei cu Romania -- 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.n ce consta semnificatia cunoasterii structurii confesionale a locuitorilor din spatiul carpatc-danubiano-pontic? -- 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.