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.