Consolidating multiple rows into one single row - large data file

Hassrona

New Member
Joined
Sep 26, 2018
Messages
13
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
without representative example with structure and expected result I can say try PowerQuery with Group feature
 
Last edited:
Upvote 0
Thanks, I will try that option. If I have no luck, I will illustrate the structure of the report and the intended outcome. Regards
 
Upvote 0
Ok still having no luck, I am having issues attaching a file of any description here, I have pasted a section of the report below, if you can imagine the first extract has one row for each individual entry here (column headings included) -

Business GroupLast NameFirst NameUNIDEmail AddressQuestionnaire TitleSectionQuestionResponseDate
AdvisorySmithDavidxxxxxxxxxPropertyDisclosureWhat best describes your relationship to the business Owner08/19/2019
AdvisorySmithDavidxxxxxxxxxPropertyDisclosureName of businessMock08/19/2019
AdvisorySmithDavidxxxxxxxxxPropertyDisclosureNature of businessWholesales08/19/2019
AdvisorySmithDavidxxxxxxxxxPropertyDisclosureEmployee Count100+08/19/2019
AdvisorySmithDavidxxxxxxxxxPropertyDisclosureYear Founded200708/19/2019

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>


Now here is the intended outcome, if you can imagine this is contained within one single row instead of 5 single rows as above, the focus is on the question and response columns -

Business GroupLast NameFirst NameUNIDEmail AddressQuestionnaire TitleSectionQuestionResponseDate
AdvisorySmithDavidxxxxxxxxxPropertyDisclosureWhat best describes your relationship to the business ?
Name of business
Nature of business
Employee Count
Year Founded
Owner
Mock
Wholesales
100+
2007
08/19/2019

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
I resolved this using the text join formula. However the problem I have now is given there are over 17,000 rows and that each employee has a unique number of questions, is there anyway I can edit the text join formula to only join data for each employee on the list ? e.g. rows 2-8 belong to employee x, rows 9-12 belong to employee y...
 
Upvote 0
is that what you want?

Source:
Business GroupLast NameFirst NameUNIDEmail AddressQuestionnaire TitleSectionQuestionResponseDate
AdvisorySmithDavidxxxxxxxxxPropertyDisclosureWhat best describes your relationship to the businessOwner08/19/2019
AdvisorySmithDavidxxxxxxxxxPropertyDisclosureName of businessMock08/19/2019
AdvisorySmithDavidxxxxxxxxxPropertyDisclosureNature of businessWholesales08/19/2019
AdvisorySmithDavidxxxxxxxxxPropertyDisclosureEmployee Count100+08/19/2019
AdvisorySmithDavidxxxxxxxxxPropertyDisclosureYear Founded
2007​
08/19/2019

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Business Group", type text}, {"Last Name", type text}, {"First Name", type text}, {"UNID", type text}, {"Email Address", type text}, {"Questionnaire Title", type text}, {"Section", type text}, {"Question", type text}, {"Response", type any}, {"Date", type text}}),
    Group = Table.Group(Type, {"Business Group", "Last Name", "First Name", "UNID", "Email Address", "Questionnaire Title", "Section", "Date"}, {{"Count", each _, type table}}),
    ListQ = Table.AddColumn(Group, "Question", each Table.Column([Count],"Question")),
    ExtractQ = Table.TransformColumns(ListQ, {"Question", each Text.Combine(List.Transform(_, Text.From), "#(cr)#(lf)"), type text}),
    ListR = Table.AddColumn(ExtractQ, "Response", each Table.Column([Count],"Response")),
    ExtractR = Table.TransformColumns(ListR, {"Response", each Text.Combine(List.Transform(_, Text.From), "#(cr)#(lf)"), type text}),
    Reorder = Table.ReorderColumns(ExtractR,{"Business Group", "Last Name", "First Name", "UNID", "Email Address", "Questionnaire Title", "Section", "Count", "Question", "Response", "Date"})
in
    Reorder[/SIZE]

Result:
Business GroupLast NameFirst NameUNIDEmail AddressQuestionnaire TitleSectionQuestionResponseDate
AdvisorySmithDavidxxxxxxxxxPropertyDisclosureWhat best describes your relationship to the business
Name of business
Nature of business
Employee Count
Year Founded
Owner
Mock
Wholesales
100+
2007
08/19/2019

then select result table and use TopAlign and WrapText
 
Last edited:
Upvote 0
This looks very helpful thank you so much, I just want to confirm a few steps firstly as I am unfamiliar with the power query add on.

- Open a new excel file, click get data and then click from file, select the workbook in question (The name of the file is 'Original - OBA QA Report'). Import
- In the Navigator Pane, select the tab in question (XLQuestionAndAnswer), Transform Data
- A query already exists in the formula window, do I delete the existing query or copy and paste the query supplied underneath this or create a new query altogether ? If a new query is needed, how is this created with the data already imported from the above step ?
- Given the name of the file, the name of the tab - does the supplied query need to be altered in anyway to accommodate the name of the file and tab ?

Thanks again so much for your help, it will be an excellent development to get the data into the required format. Thanks
 
Upvote 0
How you get data into the sheet it's up to you

if you've data in the sheet just use Ctrl+T (check table name in Name Manager)
the rest you can do in PowerQuery Editor

if your table has name Table 1 you can copy M-code into Advanced Editor
if your table has different name - change it to appropriate name, eg. Table 2 or whatever
 
Last edited:
Upvote 0
The code works ok with no issues, however when I go to export the results of the query I get a new excel tab simply with the exported code, no actual changes to the report. This is my first experience with power query. Do you know why the code is getting exported instead of the edited report ?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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