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]

without representative example with structure and expected result I can say try PowerQuery with Group feature

 

[Hassrona]

Thanks, I will try that option. If I have no luck, I will illustrate the structure of the report and the intended outcome. Regards

 

[sandy666]

I will illustrate the structure of the report and the intended outcome.

Sure, I hope this will not be a picture

 

[Hassrona]

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) -

[TABLE="width: 1120"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Business Group[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]UNID[/TD]
[TD]Email Address[/TD]
[TD]Questionnaire Title[/TD]
[TD]Section[/TD]
[TD]Question[/TD]
[TD]Response[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Advisory[/TD]
[TD]Smith[/TD]
[TD]David[/TD]
[TD]xxxxx[/TD]
[TD]xxxx[/TD]
[TD]Property[/TD]
[TD]Disclosure[/TD]
[TD]What best describes your relationship to the business [/TD]
[TD]Owner[/TD]
[TD]08/19/2019[/TD]
[/TR]
[TR]
[TD]Advisory[/TD]
[TD]Smith[/TD]
[TD]David[/TD]
[TD]xxxxx[/TD]
[TD]xxxx[/TD]
[TD]Property[/TD]
[TD]Disclosure[/TD]
[TD]Name of business[/TD]
[TD]Mock[/TD]
[TD]08/19/2019[/TD]
[/TR]
[TR]
[TD]Advisory[/TD]
[TD]Smith[/TD]
[TD]David[/TD]
[TD]xxxxx[/TD]
[TD]xxxx[/TD]
[TD]Property[/TD]
[TD]Disclosure[/TD]
[TD]Nature of business[/TD]
[TD]Wholesales[/TD]
[TD]08/19/2019[/TD]
[/TR]
[TR]
[TD]Advisory[/TD]
[TD]Smith[/TD]
[TD]David[/TD]
[TD]xxxxx[/TD]
[TD]xxxx[/TD]
[TD]Property[/TD]
[TD]Disclosure[/TD]
[TD]Employee Count[/TD]
[TD]100+[/TD]
[TD]08/19/2019[/TD]
[/TR]
[TR]
[TD]Advisory[/TD]
[TD]Smith[/TD]
[TD]David[/TD]
[TD]xxxxx[/TD]
[TD]xxxx[/TD]
[TD]Property[/TD]
[TD]Disclosure[/TD]
[TD]Year Founded[/TD]
[TD]2007[/TD]
[TD]08/19/2019[/TD]
[/TR]
</tbody>[/TABLE]


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 -

[TABLE="width: 1120"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Business Group[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]UNID[/TD]
[TD]Email Address[/TD]
[TD]Questionnaire Title[/TD]
[TD]Section[/TD]
[TD]Question[/TD]
[TD]Response[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Advisory[/TD]
[TD]Smith[/TD]
[TD]David[/TD]
[TD]xxxxx[/TD]
[TD]xxxx[/TD]
[TD]Property[/TD]
[TD]Disclosure[/TD]
[TD]What best describes your relationship to the business ?
Name of business
Nature of business
Employee Count
Year Founded[/TD]
[TD]Owner
Mock
Wholesales
100+
2007[/TD]
[TD]08/19/2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[Hassrona]

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...

 

[sandy666]

is that what you want?

Source:
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Business Group[/td][td=bgcolor:#5B9BD5]Last Name[/td][td=bgcolor:#5B9BD5]First Name[/td][td=bgcolor:#5B9BD5]UNID[/td][td=bgcolor:#5B9BD5]Email Address[/td][td=bgcolor:#5B9BD5]Questionnaire Title[/td][td=bgcolor:#5B9BD5]Section[/td][td=bgcolor:#5B9BD5]Question[/td][td=bgcolor:#5B9BD5]Response[/td][td=bgcolor:#5B9BD5]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Advisory[/td][td=bgcolor:#DDEBF7]Smith[/td][td=bgcolor:#DDEBF7]David[/td][td=bgcolor:#DDEBF7]xxxxx[/td][td=bgcolor:#DDEBF7]xxxx[/td][td=bgcolor:#DDEBF7]Property[/td][td=bgcolor:#DDEBF7]Disclosure[/td][td=bgcolor:#DDEBF7]What best describes your relationship to the business[/td][td=bgcolor:#DDEBF7]Owner[/td][td=bgcolor:#DDEBF7]08/19/2019[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Advisory[/td][td]Smith[/td][td]David[/td][td]xxxxx[/td][td]xxxx[/td][td]Property[/td][td]Disclosure[/td][td]Name of business[/td][td]Mock[/td][td]08/19/2019[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Advisory[/td][td=bgcolor:#DDEBF7]Smith[/td][td=bgcolor:#DDEBF7]David[/td][td=bgcolor:#DDEBF7]xxxxx[/td][td=bgcolor:#DDEBF7]xxxx[/td][td=bgcolor:#DDEBF7]Property[/td][td=bgcolor:#DDEBF7]Disclosure[/td][td=bgcolor:#DDEBF7]Nature of business[/td][td=bgcolor:#DDEBF7]Wholesales[/td][td=bgcolor:#DDEBF7]08/19/2019[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Advisory[/td][td]Smith[/td][td]David[/td][td]xxxxx[/td][td]xxxx[/td][td]Property[/td][td]Disclosure[/td][td]Employee Count[/td][td]100+[/td][td]08/19/2019[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Advisory[/td][td=bgcolor:#DDEBF7]Smith[/td][td=bgcolor:#DDEBF7]David[/td][td=bgcolor:#DDEBF7]xxxxx[/td][td=bgcolor:#DDEBF7]xxxx[/td][td=bgcolor:#DDEBF7]Property[/td][td=bgcolor:#DDEBF7]Disclosure[/td][td=bgcolor:#DDEBF7]Year Founded[/td][td=bgcolor:#DDEBF7]

2007​

[/td][td=bgcolor:#DDEBF7]08/19/2019[/td][/tr]
[/table]

[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:
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Business Group[/td][td=bgcolor:#70AD47]Last Name[/td][td=bgcolor:#70AD47]First Name[/td][td=bgcolor:#70AD47]UNID[/td][td=bgcolor:#70AD47]Email Address[/td][td=bgcolor:#70AD47]Questionnaire Title[/td][td=bgcolor:#70AD47]Section[/td][td=bgcolor:#70AD47]Question[/td][td=bgcolor:#70AD47]Response[/td][td=bgcolor:#70AD47]Date[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Advisory[/td][td=bgcolor:#E2EFDA]Smith[/td][td=bgcolor:#E2EFDA]David[/td][td=bgcolor:#E2EFDA]xxxxx[/td][td=bgcolor:#E2EFDA]xxxx[/td][td=bgcolor:#E2EFDA]Property[/td][td=bgcolor:#E2EFDA]Disclosure[/td][td=bgcolor:#E2EFDA]What best describes your relationship to the business
Name of business
Nature of business
Employee Count
Year Founded[/td][td=bgcolor:#E2EFDA]Owner
Mock
Wholesales
100+
2007[/td][td=bgcolor:#E2EFDA]08/19/2019[/td][/tr]
[/table]

then select result table and use TopAlign and WrapText

 

[Hassrona]

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

 

[sandy666]

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

 

[Hassrona]

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 ?