Export survey's multiple-length text answers to more compact format

[estillbham]

I have been given response to a questionnaire in an Excel spreadsheet. There are 12 questions and 25 responses. Because the answers to any particular question range from a few words to a few hundred words, the sheet ends up with a lot of white space. I have tried several ways of formatting the table, but the smallest printed output I can get is 75 pages.

Is there a way to export the data to a text file which will not waste so much space? I am thinking I want something like this:

Export Questions and Answers for each respondent:

Question 1
Answer 1
Q 2
A 2
...
Q 12
A 12
[page break]

I hope that makes sense.

Thanks.

 

[Hui]

If the questions are in the same order do you really need to export the questions for each person again?

so just use:

A 1
A 2
...
A 12
[page break]

 

[Peter Bartholomew]

The reason survey results are verbose it that they may need to accommodate questions such as

What else is important to you ...
Other ...
On as scale of 1 to 5 ...
Check all that apply ...​

Given that you have none of these, the text may be replaced by question numbers and option numbers for the column headings and [1,0] for the body of the table.

That can be set up by inserting a new table and incrementing the question number each time a question is found in the original. The option index increments in 1s but resets each time a new question is encountered. The body of the table is set to one if there is text in the original. Don't forget you need a list somewhere to link the numbers to the original text.

Beyond that, restructuring to make the results more compact is likely to be time wasted in that you are likely to make the analysis of the results more complicated rather than less. I would recommend analysing the results in Excel and not printing or exporting raw data as text.

It is also possible to analyse the results by using Power Query to transform the data but I assume you do not wish to go there!