Survey Question with Multiple Answers in Multiple Columns

[cdubs34]

Hello,

I have received a spreadsheet with survey results and many of the questions have multiple answers in multiple columns. For example:

Respondent	Where do you keep the product?
a@gmail.com	Home		Car
b@gmail.com		Work
c@gmail.com	Home		Car
d@gmail.com				Rental Property

<tbody>
</tbody>

As you can see the formatting makes it a bit difficult to do quick pivot table etc analysis. Could you please provide any recommendations on how best/most efficiently to handle this? Should I just create a new column that combines the 4 columns of answers here so for example a@gmail.com's response would be HomeCar? Thank you.

 

[pbornemeier]

Pivot tables do not do well when you use non-numeric values in the Values field. If you convert the data to this (which could be done by code):

		Home	Work	Car	Rental Property
a@gmail.com	0.5		0.5	
b@gmail.com		1		
c@gmail.com	0.5		0.5	
d@gmail.com				1

You will be able to extract more data for those questions with multiple answers.

 

[cdubs34]

Thank you!