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 |
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.