1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: Is there any formula that would do Autofilter's functionality as a formula

Discussion in 'Business Applications' started by LocYip, Feb 3, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. LocYip

    LocYip Thread Starter

    Joined:
    Feb 3, 2010
    Messages:
    2
    Hi, I need to do the following:

    Search array
    1 a 12 asdas
    2 b 121 agrsfsa
    3 c 3 qwgds
    4 b 131 erwer
    5 e 4123 q3eqt

    need to return something like this
    lookup value b

    answer should look like this

    2 b 121 agrsfsa
    4 b 131 erwer

    index, match can give me one row, however what could return multiple values

    Thanks in advance for your help.
    Cheers
     
  2. sdc872

    sdc872

    Joined:
    Feb 3, 2010
    Messages:
    7
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Try this. VBA code.
    I just put it together, simple but works.:)
     

    Attached Files:

  4. LocYip

    LocYip Thread Starter

    Joined:
    Feb 3, 2010
    Messages:
    2
    Thanks folks for the quick reply.

    @sdc872 - I already figured out the way to return one row using index/match - so that is not my challenge. what I am a looking is for a table return. Pls suggest if you think I missed a point there.

    @Keebellah - Thanks good code. After going through the code - i used to know C++ pretty well. i figured u use the autofilter in it again.. however not sure i understand the code completely.

    i've devised a formula based solution but its very ugly

    get result 1 using index/match
    search again in the smaller matrix i.e. reducing the search matrix by the row on which result 1 was found.

    a more elegant result would be effective.

    looking to get a handle on index/match using this too.
    cheers


    p.s.: awesome forum . cheers
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    If you look at the code you'll understand it.
    Once you filter on lets sya 'b' you can use the autofilter buttons to refine for the other columns.
    I built this macro from a recorded macro.
    Try this. Make sure the autofilter is off.

    Record a new macro, give it a name and do the thing you need.
    After it's completed, stop the recording.

    In the vba project you can see the code built and then you can build on from there.
    That's tha way I started many solutions and yes, this forum is great.(y)
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    I made a small change but it makes it flexible.

    If you double-click on the first row, you'll get the input dialog.

    The column you are double clicking in will de the column to apply the filter to.
    If the column contains text in *w* or *qs* and all the cells containing a 'w' or 'qs' in them will be filtered.

    This only works for numbers

    The code is on worksheet level ;)
     

    Attached Files:

  7. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Another very powerful way to getwhat you want is to use Data, Advanced Filter.

    This Allows you to set up your list in columns A, B, C, D in example
    Then set up criteria F, G ,H ,I in example. This can contain multiple rows of queries.
    Then extract the results to another location K, L , M, N in example

    You need to have field headings as in Row 1 of example. These field heading are copied to the oteher locations, so that the spellings are identical.

    You then click onA1
    Then Data, Filter, Advanced Filter
    Click on Copy to another location
    Then set your list range A1 to D6 as in example
    Then set your Criteria Range F1 to I2 as in example (This can be more than 2 rows if you have mutiple queries (an OR situation)
    Then set your copy to range K1 to N1
    Click on Ok and your results will appear under K1 to N1

    It is a very powerful tool and well worth knowing even if you don't need it at the moment.
     

    Attached Files:

  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/900050

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice