I got 2+ tables and want to stack their data with filter. Each position have mark. Here PN1 and PN2. For example.
Table 1
Column A | Column B | Column C | Column D |
---|---|---|---|
PN1 | Ducts | 200x200 | 3 |
PN2 | Ducts | 300x300 | 5 |
Table 2
Column A | Column B | Column C | Column D |
---|---|---|---|
PN1 | TRSL | 200x200 | 1 |
PN2 | TRSL | 300x300 | 2 |
I want to achieve via VSTACK AND Filter so it can look like this
TableSUM
Column A | Column B | Column C | Column D |
---|---|---|---|
PN1 | Ducts | 200x200 | 3 |
PN1 | TRSL | 200x200 | 1 |
PN2 | Ducts | 300x300 | 5 |
PN2 | TRSL | 300x300 | 2 |
I got the first with PN1 but cant figure out PN2 so it would stack nicely.
Used
=vstack(filter(table1!A2:C6,table1!A2:A6="PN1,""),filter(table2!A2:C6,table1!A2:A6="PN1,""))
But filter can process arrays with + or OR function but it would just stack them without sequence.While i need this sequence to continue.
Just got a little bit lost.
Many thanks.
EDIT: Also. I there is no needed mark in the table it returns #N/A and take the whole row. Is there a way to fix this.
=LET(data,VSTACK(Table1,Table2),ucol,1,
uc,INDEX(data,,ucol),
SORTBY(data,XMATCH(uc,UNIQUE(uc))))