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

Question

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

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.

Answer 1

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.

Answer 2

In Excel:

=TEXTJOIN(", ",TRUE,IF(A1:A9 = "XX",B1:B9,""))

As an array formula with Ctrl-Shift-Enter instead of Enter.

In Google Sheets:

=JOIN(", ",FILTER(B1:B9,A1:A9="XX"))