When trying to do analysis or import Socialsuite response data into other statistical packages, it may be required to turn a Socialsuite report from a flat formal into a pivoted matrix report.
The goal is to end up with each question as its own column and each row representing the individual beneficiary. The intersection of the column and the row would be the response.
Watch the Video
Here is how you do it in Excel.
- Turn your report into a Table using the shortcut Ctrl-T
- Select Insert > Pivot Table as a new sheet
- Select More Tables within the Pivot Table and select Yes to the popup
- Right-click on the table name and select Add Measure
- Add the formula =CONCATENATEX(Table3,[Response],”, “) where Table3 represents your table name and Response is the field to show as text.
- Add the Question field to the columns area and Beneficiary Name to the Rows.
Here is the result.
