How to merge rows based on value in one column in Excel?-CodePudding

I want to merge/concatenate rows if those rows have a duplicate values in one column. The merge step applies to more than one columns. In de table below I show an example of the problem:

 ----- -------- -------- -------- -------- -------- ---------- 
|     |   A    |   B    |   C    |   D    |   E    |     F    |
 ----- -------- -------- -------- -------- -------- ---------- 
| Dog |        |        | param1 |        |        |          |
 ----- -------- -------- -------- -------- -------- ---------- 
| Dog | param2 |        |        |        |        |          |
 ----- -------- -------- -------- -------- -------- ---------- 
| Dog |        |        |        |        |        |          |
 ----- -------- -------- -------- -------- -------- ---------- 
| Dog |        |        |        |        |        | param3   |
 ----- -------- -------- -------- -------- -------- ---------- 
| Cat |        | param5 |        |        |        |          |
 ----- -------- -------- -------- -------- -------- ---------- 
| Cat |        |        |        | param6 |        |          |
 ----- -------- -------- -------- -------- -------- ----------

I have about 4000 unique row values and about 30 columns. The duplicate row values are ranging from n=1 to n=10.

My preferred table:

 ----- -------- -------- -------- -------- -------- ---------- 
|     |   A    |   B    |   C    |   D    |   E    |     F    |
 ----- -------- -------- -------- -------- -------- ---------- 
| Dog | param2 |        | param1 |        |        | param3   |
 ----- -------- -------- -------- -------- -------- ---------- 
| Cat |        | param5 |        | param6 |        |          |
 ----- -------- -------- -------- -------- -------- ----------

Can this be done in Excel with some magic or do I need advanced stuff like python for this? I have tried multiple formula's with CONCATINATE but to no success.

Thank you in advance

CodePudding user response：

This can also be accomplished using Power Query, available in Windows Excel 2010 and Excel 365 (Windows or Mac)

To use Power Query

Select some cell in your Data Table
Data => Get&Transform => from Table/Range
When the PQ Editor opens: Home => Advanced Editor
Make note of the Table Name in Line 2
Paste the M Code below in place of what you see
Change the Table name in line 2 back to what was generated originally.
Read the comments and explore the Applied Steps to understand the algorithm

M Code

let

//change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],

//set all columns to data type text
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(Table.ColumnNames(Source), each {_, type text})),

//Group by Animal
//Then "Fill Up" each column and return only the first row
    #"Group Animal" = Table.Group(#"Changed Type","Animal", 
        {"Consolidate", each Table.FillUp(_,Table.ColumnNames(_)){0}}),

//Expand the grouped table and re-set the data types to text
    #"Expanded Consolidate" = Table.ExpandRecordColumn(#"Group Animal", "Consolidate",
        List.RemoveFirstN(Table.ColumnNames(#"Changed Type"))),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Consolidate", List.Transform(Table.ColumnNames(#"Expanded Consolidate"), each {_, type text}))
in
    #"Expanded Consolidate"

CodePudding user response：

Tricky. One way is to nest REDUCE() in another:

Formula in A8:

=DROP(REDUCE(0,UNIQUE(A1:A6),LAMBDA(a,b,VSTACK(a,REDUCE(b,SEQUENCE(6),LAMBDA(x,y,HSTACK(x,@SORT(INDEX(FILTER(B1:G6&"",A1:A6=b),,y),,-1))))))),1)

Or, a bit more dynamic:

=LET(r,A1:G6,s,TAKE(r,,1),t,DROP(r,,1)&"",DROP(REDUCE(0,UNIQUE(s),LAMBDA(a,b,VSTACK(a,REDUCE(b,SEQUENCE(COLUMNS(t)),LAMBDA(x,y,HSTACK(x,@SORT(INDEX(FILTER(t,s=b),,y),,-1))))))),1))

CodePudding user response：

Highlight the values you need to be grouped together then go to data, click on groe tab