excelexcel-formulagoogle-sheetsarray-formulasapple-numbers

How can I concatenate a range of cells according to adjacent value?

In the following table, I'd like to concatenate the numbers of column B, according to string in column A:

My table

For example, all the numbers adjacent to XX would be copied to another cell in this format: 12, 12, 21, 23, 31, 32.

I can manually concatenate the whole of column B using:

=ArrayFormula(concatenate(B2:D&", "))

But I need to split those values according to column A.

I can manipulate this data in Google Sheets, Excel or Numbers.

Solution

  • Try this:

     =ARRAYFORMULA({UNIQUE(A1:A5), TRIM(TRANSPOSE(QUERY(IF(transpose(UNIQUE(A1:A5))=A1:A5,B1:B5&",",""),,500000)))})

    I hope your rows don't exceed 500000 and you don't have blanks.