VBA Code to group Satisfaction Survey Data in Excel

[newqueen]

Dear All,

I frequently carry out analyses of Satisfaction Survey Data of very many people. I have a Survey Questionnaire with about 30 questions on my computer. I get each individual to complete the survey data by clicking/choosing I agree, strongly agree, disagree, etc. after which I save the document in the persons name in a folder named ‘Completed Questionnaire’.

I get another person to do the same thing after which I also save it in the person’s name.

When all my respondents have completed the questionnaire – say about 50 respondents, I then I print out each questionnaire and enter/group the responses into a Summary Excel sheet, from which I use the COUNT/COUNTA functions to do the analysis.

I have no problem doing the final survey analysis once the individual questionnaires have been manually entered onto the summary Excel sheet and grouped. Unfortunately, keying in the responses from each questionnaire manually into the Summary Excel Sheet takes a great amount of time, hence I need help with it.

I need a VBA code that will be able to transfer responses from each completed questionnaire onto the Summary Excel sheet, to save me the enormous time required to enter, manually, the individual responses from each questionnaire onto the Summary Excel sheet.

What I am hoping to achieve is this: Once an individual completes a questionnaire sheet which is on my computer), and I click a SAVE button, the vba code should be able to pick the responses from that questionnaire and transfer them to the Summary Excel Sheet in the following format, e.g.:

Name/ID of respondent Q1 Q2 Q3 Q4 (etc. to Q30)
XYZ Agree Disagree Strongly Agree Disagree


After transferring the responses from each questionnaire to the Summary Sheet in the above format, the code should SAVE the completed questionnaire in a designated folder named e.g. ‘Summarized’.

I will then do the required Analysis from the Summary Excel sheet. I believe I should be able to carry out the final analysis of the Summary Excel Sheet, using the Excel COUNT/COUNTA function.

Thanks all for your kind help.

Newqueen

[dflak]

Attach a sample of the final workbook and an individual questionnaire workbook. If you organize the data properly you can use pivot tables instead of COUNT and COUNTA.

[newqueen]

Dear dflak,

Thanks for your kind response.

As requested, I have attached a sample workbook that contains (a) a sample questionnaire, and (b) a sample Summary sheet.

The Questionnaire Sheet:

1. Because these are sample files, for the questionnaire, I have made up 5 questions and replicated them to give 22 questions.

2. The sheet has been populated with sample responses from a respondent.

3. On the questionnaire sheet, I have used ‘ticks’ to denote the responses under each item.

4. There is also a ‘code charts’ used to convert the responses to numbers.

The Summary Sheet:

have arranged the Summary sheet the way it is needed.

As indicated in my first Post, when the Summary Sheet is fully populated with all the responses, it becomes easier for us to carry out the final analysis. My problem is getting a vba code that would do the following:

a) After respondent completes the questionnaire as shown on Sheet 1 of the attached file, and a SAVE button is clicked, the vba code would transfer each respondent’s responses from the questionnaire on to the Summary Sheet in a horizontal format as shown in the example on the Summary Sheet attached.

b) After transferring the responses for each questionnaire sheet, the code would save the sheet (in the respondent’s Name/ID) in a designated folder named ‘Summarized’.

As we currently have some formula that we use to carry out the final analysis from the Summary Sheet, it may not be necessary to use Pivot Table to do that.

Once each questionnaire is completed on the computer and saved in a folder, we want to use vba code to automate the process of transferring the responses from each questionnaire to the Summary Sheet in the format shown in the attached Summary Sheet.

I should therefore be very grateful for help with this, please.

Thanks to you and all who will help with this.

Newqueen

[davsth]

well it would be simpler with different files but if you put the following in h7 =b5
and in h8 to copy down to h29 =IFERROR(MATCH("a",C8:G8,0),"")

then just copy the range to the master sheet with paste special transpose values

off out now so hopefully a start

[newqueen]

Dear Davsth,

Thanks very much for your kind suggestion.

Yours is a very useful suggestion. I would add that column that generates those 'number codes' to each of the questionnairs, and make the column 'hidden'.

However, with that, I would need a vba code which, when a 'SAVE' or any other designated button is clicked, the number codes in the hidden column H (rows 7 to 29) of each questionnaire will be transferred to the Summary Sheet with transposed values, and then, if possible, the questionnaire is removed from that folder and saved in a different folder, to show that it has been dealt with.

I hope someone can help with this.

Again, thanks for your useful suggestion.

Newqueen

[dflak]

I got carried away with this. I decided that newqueen isn't the only person that needs this kind of wheel, so I invented a more general application that allows you to develop a number of different kinds (series) of surveys, each with its own set of question and set of responses to select.

The program, with instructions is posted to this forum: https://www.excelforum.com/tips-and-...ml#post4804161

[newqueen]

Hi davsth,

In your Post #4 above, you provided the following formula: =IFERROR(MATCH("a", C8:G8,0),""). This is because the main questionnaire had 'ticks'. These were achieved by typing in the letter 'a' and formatting each one of them with Webdings.

I have decided to use radio buttons on the questionnaire instead of the ticks shown in the sample files. As you may know, there are a number of advantages of using radio buttons.

Please, I need your help to tweak your formula for me so that, on re-designing the questionnaire to use radio buttons, the formula will return the relevant code numbers as it currently does with ticks used on the questionnaire.

Thanks for your continued help.

Newqueen

[davsth]

Assuming that you grouped your radio buttons, its even easier as you dont need a formula, as 1 cell will contain a number 1 to 5 depending what option is selected, so you just make sure the linked cells, or a cell referencing the linked cell are all in a range, and again you can copy and paste this to a master sheet. VBAcode can be written to open all files in a folder and paste the results to a master file such as https://www.mrexcel.com/forum/excel-...workbooks.html

[newqueen]

Thanks very much. I shall explore the link that you provided.