1

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

Mian
  • 137
  • 1
  • 12

2 Answers2

4

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

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • Superb... Great Idea... Thanks Chris Neilsen... It did the job perfectly fine, now i am using x name in LET function as a Filter Function with include arguments instead of tables, and it is performing great. Thanks a bunch man :) – Mian Aug 15 '21 at 10:00
2

You can use LET to do calculations in each table in one go.

For example:

=LET(x, Table1,
y, Table2,
x1, SUM(FILTER(x, range_table1=value)),
y1, SUM(FILTER(y, range_table2=value)),
x1+y1)

EDIT as suggested by @Scott Craner: If you want to append the data on top of each other, you can use SEQUENCE. EDIT a nice improvement suggested by @chris neilsen here shows a much cleaner way than I originally had by indexing on both a sequence of rows and columns.

=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)))
EDS
  • 2,155
  • 1
  • 6
  • 21
  • Got it, have edited answer to reflect that possibility, thanks – EDS Aug 13 '21 at 15:59
  • 1
    A somewhat cleaner version [here](https://stackoverflow.com/a/67880546/445425) – chris neilsen Aug 13 '21 at 19:43
  • Wow, that is definitely cleaner haha. I was trying to figure out for the longest time why something I couldn't just do ```IF(row_count – EDS Aug 13 '21 at 19:57
  • 1
    I had to find that out as well. Nice explanation from JMP here: https://stackoverflow.com/questions/68235283/index-rows-and-columns-unexpected-results – P.b Aug 14 '21 at 08:27
  • This is perfectly working fine but a bit issue i could not understand, if there are more than two tables, like if i add table3... or table4... I edited the question as well to get the best solution for many other people within the same question – Mian Aug 15 '21 at 08:33
  • I tried it '=LET(x, Table1, y, Table2, bc, Table3, rows1, ROWS(x), rows2, ROWS(y), rows3, ROWS(bc), myrows, SEQUENCE(rows1+rows2+rows3), mycols, SEQUENCE(1,COLUMNS(x)), IF(myrows<=rows1, x, INDEX(bc, myrows-rows1, mycols)))' and it did not work – Mian Aug 15 '21 at 08:45