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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Respondent[/TD]
[TD]Where do you keep the product?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a@gmail.com[/TD]
[TD]Home[/TD]
[TD][/TD]
[TD]Car[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b@gmail.com[/TD]
[TD][/TD]
[TD]Work[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c@gmail.com[/TD]
[TD]Home[/TD]
[TD][/TD]
[TD]Car[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d@gmail.com[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Rental Property[/TD]
[/TR]
</tbody>[/TABLE]

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!