Transform Survey responses with multiple answers on multiple choice

Question

I have survey response data, below is a sample:

IDWhich 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)123Asda, LidlAsda, Lidl124Asda, Tesco, LidlTesco, Lidl125Aldi, Asda, Tesco, LidlAldi, Asda, Tesco126Aldi, Tesco, Lidl, SainsburysAldi, Tesco, Lidl, Sainsburys127Aldi, Asda, Tesco, Lidl, SainsburysSainsburys

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:

IDQuestionAldiAsdaTescoLidlSainsburys123Which of the following Supermarkets are you aware of? (Please select all that apply)01010123Which of the following Supermarkets do you shop at? (Please select all that apply)01010124Which of the following Supermarkets are you aware of? (Please select all that apply)00110124Which of the following Supermarkets do you shop at? (Please select all that apply)11110125Which of the following Supermarkets are you aware of? (Please select all that apply)11110125Which of the following Supermarkets do you shop at? (Please select all that apply)11100126Which of the following Supermarkets are you aware of? (Please select all that apply)10111126Which of the following Supermarkets do you shop at? (Please select all that apply)10111127Which of the following Supermarkets are you aware of? (Please select all that apply)11111127Which of the following Supermarkets do you shop at? (Please select all that apply)00001

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 Answer

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!