Need to combine a number of rows in a file based on certain conditions

[Cantrecallmyusername]

Hi,

I have a large file which has a number of rows which I wish to combine into a smaller number based on certain conditions.
The data in column A is the key piece of information and is duplicated within the report but some of the other rows are split out with data tags which I wish to represent in one column.

The below is a example of my data set - the top three rows are condensed into 1 row which is based on the value of column b.
There will be a different amount of variables in the columns to the right of B and I would like a macro to loop through my file which will have ~2k rows each time

I hope this makes sense and appreciate your help with this one.

 

[lrobbo314]

One way using Power Query.

Book1 (version 1).xlsm
	A	B	C	D	E	F	G	H	I
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9
2	Test Data	Test DataBusiness Glossary	Missing_xyz	Business Glossary	Test DataBusiness Glossary	Data is Great	50	Under Review	This a a larger text field
3	Test Data	Test DataBusiness Glossary	Missing_abc	Business Glossary	Test DataBusiness Glossary	Data is Great	50	Reviewed	This a a larger text field
4	Test Data	Test DataBusiness Glossary	Incomplete_xyz	Business Glossary	Test DataBusiness Glossary	Data is Great	50	Under Review	This a a larger text field
5
6	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9
7	Test Data	Test DataBusiness Glossary	Missing_xyz Missing_abc Incomplete_xyz	Business Glossary	Test DataBusiness Glossary	Data is Great	50	Under Review Reviewed	This a a larger text field
Sheet7

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Group = Table.Group(Source, {"Column2"}, {{"Count", each _, type table [Column1=text, Column2=text, Column3=text, Column4=text, Column5=text, Column6=text, Column7=number, Column8=text, Column9=text]}}),
    CN = Table.ColumnNames(Source),
    Result = Table.TransformColumns(Group,{{"Count", (tr)=> List.Accumulate(CN,Source, (state,current) => Table.Distinct(Table.TransformColumns(state, {{current, each Text.Combine(List.Distinct(List.Transform(Table.Column(state,current),Text.From)),Character.FromNumber(10))}})))}}),
    Expand = Table.ExpandTableColumn(Result, "Count", {"Column1", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column1", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    Reorder = Table.ReorderColumns(Expand,CN)
in
    Reorder

 

[Cantrecallmyusername]

One way using Power Query.

Book1 (version 1).xlsm
	A	B	C	D	E	F	G	H	I
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9
2	Test Data	Test DataBusiness Glossary	Missing_xyz	Business Glossary	Test DataBusiness Glossary	Data is Great	50	Under Review	This a a larger text field
3	Test Data	Test DataBusiness Glossary	Missing_abc	Business Glossary	Test DataBusiness Glossary	Data is Great	50	Reviewed	This a a larger text field
4	Test Data	Test DataBusiness Glossary	Incomplete_xyz	Business Glossary	Test DataBusiness Glossary	Data is Great	50	Under Review	This a a larger text field
5
6	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9
7	Test Data	Test DataBusiness Glossary	Missing_xyz Missing_abc Incomplete_xyz	Business Glossary	Test DataBusiness Glossary	Data is Great	50	Under Review Reviewed	This a a larger text field
Sheet7

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Group = Table.Group(Source, {"Column2"}, {{"Count", each _, type table [Column1=text, Column2=text, Column3=text, Column4=text, Column5=text, Column6=text, Column7=number, Column8=text, Column9=text]}}),
    CN = Table.ColumnNames(Source),
    Result = Table.TransformColumns(Group,{{"Count", (tr)=> List.Accumulate(CN,Source, (state,current) => Table.Distinct(Table.TransformColumns(state, {{current, each Text.Combine(List.Distinct(List.Transform(Table.Column(state,current),Text.From)),Character.FromNumber(10))}})))}}),
    Expand = Table.ExpandTableColumn(Result, "Count", {"Column1", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column1", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    Reorder = Table.ReorderColumns(Expand,CN)
in
    Reorder

Thanks for the reply but power query is not an option for me, was hoping to achieve this with a macro.