2

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

thomaswerk_94
  • 21
  • 3
  • This question can be best placed at [Super User](https://superuser.com/). For more info check out this [question](https://meta.stackexchange.com/questions/129598/which-computer-science-programming-stack-exchange-sites-do-i-post-on) – sitWolf Dec 16 '22 at 09:12
  • 3 steps: (1) filter unique keys (the first column in your case, that should be A actually) on new sheet, then (2) use FILTER function against each key, in each column. So cells will receive a RANGE result (and actually you may have more than 1 value in a cell, per your requirements) (3) convert range to text in cells, probably using CONCATENATE – Mixaz Dec 16 '22 at 09:36

3 Answers3

3

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"

enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
2

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

enter image description here

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))
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Thank you a lot for replying. I see that I currently have Microsoft Excel 2016 on my device which does not yet have the LET or DROP functions. I will try your code using Office365 Excel version on another device. – thomaswerk_94 Dec 16 '22 at 10:26
  • Either that or use it with Excel Web @thomaswerk_94. – JvdV Dec 16 '22 at 10:33
0

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