+ Reply to Thread
Results 1 to 13 of 13

Advanced Filter Help - No Results in List Range

  1. #1
    Registered User
    Join Date
    09-24-2014
    Location
    CA
    MS-Off Ver
    2007
    Posts
    8

    Advanced Filter Help - No Results in List Range

    Hi all, I'm new to the forums here and could appreciate any bit of help I can get for my filtration issue.

    What I'm trying to do is apply a temporary filter from a range varying from 15-30 cells to a list of 2,000+ cells and copy over the supplementary data from my criteria. The Data should match verbatim to one another, both ranges are in table format, I included the top row of the table as per the prompt, and there are a few different filters by value set up within either workbook.
    I encounter my problem when applying the filter using the 'Advanced Filter' under the sort and filter ribbon of the data tab. What happens is my list range shrinks to nothing or I get a value that is completely irrelevant.

    See below for step-by-step pictures:
    http://imgur.com/EtB7vr8
    http://imgur.com/8cHKfKa
    http://imgur.com/VKHI6QV
    http://imgur.com/hhQVAlC

    Thanks so much in advance.

  2. #2
    Registered User
    Join Date
    09-24-2014
    Location
    CA
    MS-Off Ver
    2007
    Posts
    8

    Re: Advanced Filter Help - No Results in List Range

    Any help would be great, please. I've been trying to figure out what I'm not doing or doing wrong for a while.

  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Advanced Filter Help - No Results in List Range

    Can you post the worksheet instead of screencaps? I can't figure out what's going on.

  4. #4
    Registered User
    Join Date
    09-24-2014
    Location
    CA
    MS-Off Ver
    2007
    Posts
    8

    Re: Advanced Filter Help - No Results in List Range

    Sure thing,thanks so much for the help on this one, I've been looking through countless tutorials.

    To be clear, I need to marry the data of each city's Subcounty and County to the tab titled "list". I think the best way would be a simple copy paste using the Advanced Filter function, but it returns only one result or none at all. I'm sorry if this seems cryptic in any way.

  5. #5
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Advanced Filter Help - No Results in List Range

    what?

    I think you have gotten way ahead of yourself.

    What is your desired result? Are you trying to fill out the "Subcounty" and "County" from the other tab? Or vice versa?

    Which is the column you are pulling data FROM, which is the column you are pulling data TO, and what column are you using as the relational key?

    I don't understand how your data is working here.

  6. #6
    Registered User
    Join Date
    09-24-2014
    Location
    CA
    MS-Off Ver
    2007
    Posts
    8

    Re: Advanced Filter Help - No Results in List Range

    I'm going to simplify things a bit. All I want is to filter the cities of column A (RELATIONAL KEY) in the "List" (TO) tab using the cities of column C in the "Criteria" (FROM) tab.

    Are you saying there's a better way of importing the corresponding "County" and "Subcounty" data for each city?
    Last edited by jcroucier; 09-26-2014 at 05:04 PM. Reason: Inserted column co-ordinates

  7. #7
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Advanced Filter Help - No Results in List Range

    Yeah, I think a lookup operation like INDEX(MATCH) is what you're actually looking for. That would be dead easy, if your data isn't messy.

    Well, you might have to do some extra processing because "California" and "CA" are going to make them different text strings.

    Like, you keep talking about "filtering", but that seems like an incidental hack to what you actually want to do, which is one-to-one data matching, right?

    Like, in column A of the LIST tab, there are city names, and that matches one-and-only-one value in column C of the CITY tab, right?

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Advanced Filter Help - No Results in List Range

    Perhaps this will work for you. I added a couple of helper columns.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  9. #9
    Registered User
    Join Date
    09-24-2014
    Location
    CA
    MS-Off Ver
    2007
    Posts
    8

    Re: Advanced Filter Help - No Results in List Range

    Wow, I'm speechless, newdoverman both of you just made my life so much easier and my thanks are beyond words. Ben, you were exactly right, an indexing function is exactly what I want, especially since I need to do this for forty-nine other states!

    Is there a good resource to learn more about indexing? It seems like a formulaic language that I could easily pick up on with some basic explication. Can either of you recommend any valuable resources? I would also like to start learning about VBA and macros.

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Advanced Filter Help - No Results in List Range

    This is better: The file will require the XLSX format when saving. There is an error message otherwise.
    Enter this formula in List!B5 and copy down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Enter this in List!C5 and copy down:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The attached file doesn't require helper columns and uses wildcards to get the data required.
    Last edited by newdoverman; 09-26-2014 at 07:33 PM.

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Advanced Filter Help - No Results in List Range

    I have picked up a lot valuable tips from Youtube videos. Just search for Excel Index and Match and you will get a lot of hits. If you search for Excelisfun, you will be presented by a host of instructional videos (more than 1000) that have Excel files that match the videos for you to download and follow along.

    You can get a good "education" by following some of the solutions here especially those offered by the Moderators and Gurus and others who have given a good solution to a problem.

    When I see a good solution, I try and dissect the formula to see exactly what it is doing then I try to apply it to a problem that I make up myself. Then I practice with it until it "sinks in".

    Attack learning methodically trying not to take on too much at a time. When you get stuck, ask a question.

    Thank you for the feedback.

    If this solves your question, you should use the thread tools just above your first post to mark the thread solved.
    Last edited by newdoverman; 09-26-2014 at 07:44 PM.

  12. #12
    Registered User
    Join Date
    09-24-2014
    Location
    CA
    MS-Off Ver
    2007
    Posts
    8

    Re: Advanced Filter Help - No Results in List Range

    Sounds good! I'm looking forward to it.

    Thanks again for the help on this, take care and I'll speak with you again when I encounter more issues.

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Advanced Filter Help - No Results in List Range

    Thank you for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Advanced Filter results don't match auto filter
    By WaterWings in forum Excel General
    Replies: 1
    Last Post: 09-13-2012, 05:37 PM
  2. Results of List of Data with Advanced Filter
    By vtphilk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-07-2008, 06:21 PM
  3. [SOLVED] Advanced Filter w/ multiple sheet List Range
    By stickandrock in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-18-2006, 09:20 AM
  4. refresh advanced filter results
    By eagle in forum Excel General
    Replies: 2
    Last Post: 11-18-2005, 05:20 AM
  5. advanced filter a range:Advanced Filter function
    By Il Principe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

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