ARRAYFORMULA with FILTER and CONCATENATE inside

Question

I have table like this. In real life it will be have more than 1000 rows.

Client | Group
--------------
Merry  | A
Merry  | C
Susan  | B
Mike   | C
Susan  | A
Joe    | A
Tom    | B
Tom    | C

And I have list of all clients which is filled from Google Form. I want to fill Group column with groups of every client using only arrayformula in the first row. My desired output is this:

Name    | Groups
----------------
Joe     | A
Tom     | B, C
Mike    | C
Merry   | A, C
Susan   | A, B
Peter   |

My best result yet is this:

Name    | Groups |   |   |   |   |   |   |   |
----------------------------------------------
Joe     |        |   |   |   |   | A |   |   |
Tom     |        |   |   |   |   |   | B | C |
Mike    |        |   |   | C |   |   |   |   |
Merry   |   A    | C |   |   |   |   |   |   |
Susan   |        |   | B |   | A |   |   |   |
Peter   |        |   |   |   |   |   |   |   |

Looks like it does what I want but I can't join all these columns into one. Formula is:

=ArrayFormula(IF(name<>"";IF(name=TRANSPOSE(FILTER(client;LEN(client)));TRANSPOSE(FILTER(group;LEN(group))););))

I used "name", "client" and "group" as named ranges according to column headers.

Sample sheet is here (view only): https://docs.google.com/spreadsheets/d/1VFnmyDTQhlCaANAvkzLgzKRKchPsGzFn4hDZYOoJF_M/edit?usp=sharing

Instead of sharing a Google Sheet spreadsheet with edit permissions for anyone, share it with view only in order to assure that readers will see the original spreadsheet. The named ranges are not set on the shared spreadsheet. — Rubén
Your solution is great, I can combine your formula and mine and get desired result with one formula cell even at big data set. Is there elegant workaround if group names will have space themselves? In this case your formula replaces every space with comma. I could solve it adding delimiter to every group name in additional tab but it's so ugly I don't want you even see it :) — student_alex
If your groups names have spaces, use the & operator to concatenate a separator. If you require further details post your follow up question as a new question. — Rubén

Rubén Rubén · Accepted Answer · 2016-09-11T23:24:06

Considering that the "best result" is on H3:O8 on the linked spreadsheet at this time.

The following formula returns the desired results:

=ArrayFormula(substitute(transpose(trim(query(transpose(H3:O8);"select *";ROWS(A:A))));" ";","))

Explanation

QUERY concatenates the rows values separating them by a space character. The above formula use this feature to concatenate the result got so far by the OP, then TRIM is used to remove the extra spaces and SUBSTITUTE to replace the remaining spaces by a comma.

ARRAYFORMULA with FILTER and CONCATENATE inside

2 Answers