+ Reply to Thread
Results 1 to 12 of 12

Filter data by key in criteria

  1. 01-24-2013, 02:17 AM #1
    pabloponza
    pabloponza is offline
    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
    Reply With QuoteRegister To Reply
  2. 01-24-2013, 03:49 AM #2
    Fotis1991
    Fotis1991 is offline
    Forum Expert 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.
    Reply With QuoteRegister To Reply
  3. 01-24-2013, 04:31 AM #3
    abubaniyan
    abubaniyan is offline
    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
    Reply With QuoteRegister To Reply
  4. 01-24-2013, 04:47 AM #4
    Fotis1991
    Fotis1991 is offline
    Forum Expert 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
    Reply With QuoteRegister To Reply
  5. 01-27-2013, 11:22 PM #5
    pabloponza
    pabloponza is offline
    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
    Reply With QuoteRegister To Reply
  6. 01-28-2013, 03:04 AM #6
    Fotis1991
    Fotis1991 is offline
    Forum Expert 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 "
    Reply With QuoteRegister To Reply
  7. 01-28-2013, 06:53 AM #7
    abubaniyan
    abubaniyan is offline
    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....?
    Reply With QuoteRegister To Reply
  8. 01-28-2013, 07:09 AM #8
    Fotis1991
    Fotis1991 is offline
    Forum Expert 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.
    Reply With QuoteRegister To Reply
  9. 01-28-2013, 11:50 PM #9
    pabloponza
    pabloponza is offline
    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
    Reply With QuoteRegister To Reply
  10. 01-29-2013, 02:43 AM #10
    Fotis1991
    Fotis1991 is offline
    Forum Expert 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*
    Reply With QuoteRegister To Reply
  11. 01-29-2013, 04:39 AM #11
    abubaniyan
    abubaniyan is offline
    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...?
    Reply With QuoteRegister To Reply
  12. 01-29-2013, 04:41 AM #12
    Fotis1991
    Fotis1991 is offline
    Forum Expert 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 With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1