I want to merge data (name value/s) in different rows based on another column (name). See example below:

name | value

A | ab

A | ac

A | ad

B | bc

B | bd

I want the output to be like this (in rows and concatenated in one cell):

A ab ac ad

B bc bd

Is this possible with Google sheets query?

CodePudding user response：

I have added a new sheet ("Erik Help"). To achieve the exact results you wanted:

=ArrayFormula({"Result";IF(A2:A="",,IF(COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))>1,,VLOOKUP(A2:A&"*",REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({A2:B}, "Select MAX(Col2) where Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1"),, 9^9)),"~")),"[,\s] $",""),1,FALSE)))})

This is a more complex formula than I can typically share on this free forum, so I will need to leave the understanding of the formula to you and others who may be interested.

I also left a second formula, which shows only the aggregated list, as a "bonus" for you:

=ArrayFormula({"Aggregated List";REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY({A2:B}, "Select MAX(Col2) where Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1"),, 9^9)),"~")),"[,\s] $","")})