Consolidating multiple rows into one single row - large data file

[Hassrona]

I have a file consisting of over 17 thousand rows of information. The file lists out employee data in columns A-H. This portion of the file is ok. However in column I a list of questions are referenced vertically. Each question appears in a separate row. The employee's answer to the question is listed in Column J. Again Column J takes the same structure, each answer to each interview question appears in a separate row, side by side.

I have been asked to consolidate all of the individual interview questions into one single cell for each employee, same with their answers. Some employees were asked three questions, others five, etc there is no set pattern here. However each employee's data is separated by a blank row across the entire file.

Given there are over 17 thousand rows here, a macro is most likely needed. Any ideas on how this could be achieved. The reworked data should appear on a separate tab.

Thanks

 

[sandy666]

there is no any export
there is Close&Load and you can choose where your query table will be placed or you can stay with query table as connection
what you mean by : exported code?
code, or rather M-code is only in Advanced Editor, the rest what you see in PQ Editor is a Query table with transformed (or not) data
what you mean by: edited report?

maybe take a screenshot with the result and post here

 

[Hassrona]

Fantastic, I got the query to work. The report is now completed in line with what was needed. Thanks very much for your help, it really was invaluable

 

[sandy666]

you are welcome

have a nice day