Hi,
I have created a questionnaire in HarvestYourdata. The data output i get, is as listed in the first 5 rows in the attached excel file.
The questionnaire includes a loop of 3 questions, that continous in a new column each time.
I would like the answers for each question to appear under each other, instead of next to eachother. I have manually created the output I would like, below the first 5 rows in Excel. Is there anyway to do this quickly?
As I am interested in Power Query, which i am learning, I've been playing with this all day. See the Pivot tab in the attached file. The PQ code I used is below:
Please Login or Register to view this content.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thank you AliGW, this worked wonders.
I have not worked with PQ before, but this seems to work. Am I right in assuming I can use this in a macro as well? Or can I create a PQ Masterfile?
As I said, I am only just beginning to learn myself how it works, so at the moment, I can't answer your added questions - sorry. What I would do is load my table into PQ, then copy the code into the code editor window and see if it works on the larger, real data set. If not, come back and we might be able to tweak it.
It is possible that someone with VBA skills could write you a macro that would do the same thing.
I just tried it with the real data set (63 columns). And it does not seem to work for me. Is there a way to include more columns in the PQ code?
Also, the dataset will probably vary from week to week. I cannot figure out if the code changes all data marked, or only a set number oc columns and rows?.
Again, thank you for your assistance.
I think, given your comment about the dataset changing, you are going to struggle with this, even with VBA, because any solution offered is going to rely on there being patterns in the data and fixed logic.
Shall I move this to the VBA section and see if anyone can help you there?
I am not sure if I explained myself proberly.
The data will always have the same layout, but the size will differ. It's basically the same 3 questions in a loop. Sometimes it will loop 20 times, other times it will loop more or less. It will always be 1 row of headlines, and 4 rows of data.
OK, well I'll move you to the VBA section and see if someone can help.
Thank you
I've put out a call for help - hopefully someone will pop along soon.
here's my hack and slash solutionPlease Login or Register to view this content.
created a worksheet to output your data
you can move it anywhere after the macro has run...or add this into code later should you want
so select original sheet (its a copy of your sheet minus your manual data entry at the bottom) and then run the code
i've commented as much of the code as possible so its hopefully easy to understand what i did where
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thank you for your help,
I ended up making it work on my own, with this piece of VBA
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks