How to make formula in excel circling - Stack Overflow

admin管理员组

文章数量:1401264

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

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.

• excel
• excel-formula
• filter
• vstack

Share Improve this question Follow edited Mar 24 at 14:51 Mayukh Bhattacharya 27.8k99 gold badges2929 silver badges4242 bronze badges asked Mar 24 at 13:13 Артур ДобриянАртур Добриян 2333 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

Stack Two Tables and Sort by Unique Column

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

An approach of the task

=SORT(VSTACK(Table6,Table7),{1,2})

Table6 and Table7 are the names of the table.

It will sort also on the second column.

本文标签： How to make formula in excel circlingStack Overflow