I want to stack multiple query result in a single array.
Table 1 to 3 share the same column structure. In column D, they are either “Y” or “N”. I want to have only the “Y” row to be shown. My anticipated results are shown below.
SAMPLE SHEET
Table1
| A | B | C | D |
|---|---|---|---|
| DIO | DASF | SDFA | Y |
| SADF | ERT | RWET | N |
| DGGF | SAF | FDG | Y |
| DFG | ERT | FXG | Y |
| FG | DFG | 34RW | N |
Table2
| A | B | C | D |
|---|---|---|---|
| SAF | H | HFH | N |
| GFH | W | FG | Y |
| N | GJ | WR | Y |
| FGH | GG | BNV | N |
| 2WEF | FDG | ZZEAS | N |
Table3
| A | B | C | D |
|---|---|---|---|
| FAG | HUIO | ERE45 | N |
| YTV3 | RTERTY | IO | N |
| GASFAS | YTUYTUI | UIP | Y |
| SADFSA | SA | IOP | Y |
Anticipated Result
| A | B | C | D |
|---|---|---|---|
| DIO | DASF | SDFA | Y |
| DGGF | SAF | FDG | Y |
| DFG | ERT | FXG | Y |
| A | B | C | D |
| GFH | W | FG | Y |
| N | GJ | WR | Y |
| A | B | C | D |
| GASFAS | YTUYTUI | UIP | Y |
| SADFSA | SA | IOP | Y |
Formula I tried (map lambda), but in vain.
=map({"Table1";"Table2";"Table3"},LAMBDA(colA,arrayformula(vstack(query(indirect(colA),"select * where Col4='Y'")))))
Representative Screenshot
Here’s one approach you may test out:
=reduce(tocol(,1),J8:J10,lambda(a,c,vstack(a,query(indirect(c),"Where Col4='Y'",1))))
0