excelexcel-formulafiltervstack

How to make formula in excel circling

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.

Solution

  • Stack Two Tables and Sort by Unique Column

    enter image description here

    =LET(data,VSTACK(Table1,Table2),ucol,1,
    uc,INDEX(data,,ucol),
    SORTBY(data,XMATCH(uc,UNIQUE(uc))))