Data Analysis - Related Open Ended Questions with a yes/no response

[GasmanC]

[FONT=&quot]Hi,[/FONT]
[FONT=&quot]I've searched multiple different queries with google about how to accomplish this with excel but have had no luck.[/FONT]
[FONT=&quot]I have a question on a survey which reads:[/FONT]
[FONT=&quot]Q1. What advice was given during call?[/FONT]
[FONT=&quot]I plan on grouping the responses into themes/categories which I have found out how to accomplish, but the follow up question is:[/FONT]
[FONT=&quot]Q2. Advice followed? Yes or No.[/FONT]
[FONT=&quot]Which is relational to the advice that the respondent writes down. Followed by a their question:[/FONT]
[FONT=&quot]Q3. If advice not followed, Why?[/FONT]
[FONT=&quot]Again, I was going to place the responses into themes/categories. But this question is also relational to questions 1 and 2.[/FONT]
[FONT=&quot]How do I go about coding an excel spreadsheet so I can analyse the data from the relational responses.[/FONT]
[FONT=&quot]At the end of the day what I want is to theme the advice given, find out if it was followed, and if it was not followed, theme the reasons it wasn't.[/FONT]
[FONT=&quot]Many thanks, and kind regards,[/FONT]
[FONT=&quot]gasmanc[/FONT]

 

[GasmanC]

The data is along the lines of the following:

I'm not sure how I would make it into a pivot table. I suppose I would have to have the same respondent ID for each line of advice?

Thanks in advance.