I have two different tables (Table1 and Table2 in two different excel sheets. (There could be more than two tables, like 3 or 4)
3rd sheet is for reporting/filtering. Now in third/reporting sheet i want to use Filter function in one cell and want the both tables from both sheets to be spilled out according to given include criteria for both tables
i.e
=FILTER(Table1 + Table2
OR
=SORT(FILTER(Table1,inclue,"")+FILTER(Table2,Inclue,"")) << it adds the two results if numbers
I hope i am clear in explanation I am trying to filter both (all) tables showing the result in one go and all i want to do the task is using formula bar, not the VBA. Please help me do that
The "Union" of two tables can be accomplished as
=LET(x, table1, y, table2,
rows1, ROWS(x), rows2, ROWS(y),
myrows, SEQUENCE(rows1+rows2),
mycols, SEQUENCE(1,COLUMNS(x)),
IF(myrows<=rows1, x, INDEX(y, myrows-rows1, mycols)))
For 3 tables it can be exetended as
=LET(x, Table1, y, Table2, z, Table3,
rows1, ROWS(x), rows2, ROWS(y), rows3, ROWS(z),
myrows, SEQUENCE(rows1+rows2+rows3),
mycols, SEQUENCE(1,COLUMNS(x)),
IF(myrows<=rows1, x,
IF(myrows<=(rows1+rows2),INDEX(y, myrows-rows1, mycols),
INDEX(z, myrows-rows1-rows2, mycols))))
For more than three tables, follow the same pattern