Filtering Table B by the results in a column of Table A

Since this is my first time posting on here and being the VBA novice that I am, I'll try to be as descriptive as I can in what I'm asking. I'm not even sure what I'm asking for is plausible so if it's not disregard. (Please bear with the noob talk)

So I'm looking to filter data that's in my Table B based on the values in a column on Table A.

Let's say that Table A has 4 columns. I want Table B to be filtered by the values in column 4 of Table A. However, I've filtered Table A by a value that I'm NOT looking for in column 2.

Is this plausible?

Hi

quite possible. What do you want to do with the results of the filters? Put them somewhere?

Put up an example file that has some relevant data, and explain exactly what result you would get and where it would go.

rylo

Example.xlsm

Here's a sample file.

You'll notice I already have a Worksheet_Change macro in place. The orange table is Table A and Table B is what I want to filter based on the results from Table A Column 3. Which is "Country".

Hi

Get rid of the formulas in Dashboard!A6:C9 and then try

Please Login or Register  to view this content.

rylo

Hi

The filtering based on the formula driven table is giving problems being able to provide a contiguous criteria range, which is why I tried to build the table that is currently filled with formulas.

Rather than adding filters, add some clearing code like

Please Login or Register  to view this content.

immediately before the for statement in the code in #4.

If that really doesn't solve things, then set up an example file that will give the mixed length results (all the data in the original example file will really only come back with 2 rows!) so that we have something more like your real situation to work with.

rylo

Thanks! Works Perfectly when I .ClearContents the specified range.

Lastly, this code

Please Login or Register  to view this content.

seems to not be filtering out properly. Rather than filtering in place for what the results are in column "G5", It just hides all the rows from A22:G37762.

So I thought of using something like...

Please Login or Register  to view this content.

but the problem with this code is that the filtered table just gets filtered for the LAST value that's in column "G5".

Any Ideas?

Hi

Depends on what you mean by 2 ranges. If you can work out how to do it manually, then all you have to do is set up a similar criteria pattern for the code, and select the relevant range. Should filter out as required.

rylo

Works perfectly that way!!!!!

While doing this in my real project. I realized that when it filters out for what's in the ranges..

Please Login or Register  to view this content.

it doesn't filter out for an exact match.

As shown in your example provided from the excel file I gave you... you had it filtering out for "Country". Which worked perfectly until I came to rows that had both "US" and "US CA".

So my question is... is there anyway to add in the advance filter code that I only want EXACT matches from the specified criteriarange?

Hi

Found a couple of ways
1) Create a new column beside the Type column which has a formula =exact(B15,C6). You would have to modify it if you have multiple criteria so that is has an OR statement to cover off all options. Then you filter out for the TRUE results. Extremely cumbersome!

2) Another way I've found is to have the criteria in the form '=US Note that this would be exactly what you type into the cell. The single apostrophe, = and US. This should bring back only those that match US.

Try that in a manual example to be sure you follow what I mean, then see if you can update the code to produce the correct criteria format.

rylo

The second way works perfectly!!

I'll just have to figure out how I can bake the "=" to show as part of my results in the VBA without having to add an extra column.

You've been alot of help Rylo!!! Thanks for your time and patience!!

Hi

If I remember what I was doing properly then try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

rylo

Thatttttttttttttttt's whyyyyyyyyyy

I was trying it to be just

Please Login or Register  to view this content.

which wasn't working for me. I needed that " ' " before the equal sign. I hate it when it's something as simple as that and I didn't see it to begin with. =/

Thanks Rylo!!