Separating responses from multiple response survey into separate columns

Question

Hi Everyone,

I have answers from a survey that has a new question each week. Customers can choose to answer each week or skip a week. The issue is that the system that provides the data provides it in the below format. So I can't easily tell where the drop off is coming from. How Can I create a data table that shows "Pascale's" answers for each week and notes weeks where they did not answer, in the example below you will see the person did not answer week 5, however they answered in week 6 again.

Answer 1

You may try Power Query.

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", type text}, {"Answer", type text}, {"Date", type datetime}, {"Member Name", type text}, {" Member Surname", type text}, {" Mobile Number", Int64.Type}, {" AccountID", Int64.Type}}), 

#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Date"}), 

#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Week]), "Week", "Answer") 

in

#"Pivoted Column"

Here is steps.

1. Remove date column
2. Select Week column>Povit Column>

Answer 2

Sure no problem.

Here is thelink

Answer 3

Could you share us a test file with some dummy data in it? Then I will try Excel function on it and send back to you.

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Answer 4

Hi There

Please see below

Answer 5

Could you share us an expected result? Then I will have a try.

It seems could be done with macro.