CODEWITHSUNDEEP
google-sheets
Antonio Padua
Antonio Padua

Reputation: 113

Join and Filter in an arrayformula

I have a table like this

A B C D E
1 Co1 Te1 S1 R1 Yes
2 Co1 Te1 R2 No
3 Co1 Te1 R3 No
4 Te2 S2 R4 Yes
5 Te2 R5 No

I'm using the following drag down formula to join and filter D where A repeats

=IF(E="Yes"; JOIN(CHAR(10);FILTER(D1:D;A1:A=A1;D1:D<>""));"")

The result is the following

row/header| F
1| R1
1| R2
1| R3
2| R4
2| R5

There is a way to achieve the same result with an arrayformula?

Link to the real table (column T)

https://docs.google.com/spreadsheets/d/1fQpZcLPIlgv4Q62-2Hfg5gv-5AEZz0AQgmC9Pfm3Dxs/edit#gid=306985279

Upvotes: 0

Views: 65

Answers (1)

MattKing
MattKing

Reputation: 7773

You should know that in a situation like this, an Arrayformula will not make the sheet faster than a dragdown Filter.

That said, here is the traditional way to solve these problems. Make a new tab. Place this formula in cell B2.

=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(TRIM(QUERY(QUERY({BaseDoc!R7:R&"|"\CHAR(10)&BaseDoc!F7:F};"select MAX(Col2) where Col1<>'|' group by Col2 pivot Col1");;9^9)));"|";0)))

Those results should give you the table you need. If desired, you can do an Arrayformula(Vlookup( into the range to report back to your original tab.

Upvotes: 2

Related Questions