Home

Is it possible to make a query automatically create new rows out of specific columns?

%3CLINGO-SUB%20id%3D%22lingo-sub-362948%22%20slang%3D%22en-US%22%3EIs%20it%20possible%20to%20make%20a%20query%20automatically%20create%20new%20rows%20out%20of%20specific%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362948%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20am%20trying%20to%20create%20a%20query%20that%20automatically%20hides%20questions%20based%20on%20their%20answer.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EMy%20end%20goal%20is%20to%20have%20the%20query%20to%20pull%20a%20file%20from%20my%20C%20drive%20as%20soon%20as%20I%20download%20it%20and%20automatically%20format%20it%20then%20create%20a%20new%20filtered%20sheet%20that%20only%20shows%20questions%20with%20a%20No%20or%20N%2FA%20answer.%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThe%20way%20the%20file%20is%20being%20downloaded%20currently%20is%20as%20shown%20below%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CDIV%20class%3D%22cms_table%22%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EID%3C%2FTD%3E%3CTD%3EID%3C%2FTD%3E%3CTD%3Eq1%3C%2FTD%3E%3CTD%3Eq1%20answer%3C%2FTD%3E%3CTD%3Eq1%20explanation%3C%2FTD%3E%3CTD%3Eq2%3C%2FTD%3E%3CTD%3Eq2%20answer%3C%2FTD%3E%3CTD%3Eq2%20explanation%3C%2FTD%3E%3CTD%3Eq3%3C%2FTD%3E%3CTD%3Eq3%20answer%3C%2FTD%3E%3CTD%3Eq3%20explnation%3C%2FTD%3E%3CTD%3EID%3C%2FTD%3E%3CTD%3EID%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3Ea%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3E1a%3C%2FTD%3E%3CTD%3Ee%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Eb%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3E2b%3C%2FTD%3E%3CTD%3Ef%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Ec%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3E3c%3C%2FTD%3E%3CTD%3Eg%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Ed%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3E4d%3C%2FTD%3E%3CTD%3Eh%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FDIV%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3EI%20want%20to%20reformat%20it%20so%20that%20every%20question%20gets%20pulled%20out%20and%20made%20into%20a%20new%20row%20with%20the%20rest%20of%20the%20data%20pulling%20with%20it%20as%20shown%20below%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CDIV%20class%3D%22cms_table%22%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EID%3C%2FTD%3E%3CTD%3EID%3C%2FTD%3E%3CTD%3EQ%3C%2FTD%3E%3CTD%3EQuestion%3C%2FTD%3E%3CTD%3EAnswer%3C%2FTD%3E%3CTD%3EExplanation%3C%2FTD%3E%3CTD%3EID%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3Ea%3C%2FTD%3E%3CTD%3Eq1%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Ee%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Eb%3C%2FTD%3E%3CTD%3Eq1%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Ef%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Ec%3C%2FTD%3E%3CTD%3Eq1%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Eg%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Ed%3C%2FTD%3E%3CTD%3Eq1%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Eh%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3Ea%3C%2FTD%3E%3CTD%3Eq2%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Ee%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Eb%3C%2FTD%3E%3CTD%3Eq2%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Ef%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Ec%3C%2FTD%3E%3CTD%3Eq2%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Eg%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Ed%3C%2FTD%3E%3CTD%3Eq2%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Eh%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3Ea%3C%2FTD%3E%3CTD%3Eq3%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Ee%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3Eb%3C%2FTD%3E%3CTD%3Eq3%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Ef%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3Ec%3C%2FTD%3E%3CTD%3Eq3%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Eg%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3Ed%3C%2FTD%3E%3CTD%3Eq3%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3CTD%3Eh%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FDIV%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20created%20another%20sheet%20with%202%20columns%20that%20lists%20all%20the%20questions%20(my%20file%20has%209)%20and%20corresponding%20q1%2Fq2%2Fetc%20as%20seen%20here%22%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22cms_table%22%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3Eq1%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eq2%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Eq3%3C%2FTD%3E%3CTD%3Exx%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FDIV%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%3EI%20just%20don%E2%80%99t%20know%20how%20to%20tie%20these%20together%20and%20come%20up%20with%20the%20desired%20formatted%20table.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%E2%80%99m%20working%20in%20Excel%202016.%20I%20also%20don%E2%80%99t%20seem%20to%20have%20Power%20Query.%20I%20tried%20downloading%20it%2C%20but%20it%20says%20I%20already%20have%20it%20installed.%20When%20I%20try%20to%20add%20it%20through%20the%20customize%20ribbon%20page%2C%20it%20does%20not%20show%20up.%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20tried%20looking%20up%20VBA%20codes%20to%20use%2C%20but%20nothing%20seems%20to%20work%20for%20me%20and%20the%20Query%20tool%20bar%20does%20not%20have%20a%20lot%20to%20work%20with%20from%20what%20I%20can%20see.%20Everything%20has%20been%20done%20manually%20thus%20far.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EThanks%20in%20advance%20for%20all%20help!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-362948%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363329%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20make%20a%20query%20automatically%20create%20new%20rows%20out%20of%20specific%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363329%22%20slang%3D%22en-US%22%3EI%20know%2C%20there%20won't%20be%20a%20powerquery%20tab%20in%202016%2C%20as%20it%20is%20integrated%20into%20the%20data%20tab.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-362985%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20make%20a%20query%20automatically%20create%20new%20rows%20out%20of%20specific%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362985%22%20slang%3D%22en-US%22%3E%3CP%3EI%20meant%20to%20say%20the%20tab%20%22Power%20Query%22%20isn't%20there.%20I%20have%20been%20using%20the%20query%20via%20the%20Data%20tab%2C%20but%20I%20can't%20figure%20out%20how%20to%20create%20the%20new%20rows%20through%20the%20query.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-362975%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20make%20a%20query%20automatically%20create%20new%20rows%20out%20of%20specific%20columns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-362975%22%20slang%3D%22en-US%22%3EPowerQuery%20is%20there%20alright%2C%20it%20is%20on%20the%20Data%20tab%20and%20they%20stupidly%20called%20it%20%22Get%20%26amp%3B%20Transform%22.%3C%2FLINGO-BODY%3E
gmu18
New Contributor

Hello,

 

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:

IDIDq1q1 answerq1 explanationq2q2 answerq2 explanationq3q3 answerq3 explnationIDID
1axxxxxxxxxxxxxxxxxx1ae
2bxxxxxxxxxxxxxxxxxx2bf
3cxxxxxxxxxxxxxxxxxx3cg
4dxxxxxxxxxxxxxxxxxx4dh


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:

IDIDQQuestionAnswerExplanationID
1aq1xxxxxxe
2bq1xxxxxxf
3cq1xxxxxxg
4dq1xxxxxxh
1aq2xxxxxxe
2bq2xxxxxxf
3cq2xxxxxxg
4dq2xxxxxxh
1aq3xxxxxxe
2bq3xxxxxxf
3cq3xxxxxxg
4dq3xxxxxxh


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"

q1xx
q2xx
q3xx


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!

3 Replies
PowerQuery is there alright, it is on the Data tab and they stupidly called it "Get & Transform".

I meant to say the tab "Power Query" isn't there. I have been using the query via the Data tab, but I can't figure out how to create the new rows through the query. 

I know, there won't be a powerquery tab in 2016, as it is integrated into the data tab.
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies