+ Reply to Thread
Results 1 to 2 of 2

Filter rows by more than two OR criterias and extracting results into a new sheet?

  1. #1
    Registered User
    Join Date
    05-21-2014
    Posts
    1

    Filter rows by more than two OR criterias and extracting results into a new sheet?

    Dear Friends,

    I need to filter a large database using multiple OR criterias.

    Database is CUSTOMER NAMES in each row with CONSUMED PRODUCTS following in each cell.

    I want to filter two or more products and list customer names in sheet2. Like listing customers using salt or pepper
    Filter criterias will be entered into cells in sheet2 with results shown below and I want search to be active and alive just like vlookup function.

    Sheet1
    CUSTOMER NAMES---CONSUMED PRODUCT---CONSUMED PRODUCT---CONSUMED PRODUCT
    GEORGE--------------SUGAR-----------------SALT
    MICHAEL-------------PEPPER-----------------CHILI
    JACK-----------------BREAD------------------SALT-----------------COFFEE

    Could you please support me with this?
    Regards,

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,568

    Re: Filter rows by more than two OR criterias and extracting results into a new sheet?

    Take a look at this. I used a helper column on Sheet 1 which you can hide if you like. It counts the times that the products on sheet2 appear in that row. You will need to modify this for more products.

    Then in sheet2, A5, this arrayed formula

    =IFERROR(INDEX(Sheet1!A$2:A$20,SMALL(IF(Sheet1!$E$2:$E$20>0,ROW($E$2:$E$20)-1),ROWS($A$1:$A1))),"")

    copied across and down.
    Array Formula..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ 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. Pasting filter results to another sheet
    By ceejay22 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-11-2013, 10:28 PM
  2. Filter a sheet and copy results to another sheet
    By dixiV in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-03-2012, 06:35 AM
  3. [SOLVED] VBA for extracting different results based on multiple criterias
    By siduri in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-29-2012, 10:09 AM
  4. returning results via filter to next sheet
    By excellentexcel in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-27-2009, 08:11 AM
  5. How do I link filter results into another sheet?
    By aneczka in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-07-2006, 10:35 AM

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