+ Reply to Thread
Results 1 to 4 of 4

Creating a List With 2 Criteria and Allowing Duplicate Return Values

  1. #1
    Registered User
    Join Date
    03-20-2020
    Location
    Maywood, USA
    MS-Off Ver
    2016 & 365
    Posts
    6

    Creating a List With 2 Criteria and Allowing Duplicate Return Values

    GREETINGS!


    Context:
    Iím attempting to create a horizontal list that dynamically shifts. The data comes from a standard table with 4 columns. My main issue is that I am unable to figure out a formula that creates a list that has 2 criteria and allows for duplicate values.

    Criteria 1: Items match the searched itemís manf. #
    Criteria 2: Item has been not been used.

    Goal: List of all applicable expiration dates. (Some unsued items may have the same expiration date).


    I am familiar with using iserror, index, small, row to create a list that allows for duplicate values but with only on condition.

    I am also familiar with using index, match, countif to get a unique list with multiple conditions.

    For some reason, I canít figure out how to add one more criteria to a duplicate allowing list. Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,964

    Re: Creating a List With 2 Criteria and Allowing Duplicate Return Values

    Try in G2:
    Please Login or Register  to view this content.
    Because you are getting familiar with INDEX(MATCH,SMALL,... then I 'd like to introduce AGGREGATE which does not require Ctrl-Shift_enter
    Attached Files Attached Files

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,852

    Re: Creating a List With 2 Criteria and Allowing Duplicate Return Values

    Please select G4:U4

    =IFERROR(AGGREGATE(15,6,Table3[Expiration]/(Table3[Mfg Itm ID]=TEXT($F4,"0000000"))/NOT(Table3[USED]),COLUMNS($G4:G4)),"")
    Confirm by Ctrl+Enter

    or MS365
    =TRANSPOSE(SORT(FILTER(Table3[Expiration],(Table3[Mfg Itm ID]=TEXT($F4,"0000000"))*NOT(Table3[USED]))))
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-18-2019
    Location
    Dubai
    MS-Off Ver
    2019
    Posts
    39

    Re: Creating a List With 2 Criteria and Allowing Duplicate Return Values

    Thank you. I have been lookig for this solution for some time.




    Best Regards

+ 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. How to: Return list of values from one column based on two criteria
    By AndreasMS in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-16-2020, 12:40 PM
  2. [SOLVED] Creating a list of unique values based on multiple criteria
    By 27POP27 in forum Excel General
    Replies: 5
    Last Post: 03-13-2019, 11:26 PM
  3. [SOLVED] Creating a list of unique values based off criteria
    By kristentringali in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-23-2018, 12:54 PM
  4. List unique values matching a criteria that has duplicate values
    By Andrewjs in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2017, 10:11 PM
  5. Return a list of cell values based on criteria in other columns
    By Mikeyd74 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-16-2015, 10:02 AM
  6. Replies: 3
    Last Post: 12-13-2013, 06:23 AM
  7. not allowing duplicate entries from a list
    By Kevska in forum Excel General
    Replies: 9
    Last Post: 02-14-2011, 08:11 PM

Tags for this Thread

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