CODEWITHSUNDEEP
google-sheets
Antonio Padua
Antonio Padua

Reputation: 113

QUERY in ARRAYFORMULA to FILTER and JOIN

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

Upvotes: 1

Views: 133

Answers (1)

player0
player0

Reputation: 1

try this in row 1 on empty column:

={"Lista de processos e registros completos";""; ARRAYFORMULA(IF(E3:E="Tese"; 
 IFNA(VLOOKUP(C3:C; REGEXREPLACE(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(TRANSPOSE(
 QUERY(QUERY({C3:C&"¤"\IF(D3:D="";;"×"&D3:D)\ROW(D3:D)}; 
 "select Col1,max(Col2) group by Col1 pivot Col3"); 
 "offset 1"; 0));;9^9)); "¤"));"^×"; ); "×"; CHAR(10)); 2; 0)); ))}

enter image description here

demo spreadsheet

Upvotes: 2

Related Questions