How can I make a query automatically create new rows out of specific columns?

gmu18

New Member
Joined
Feb 21, 2019
Messages
1
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!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top