De-rotate repeated questions

This processing questionnaire method to de-rotate repeated questions requires interviewers to ask respondents the same set of questions for 2+ products, or situations. Researchers refer to these as “trailer” surveys, when interviewers ask about products in a different order for each respondent (in rotation).

Analysis in QPSMR Companion copes with rotated data using grid variables and overlay tables. You might need to export the data to other packages which do not have this feature.

The program will enter data in the same order as it was collected on the printed questionnaire. As an example, a questionnaire collecting information about 3 different products – A, B and C (tested in any order).

Step 1

When defining Question Definitions files (QDF), before each set of repeated questions you should include a “Product tested” entry. Label each question text “1st product tested”, “2nd product tested”, and “3rd product tested”. This will establish the product names and their order before the respondents test each product.

You can then use this Question Definitions file (QDF) for data entry purposes. Information from the product test questionnaires is entered in the same order as it was collected on the printed document.

Step 2

Once all questionnaires’ data has been entered, we recommend that you back-up the completed data file. Then use Companion to open the data file and from the “Data” window, select [File] [Export Data].

Choose all questions which relate to the 1st tested product, including “product tested” entry and save to a CSV file. You should then follow the same procedure until you also have a CSV file for the 2nd tested, and 3rd tested products.

Step 3

Next using a spreadsheet program, such as Excel, insert the 3 individual CSV files into a new master CSV file. Sort this master file on the product being tested (using the column containing the “Product tested” entry), so that you re-order the information by product, rather than its test date.

IMPORTANT: Remember to highlight information in all the spreadsheet columns when sorting in a CSV file. If you do not do this, the data for each respondent will no longer be correct!

Step 4

The next step is to split the master CSV file back into 3 separate “product” CSV files, one for product A, another for Product B and another for product C. Each new “product” CSV file should include the original column headings, containing the entry names as the first row.

Step 5

Now copy the data entry Question Definitions file (QDF), so that you can make a correctly labelled “master” questionnaire to use for analysis purposes.

Using Companion, open the new “master” Question Definitions file (QDF) and use “Text find and replace” to change the text for all “1st product” entries to read “Product A”, all the ”2nd product” entries to read “Product B”, and all the ”3rd product” entries to read “Product C”.

Step 6

Using this new “master” Question Definitions file (QDF) with Companion, open the original data file and from the “Data” window, select [File] [Update from CSV file], choose the “Product A” CSV file. Notice that Companion automatically selects the [Portable format] radio button at the “Format of CSV file” dialog box, and select [OK].

The screen will then show a description of the “data hand over”; when you have checked the display select [Yes]. Companion will automatically feed the re-ordered information from the “Product A” CSV file, into the “Product A” entries in the master questionnaire.

Repeat this procedure for the “Product B” and “Product C” CSV files, to hand over the re-ordered data into the remaining product entries.

You can use this new “master” QDF file for analysis and export the data.

TIP: When using this method to de-rotate repeated questions, it is possible to “hand over” all the re-ordered product information using just one CSV file, rather than 3 separate product CSV files, if you prefer.

To do this you should sort 3 separate files, as described above, and then sort the re-ordered information again, this time by the serial number column. You need to take care, however, to include all relevant columns of the spreadsheet in the “sort” and you must check that all re-ordered product information, from each of the product files, uses the same serial number order.

You can then cut the “entry” information from the individual product files and paste it into one master product CSV file, correctly labelling the column for each question. Information from this master CSV file can then be “handed over” as described above.


To de-rotate repeated questions:

  • Copy QDF and data to master files and modify texts in new MASTER.QDF to include product names.
  • Open ORIGINAL.QDF and export a portable CSV file for each test (TEST1, TEST2 etc).
  • Open TEST1.CSV in your spreadsheet program and save as MASTER.CSV then open other CSVs and copy cells into MASTER.CSV.
  • Sort MASTER.CSV on TEST question.
  • From MASTER.CSV copy cells for Test 1 to TEST1.CSV.
  • From MASTER.CSV copy cells for Test 2 to TEST2.CSV, and so on.
  • Open MASTER.QDF and update the data from TEST1.CSV, then from TEST2.CSV and so on.