Given a table like this one
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Controvérsia | 1 | Controvérsia 1 | Tese | |
| 2 | Controvérsia | 1 | Controvérsia 1 | (2017/0154068-7) | Rejeitado |
| 3 | Controvérsia | 1 | Controvérsia 1 | (2016/0278523-9) | Rejeitado |
| 4 | Controvérsia | 2 | Controvérsia 2 | Tese | |
| 5 | Controvérsia | 2 | Controvérsia 2 | (2017/0201971-0) | Sobrestado |
| 6 | Controvérsia | 2 | Controvérsia 2 | (2016/0290114-1) | Sobrestado |
| 7 | Controvérsia | 2 | Controvérsia 2 | (2016/0309592-1) | Sobrestado |
| 8 | Controvérsia | 3 | Controvérsia 3 | Tese | |
| 9 | Controvérsia | 3 | Controvérsia 3 | (2016/0313842-4) | Afetado |
| 10 | Controvérsia | 3 | Controvérsia 3 | (2016/0305954-5) | Afetado |
In which Col C has the following arrayformula
=ARRAYFORMULA(IF(A2:A<>"",A2:A&" "&B2:B, ""))
As long rows in Col C has the same string Col E should return all its correspondent strings in the same Col F row (the one in which Col E ="Tese")
So, as rows 1 to 3 of Col A strings are "Controvérsia 1", row 1 in Col F should return (with line breaks):
(2017/0154068-7)
(2016/0278523-9)
(2016/0278498-6)
I'm using a dropdown formula to do the job, which is working fine:
=IFERROR(IF(E3="Tese", JOIN(CHAR(10),FILTER(D3:D,C3:C=C3,D3:D<>"")),""))
But it would be easier if it could be replaced by an ARRAYFORMULA (the table is over 4,000 rows!).
I think it is possible to do it using a QUERY, but I could not write the formula down.
The real table is here: LINK
