+ Reply to Thread
Results 1 to 12 of 12

Filter data by key in criteria

  1. #1
    Registered User
    Join Date
    08-15-2012
    Location
    Thailand
    MS-Off Ver
    Excel 2007
    Posts
    34

    Filter data by key in criteria

    Dear fellow

    I have a set of data that I need to filter for 3 criteria and check for all that match.
    I need a formula or a anyway to filter the data by just enter the criteria(plus auto dropdown would be really nice).
    How can I do that?

    the data is in Sheet1 and desire outcome is in Sheet2
    KeyInFilterData.xlsx

    Thank you in advance

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter data by key in criteria

    In F3 of your second sheet put this ARRAY formula and copy down.

    Please Login or Register  to view this content.
    Change ALL semi colons in my formula to Comma.
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Doha, State of Qatar
    MS-Off Ver
    excel 2003, 2007, 2010, 2016
    Posts
    124

    Re: Filter data by key in criteria

    Fotis: Its not working when we are entering any different combination.....Have a look on attached file...when i enter different criteria its not working
    Attached Files Attached Files

  4. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter data by key in criteria

    My dear friend, it's because there are many spaces in the first sheet.

    In this case we have to use TRIM function to get our result.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-15-2012
    Location
    Thailand
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Filter data by key in criteria

    Thanks a lot Fotis!
    The formula works very well. May I ask why you need "-1" in this formula "ROW(Sheet1!$A$2:$A$96)-1 " ?

    regards

  6. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter data by key in criteria

    You are welcome.

    Your data in Sheet1, start in second row(Row 1 is the header). So need to "tell" to excel in which row start to search. If your data start in row 3 then you use the formula in this way.

    "ROW(Sheet1!$A$2:$A$96)-2 "

  7. #7
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Doha, State of Qatar
    MS-Off Ver
    excel 2003, 2007, 2010, 2016
    Posts
    124

    Re: Filter data by key in criteria

    hey why you have not entered "two" directly in the above formula....?

  8. #8
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter data by key in criteria

    @ abubaniyan

    I don't understand the meaning of your question.

  9. #9
    Registered User
    Join Date
    08-15-2012
    Location
    Thailand
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Filter data by key in criteria

    Thank you, Fotis
    I'm clear now. :D

  10. #10
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter data by key in criteria

    You are welcome.

    Thanks dor the feedback and for reb*

  11. #11
    Forum Contributor
    Join Date
    12-14-2012
    Location
    Doha, State of Qatar
    MS-Off Ver
    excel 2003, 2007, 2010, 2016
    Posts
    124

    Re: Filter data by key in criteria

    why you had use "ROW(Sheet1!$A$2:$A$96)-2 " this formula.....can't you directly enter "2" in place of this formula...?

  12. #12
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter data by key in criteria

    You'll find the answer if you try it.

+ 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