+ Reply to Thread
Results 1 to 4 of 4

Array formulate with multiple criteria without duplicates

  1. #1
    Registered User
    Join Date
    10-21-2016
    Location
    Chicago, IL
    MS-Off Ver
    MAC 2011
    Posts
    4

    Array formulate with multiple criteria without duplicates

    Hi - I am attempting to pull a list of names from column A, based on whether they fit multiple criteria (the criteria is whether there is any text in column B OR column C).

    I am struggling with the OR function, and even when I get close, I am still pulling duplicates (because there is text in column B and column C).

    Here's what I think the formula should be (but obviously it isn't working -- instead this just pulls the full list without any filter)
    =IFERROR(INDEX($A$3:$A:200,SMALL(IF(OR($B$3:$B$200<>"",$C$3:$C$200<>""),ROW($A$3:$A$200)),ROW(1:1))-2,1)"")

    I've been reading through threads and piecing this together, so there may certainly be an easier way to do this. Any help is very appreciated!

    Thanks

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: Array formulate with multiple criteria without duplicates

    Hi mvictor and welcome to the forum,

    When you ask for "without duplicates" I immediately think you should use Advanced Filters instead of a formula. Read or watch https://support.microsoft.com/en-us/...1-8daaec1e83c2

    If the above doesn't help then supply a sample workbook so we can try a formula or two on it. Maybe Advanced Filter will be the real answer.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,147

    Re: Array formulate with multiple criteria without duplicates

    Try

    =IFERROR(INDEX($A$3:$A200,SMALL(IF(($B$3:$B$200<>"")+($C$3:$C$200<>""),ROW($A$3:$A$200)-ROW($A$3)+1),ROW(1:1)),1),"")

    Enter with Ctrl+shift+Enter

    or

    =IFERROR(INDEX($A$3:$A200,AGGREGATE(15,6,(ROW($A$3:$A$200)-ROW($A$3)+1)/(($B$3:$B$200<>"")+($C$3:$C$200<>"")),ROWS($1:1)),1),"")

    normal entry
    Last edited by JohnTopley; 09-07-2021 at 12:47 PM.

  4. #4
    Registered User
    Join Date
    10-21-2016
    Location
    Chicago, IL
    MS-Off Ver
    MAC 2011
    Posts
    4

    Re: Array formulate with multiple criteria without duplicates

    The former worked perfect!! Thanks so much

+ 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. Replies: 1
    Last Post: 01-14-2021, 04:19 PM
  2. [SOLVED] Lookup to match multiple criteria and return multiple results excluding duplicates.
    By kris3012 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-13-2018, 11:06 AM
  3. Replies: 10
    Last Post: 01-02-2017, 01:44 PM
  4. Countifs on an Array Range using multiple criteria in an Array
    By Mysore in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-12-2016, 04:39 PM
  5. Replies: 13
    Last Post: 04-19-2015, 11:51 PM
  6. [SOLVED] How to formulate gains and losses for a gross without duplicates...
    By johnny.rockets in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-20-2013, 03:16 PM
  7. How to formulate Min Max Function with Criteria?
    By geng in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-08-2010, 07:35 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