I am trying to create a query that automatically hides questions based on their answer.
My end goal is to have the query to pull a file from my C drive as soon as I download it and automatically format it then create a new filtered sheet that only shows questions with a No or N/A answer.
The way the file is being downloaded currently is as shown below:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]ID[/TD]
[TD]q1[/TD]
[TD]q1 answer[/TD]
[TD]q1 explanation[/TD]
[TD]q2[/TD]
[TD]q2 answer[/TD]
[TD]q2 explanation[/TD]
[TD]q3[/TD]
[TD]q3 answer[/TD]
[TD]q3 explnation[/TD]
[TD]ID[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]1a[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]2b[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]3c[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]4d[/TD]
[TD]h[/TD]
[/TR]
</tbody>[/TABLE]
I want to reformat it so that every question gets pulled out and made into a new row with the rest of the data pulling with it as shown below:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]ID[/TD]
[TD]Q[/TD]
[TD]Question[/TD]
[TD]Answer[/TD]
[TD]Explanation[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]q1[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]q1[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD]q1[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]q1[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]q2[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]q2[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD]q2[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]q2[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]q3[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]q3[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD]q3[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]q3[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]h[/TD]
[/TR]
</tbody>[/TABLE]
I have created another sheet with 2 columns that lists all the questions (my file has 9) and corresponding q1/q2/etc as seen here"
[TABLE="width: 500"]
<tbody>[TR]
[TD]q1[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD]q2[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD]q3[/TD]
[TD]xx[/TD]
[/TR]
</tbody>[/TABLE]
I just don’t know how to tie these together and come up with the desired formatted table.
I’m working in Excel 2016. I also don’t seem to have Power Query. I tried downloading it, but it says I already have it installed. When I try to add it through the customize ribbon page, it does not show up.
I tried looking up VBA codes to use, but nothing seems to work for me and the Query tool bar does not have a lot to work with from what I can see. Everything has been done manually thus far.
Thanks in advance for all help!
My end goal is to have the query to pull a file from my C drive as soon as I download it and automatically format it then create a new filtered sheet that only shows questions with a No or N/A answer.
The way the file is being downloaded currently is as shown below:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]ID[/TD]
[TD]q1[/TD]
[TD]q1 answer[/TD]
[TD]q1 explanation[/TD]
[TD]q2[/TD]
[TD]q2 answer[/TD]
[TD]q2 explanation[/TD]
[TD]q3[/TD]
[TD]q3 answer[/TD]
[TD]q3 explnation[/TD]
[TD]ID[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]1a[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]2b[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]3c[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]4d[/TD]
[TD]h[/TD]
[/TR]
</tbody>[/TABLE]
I want to reformat it so that every question gets pulled out and made into a new row with the rest of the data pulling with it as shown below:
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]ID[/TD]
[TD]Q[/TD]
[TD]Question[/TD]
[TD]Answer[/TD]
[TD]Explanation[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]q1[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]q1[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD]q1[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]q1[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]q2[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]q2[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD]q2[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]q2[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]a[/TD]
[TD]q3[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]b[/TD]
[TD]q3[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]c[/TD]
[TD]q3[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]d[/TD]
[TD]q3[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]h[/TD]
[/TR]
</tbody>[/TABLE]
I have created another sheet with 2 columns that lists all the questions (my file has 9) and corresponding q1/q2/etc as seen here"
[TABLE="width: 500"]
<tbody>[TR]
[TD]q1[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD]q2[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD]q3[/TD]
[TD]xx[/TD]
[/TR]
</tbody>[/TABLE]
I just don’t know how to tie these together and come up with the desired formatted table.
I’m working in Excel 2016. I also don’t seem to have Power Query. I tried downloading it, but it says I already have it installed. When I try to add it through the customize ribbon page, it does not show up.
I tried looking up VBA codes to use, but nothing seems to work for me and the Query tool bar does not have a lot to work with from what I can see. Everything has been done manually thus far.
Thanks in advance for all help!