+ Reply to Thread
Results 1 to 6 of 6

Create an array for matches of a source array to any values in a match array.

  1. #1
    Registered User
    Join Date
    03-07-2011
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2003, 2007, & 2010
    Posts
    52

    Create an array for matches of a source array to any values in a match array.

    I'm looking to identify all rows in a range that match any values from another dynamic range that can have a varying number match values. Need to get all matches to remain 0 and all non matches to go to 1. Below is the current code I am using but it does not evaluate it as single array as I was hoping.

    Please Login or Register  to view this content.
    Below is an example of what the two input ranges would be and the output I am looking for. Thanks for the help.

    MATCH_VALUES_RANGE
    1
    2
    3

    DATA_RANGE
    1
    7
    2
    9
    3

    DESIRED ARRAY RESULT
    0
    1
    0
    1
    0


    CROSS POST
    https://www.mrexcel.com/forum/excel-...ml#post5014390
    Last edited by drew.j.harrison; 02-22-2018 at 12:17 AM.

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Create an array for matches of a source array to any values in a match array.

    Hi Drew- I believe this works:
    Please Login or Register  to view this content.
    If you have duplicates in the MATCH_VALUES_RANGE, you would get numbers other than 0/1 in the result_array. If that's a problem, use this instead:
    Please Login or Register  to view this content.
    Obviously, array-entry (CSE) is required.
    Last edited by leelnich; 02-22-2018 at 12:43 AM.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Create an array for matches of a source array to any values in a match array.

    If you want an array in memory to pass on to another calculation this array entered
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    will return {0;1;0;1;0}.

    To output it to the spreadsheet will require this array entered and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Dave

  4. #4
    Registered User
    Join Date
    03-07-2011
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2003, 2007, & 2010
    Posts
    52

    Re: Create an array for matches of a source array to any values in a match array.

    Hmmm... the countif method doesn't seem to evaluate multiple matches properly. Ended up using the following from the cross posted thread .


    {=1-ISNUMBER(MATCH(DATA_RANGE,MATCH_VALUES_RANGE,0))}

  5. #5
    Registered User
    Join Date
    03-07-2011
    Location
    Detroit, MI
    MS-Off Ver
    Excel 2003, 2007, & 2010
    Posts
    52

    Re: Create an array for matches of a source array to any values in a match array.

    As you may have guessed I am using this to create a dynamic list of all the "release numbers" in a table that match specific "release types" identified by another dynamic range. Below is my actual code:

    =IFERROR((INDEX(DATA_RELEASE_NUMBER,MATCH(0,COUNTIF(OFFSET(AI$2,0,0,ROW()-ROW(AI$2)),DATA_RELEASE_NUMBER)+(1-ISNUMBER(MATCH(DATA_RELEASE_TYPE,LIST_RELEASE_TYPES_TEMPORARY,0))),0))),"")

    AI$2 is the header of the list of matching "release numbers" and the list generates a dynamic range called "LIST_UNSORTED_TR_NUMBERS"
    DATA_RELEASE_TYPE is the dynamic "DATA_RANGE"
    LIST_RELEASE_TYPES_TEMPORARY the dynamic "MATCH_VALUES_RANGE"

    I am then using the following formula to sort the data Highest to Lowest to create a final dynamic range that is used for data validation, sorting, conditional formatting, etc. in various other places.

    =IF(COUNTA(LIST_UNSORTED_TR_NUMBERS)>=ROWS(AJ$2:AJ3), INDEX(LIST_UNSORTED_TR_NUMBERS, MATCH(LARGE(COUNTIF(LIST_UNSORTED_TR_NUMBERS, "<"&LIST_UNSORTED_TR_NUMBERS), ROW()-ROW(AJ$2)), COUNTIF(LIST_UNSORTED_TR_NUMBERS, "<"&LIST_UNSORTED_TR_NUMBERS), 0)), "")
    Last edited by drew.j.harrison; 02-22-2018 at 01:29 AM.

  6. #6
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: Create an array for matches of a source array to any values in a match array.

    I misread the requirements, had 0 and 1 switched. This would work:
    Please Login or Register  to view this content.

+ 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: 3
    Last Post: 04-02-2016, 08:16 PM
  2. [SOLVED] INDEX MATCH array formula that matches substring n gives multiple matches
    By bkwins in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2013, 04:57 AM
  3. Create an array based off values in another array - exclude blanks
    By clifton1230 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-05-2013, 05:35 PM
  4. Search array and highlight array matches
    By ricklou in forum Excel General
    Replies: 3
    Last Post: 12-19-2012, 02:29 PM
  5. Replies: 0
    Last Post: 10-13-2012, 10:13 PM
  6. Replies: 6
    Last Post: 05-26-2012, 04:56 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