+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Registered User
    Join Date
    01-15-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    95

    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?

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

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

    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

  3. #3
    Registered User
    Join Date
    01-15-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    95

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

    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".

  4. #4
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

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

    Hi

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

    Please Login or Register  to view this content.
    rylo

  5. #5
    Registered User
    Join Date
    01-15-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    95

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

    This is exactly the direction I want to be heading. However, not there just yet.

    I wanted to autofilter my Table B (Range("A14:C44")) unless there's a way that I can reset that table back to showing it's original data before I filtered it in place.


    So doing something like this...

    Please Login or Register  to view this content.
    To replace this...

    Please Login or Register  to view this content.

    Also, I wanted to add that the ".End(xlDOwn)" code only filters out for the last cell with a value. It doesn't actually filter for the rest of values above it in that column.
    Last edited by GreenTee; 01-17-2013 at 12:45 AM.

  6. #6
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

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

    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

  7. #7
    Registered User
    Join Date
    01-15-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    95

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

    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?

  8. #8
    Registered User
    Join Date
    01-15-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    95

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

    Nvm Rylo.

    I figured it out!

    My criteriarange("G5") wasn't matching with the cellvalue in table I was filtering. So basically one cell had "Country" while the cell in the table I was filtering had "Countries". So I just changed one so that they would both match and it works perfectly with the code you provided!

    Thanks a heap!!!!

    One last question.... how can i make it so that that code doesn't just filter for one range but 2 ranges?
    Last edited by GreenTee; 01-17-2013 at 01:13 PM.

  9. #9
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

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

    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

  10. #10
    Registered User
    Join Date
    01-15-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    95

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

    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?

  11. #11
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

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

    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

  12. #12
    Registered User
    Join Date
    01-15-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    95

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

    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!!

  13. #13
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

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

    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

  14. #14
    Registered User
    Join Date
    01-15-2013
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    95

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

    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!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1