Transform Survey responses with multiple answers on multiple choice

[willd9]

I have survey response data, below is a sample:

ID	Which of the following Supermarkets are you aware of? (Please select all that apply)	Which of the following Supermarkets do you shop at? (Please select all that apply)
123	Asda, Lidl	Asda, Lidl
124	Asda, Tesco, Lidl	Tesco, Lidl
125	Aldi, Asda, Tesco, Lidl	Aldi, Asda, Tesco
126	Aldi, Tesco, Lidl, Sainsburys	Aldi, Tesco, Lidl, Sainsburys
127	Aldi, Asda, Tesco, Lidl, Sainsburys	Sainsburys

In order to analyse the responses, I want to create a column for each of the possible answers (Aldi, Asda, Tesco, Lidl, Sainsburys) and mark wherever the responder has selected them. The below would be an example of the output I want to see:

ID	Question	Aldi	Asda	Tesco	Lidl	Sainsburys
123	Which of the following Supermarkets are you aware of? (Please select all that apply)	0	1	0	1	0
123	Which of the following Supermarkets do you shop at? (Please select all that apply)	0	1	0	1	0
124	Which of the following Supermarkets are you aware of? (Please select all that apply)	0	0	1	1	0
124	Which of the following Supermarkets do you shop at? (Please select all that apply)	1	1	1	1	0
125	Which of the following Supermarkets are you aware of? (Please select all that apply)	1	1	1	1	0
125	Which of the following Supermarkets do you shop at? (Please select all that apply)	1	1	1	0	0
126	Which of the following Supermarkets are you aware of? (Please select all that apply)	1	0	1	1	1
126	Which of the following Supermarkets do you shop at? (Please select all that apply)	1	0	1	1	1
127	Which of the following Supermarkets are you aware of? (Please select all that apply)	1	1	1	1	1
127	Which of the following Supermarkets do you shop at? (Please select all that apply)	0	0	0	0	1

I'm assuming there'll be a Cross Tab tool used at some point, but I'm a bit of a novice when it comes to that tool. Any ideas?

[DataNath · Accepted solution]

Hey @willd9 here's one way you could go about it. Here we:

1) Transpose the data, grouping by each ID

2) Split the comma separated entries so we get a row for each

3) Use a dummy 1 so we can perform counts

4) Cross-Tab back, grouping by ID and question, using the supermarket names as headers and then doing a sum of our 1s

5) Select/Multi-Field Formula are just for some general clean up of header names and results

Hope this helps, please do shout if you have any questions!

[ChrisTX]

Try the attached workflow.

 

Chris