• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Filter multiple tables at once

sagarsangani

New Member
Hello,


Thank you for sharing some of the "slickest" excel tricks ever. I have been impressing my managers a lot lately after I discovered this website. But today, I am here because I'm stuck on something.


I have 2 tables which have at least 1 common category in them. I would like to filter Table2 based on how I filter Table1.


To make it simple, Table1 has Comp# and Cost and Table2 has Comp# and Comments. If I filter Table1 by Comp#, I would want Table2 to filter out automatically as well.


I'll appreciate any help!


Thanks :)
 

Hui

Excel Ninja
Staff member
Sagarsangani

You can use some VBA code to clear the criteria and then update multiple tables from a single source, E1 in this case

use something like

[pre]
Code:
Sub Update_Table()

tlookup = Range("E1").Value

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:=tlookup
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:=tlookup

End Sub
[/pre]

Adjust the Table Names and Field Numbers to suit
 

sagarsangani

New Member
I am not so good with VBA, but I was able to update the Range value and table names. I am not sure what I am supposed to do with the "Field"


Would it be the field I want to filter out? For instance, if the Comp# is in the first column of the table, would the field be 1?


If that is correct, I am not able to update the sheet.
 

Hui

Excel Ninja
Staff member
Field is the Field Number that you want to Filter by

1 = left most field

2 = second field from the left

etc
 

sagarsangani

New Member
Hello Hui,


My range lookup is D5, and I want to filter table 4 and 5, and the field I want to filter by is 1. I have tried to adjust the values but it doesn't seem to work for me.


Here's the code:


Sub Update_Table()


tlookup = Range("D5").Value


ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=1

ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=1


ActiveSheet.ListObjects("Table4").Range.AutoFilter Field:=1, Criteria1:=tlookup

ActiveSheet.ListObjects("Table5").Range.AutoFilter Field:=1, Criteria1:=tlookup


End Sub
 

Hui

Excel Ninja
Staff member
You just need to run the subroutine

Just add a button to your sheet and link the macro to it
 

sagarsangani

New Member
Awesome. Worked like a charm. Thank you so much! I have another question about designing my spreadsheet, I am wondering if few things are possible. Should I continue that here or start a new thread?
 

jofata

New Member
I was trying to do something similar.

I have one "master table" that I want to apply filters to. That will result in one list of Sample codes.

Sample Code:Implant in Torque (Ncm):
aFE322_V20051_2
58.39​
aFE322_V20051_4
58.93​
aFE322_V20051_6
51.23​
aFE322_V20051_7
58.34​
aFE322_V20051_8
60.2​

I would like to filter the other tables (in different sheets) based on the remaining samples in my "master table".

Column1Column2Column3
aFE322_V20051_1
0.02​
4.02​
aFE322_V20051_2
0.02​
4.02​
aFE322_V20051_3
0.02​
4.02​
aFE322_V20051_4
0.02​
4.02​
aFE322_V20051_5
0.02​
4.02​
aFE322_V20051_6
0.02​
4.02​
aFE322_V20051_7
0.02​
4.02​
aFE322_V20051_8
0.02​
4.02​

However I was not able to extract an array of the "filtered values". It was always reading out all 8 strings instead of only 6.

I would really appreciate some help :)
 

vletm

Excel Ninja
jofata
As written about nine years ago
Post new questions in a New Post please
and
before that
reread Forum Rules
please.
 
Top