+ Reply to Thread
Results 1 to 6 of 6

Using Dynamic Lists with duplicating criteria

  1. #1
    Registered User
    Join Date
    02-24-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    19

    Using Dynamic Lists with duplicating criteria

    I'm not sure if I'm explaining this correctly but I've included the attachment below. Basically, I have three rows of data:

    Month - Jan & Feb
    Shop - North, South, West
    Type - Fruit or Vegetable

    The issue is I want the ability to select 0, 1 or 2 shop criteria. The formula is working for 0 or 1, but for 2, I'm running in issues. For example, if I select South first, then West, nothing happens. I've attached my formula below that I've been using, and attached the spreadsheet to the message. I'm assuming I need to use OR/AND but had issues putting those in. Any ideas? I'm looking for a non-VBA solution. Thanks!!

    =IFERROR(INDEX(D:D,SMALL(IF((IF($G$6<>"",$A$2:$A$25=$G$6,TRUE))+(IF($G$9<>"",$B$2:$B$25=$G$9,TRUE))+(IF($G$12<>"",$B$2:$B$25=$G$12,TRUE))+(IF($G$3<>"",$C$2:$C$25=$G$3,TRUE))=4,ROW($C$2:$C$25)),ROW(C1))),"")

    New Model_Test.xlsx

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Retriving best score and week from a table to another table

    Hi Alex,

    I would avoid the arrays and use an added KEY column to individually mark the rows that match your search criteria, then INDEX/MATCH to draw those key rows over to your output table. Like so:
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    02-24-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Retriving best score and week from a table to another table

    Thanks for your help! The formula is a lot easier to manage. However, when I select North and West, I get no data. My desired outcome would be produce all the lines that have North or West in them.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using Dynamic Lists with duplicating criteria

    My bad, let's change the SHOP test.... use this:

    =IF(AND($J$3<>"", $J$3<>C2), N(G1), IF(AND($J$6<>"", $J$6<>A2), N(G1), IF(AND($J$9&$J$12<>"",NOT(ISNUMBER(SEARCH(B2, $J$9&$J$12)))), N(G1), N(G1)+1)))

  5. #5
    Registered User
    Join Date
    02-24-2011
    Location
    Chicago
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: Using Dynamic Lists with duplicating criteria

    Thanks! This did the trick.

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using Dynamic Lists with duplicating criteria

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Help, Duplicating a sheet with duplicating ability
    By joseph_pat in forum Excel General
    Replies: 7
    Last Post: 06-26-2015, 07:55 AM
  2. Dynamic Lists
    By CIRHS in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-05-2014, 05:00 AM
  3. Duplicating/Deleting rows based on criteria
    By excelit in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2013, 09:34 AM
  4. Dynamic Lists
    By mikera in forum Excel General
    Replies: 5
    Last Post: 08-16-2010, 01:54 PM
  5. Dynamic Lists
    By Zandra in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-04-2009, 09:47 PM

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