Search code examples
google-sheets-formularowformulaspreadsheettextjoin

Googlesheets: joining multiple cells from multiple rows into one unique cell for each row

I’m working on a kanban board. So I want the kanban board to display elements from the dataset based on the condition of its Status (1 and 2, or really anything else like To do and Done).

I want the Kanban board to display all the elements present in the row (Task and Assignee) in one single cell for each row in the dataset.

I have the following table:

A B C
Task 1 Assignee 1 Status 1
Task 2 Assignee 2 Status 1
Task 3 Assignee 3 Status 3

I have the following formula:

=(TEXTJOIN(","; TRUE; QUERY(DATA!$A:$C;"SELECT * WHERE C='Status 1'")))

This works well, except that it concatenates the two rows into one unique cell. What I am looking for is rather to have the values of the two rows being into their unique cell, dependent on the Status’s status (1 or 2).

In other words, it gives me that:

A B C D
Task 1 Assignee 1 Status 1 Task 1, Assignee 1, Task 2, Assignee 2
Task 2 Assignee 2 Status 1
Task 3 Assignee 3 Status 3

But what I want is:

A B C D
Task 1 Assignee 1 Status 1 Task 1, Assignee 1
Task 2 Assignee 2 Status 1 Task 2, Assignee 2
Task 3 Assignee 3 Status 3
Solution

  • Different formula option you can try:

    =ARRAYFORMULA(IF(C2:C="Status 1",A2:A & ", " & B2:B,))

    Or try this formula:

    =BYROW(query(A2:C,"select A,B where C='Status 1'"),LAMBDA(row, JOIN(", ",row))).

    This will give you a column with all the results in consecutive rows with no blank rows in between if that is more what is desired.

    Screenshot