CODEWITHSUNDEEP
excelpowerquery
SUMguy
SUMguy

Reputation: 1673

Excel/PowerQuery - Filter table based on visible rows in another table

I have a table in an Excel sheet that has all my data (let's call it Table A). I want to use AutoFilter on this table and display only what's visible on a different table (Table B) on a different sheet. Table B only has a few columns from TableA - it does NOT have a month or date column.

How can I set it up so that when I filter Table A, the Table B on a different sheet is also filtered?

Note: removing the non-visible rows from TableB is an option, but I don't know how I would do that.

Upvotes: 1

Views: 974

Answers (1)

elliot svensson
elliot svensson

Reputation: 603

There are not too many ways for Table B to know whether a row in Table A is visible. One way is the SUBTOTAL function's "103" mechanism.

Follow these steps to use the =SUBTOTAL(103, ...) function on Table B to check if the corresponding row at Table A. Then filter against this result so that filters at Table B match what's done at Table A:

1) Identify or make a unique column in TableA and TableB which identifies a given row. If you don't have one, make a column that numbers the data from 1 to the number of rows. We'll say this is column A.

Make sure that the numbers identify the SAME DATA POINT in both tables... i.e. if 31 is January 2009 on Table A, then 31 needs to be January 2009 on Table B.

2) Add a new column in TableB with this formula in row 2 (because I wrote "A2" in the formula), and fill down: 

=SUBTOTAL(103, INDEX(**SELECT COLUMN A IN TableA**, MATCH(A2, **SELECT COLUMN A IN TableA [again]**,0)))

3) Filter TableB by the column you just created: 1 = shown, 0 = hidden.

Done!

Upvotes: 1

Related Questions