Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Auto Filter Question

  1. #1
    Board Regular
    Join Date
    Dec 2012
    Posts
    131
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Auto Filter Question

    I have a large table of data. In column B, are the names of people in the table formatted "Last name, First name." I have over 1,000 people in the table. At times, I get a list of 10-15 people to look up. Instead of scrolling through and finding the names, I'd like to use the autofilter already set.

    I'm wondering- is there a macro where I could type in a name to the macro and it would automatically autofiler on the names typed in? I suppose I'd like an approximate match (no need to be exact). Also, some of the names I type might not yet be in the table so it'd have to account for this.

    A lot of talented people on here, I have a feeling this can be done. If it can't, please let me know why as it will be a good learning experience.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Auto Filter Question

    If you can select a cell in your table that contains the name to want to filter by, maybe this will help you:

    AutoFilter By Selection In Excel | Contextures Blog
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Dec 2012
    Posts
    131
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter Question

    Certainly helpful but the reason I thought VBA on this was to get the approximate match. I think the link's advice will only take exact matches.

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Auto Filter Question

    Have you tried recording a macro while filtering for an approximate match? That should give you some VBA code that you could edit to suit.
    Microsoft MVP - Excel

  5. #5
    Board Regular
    Join Date
    Dec 2012
    Posts
    131
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter Question

    Great advice. How do I filter for an approximate match?

  6. #6
    Board Regular
    Join Date
    Dec 2012
    Posts
    131
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter Question

    Quote Originally Posted by jac3130 View Post
    Great advice. How do I filter for an approximate match?
    Is it use auto filter and search using an asterisk?

  7. #7
    Board Regular
    Join Date
    Dec 2012
    Posts
    131
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter Question

    Here's the code I get when I record the macro:

    Code:
    Sub CustomAutoFilter()
    '
    ' CustomAutoFilter Macro
    '
    '
        ActiveSheet.Range("$A$4:$AI$1314").AutoFilter Field:=2, Criteria1:=Array( _
            "Last name, first name" , "Last name 2, first name 2", etc.), Operator:=xlFilterValues
    End Sub
    I've gotten read of the names for confidentiality purposes. Please help me figure this out.

  8. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Auto Filter Question

    You would Custom filter for Contains, but that's limited to two conditions.
    Microsoft MVP - Excel

  9. #9
    Board Regular
    Join Date
    Jan 2013
    Location
    Columbus, Ohio, United States of America
    Posts
    618
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter Question

    Jac, I think you could do something like this (not speaking from expertise here):
    Code:
    ActiveSheet.Range("$A$4:$AI$1314").AutoFilter Field:=2, Criteria1:=Array( _
        "Last name, *" , "Last name 2, *", etc.), Operator:=xlFilterValues
    I have to assume that we are not dealing with misspelled names, but nicknames, middle initials (or not), etc.
    We aims ta please!

    Rick Rothstein: Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    Hiker95: You can upload your workbook (with before and after worksheets) to Box Net
    AlphaFrog:
    Surround your pasted VBA code with code tags e.g.;
    [CODE ]your VBA code here[/CODE ]

  10. #10
    Board Regular
    Join Date
    Dec 2012
    Posts
    131
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto Filter Question

    It's giving me a syntax error. I'm trying to type (only showing part of the code)
    Code:
    Criteria1:Array( _ "Last name, *","Last name 2, *"
    Trying to get only approximate matches.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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