Results 1 to 4 of 4

Using FILTER to return matching order numbers

Threaded View

  1. #1
    Forum Contributor
    Join Date
    09-04-2013
    Location
    USA
    MS-Off Ver
    Excel 365
    Posts
    266

    Using FILTER to return matching order numbers

    I am attempting to return corresponding/related order numbers for same customer and timeframe. The twist is that there are two categories, A and B. If looking at an A category, I am looking to return all B order numbers. If looking at a B category, looking to return all A order numbers (once again, for same customer and timeframe).

    Note that Category A orders cover multiple days, whereas Category B orders cover one day..

    Anyway, so far, I am using the following formula:

    =IF(J2="A",TEXTJOIN(";",1,UNIQUE(FILTER($E$2:$E$55,(A2<=$A$2:$A$55)*(B2>=$B$2:$B$55)*(C2=$C$2:$C$55),""))),IF(J2="B",TEXTJOIN(";",1,UNIQUE(FILTER($E$2:$E$55,(A2>=$A$2:$A$55)*(B2<=$B$2:$B$55)*(C2=$C$2:$C$55),"")))))
    But, I am getting extraneous order numbers. For example, if looking at Part A categories, it will return both Category A and B order numbers. If I attempt to modify to exclude a category, it does not return any results.

    See attached spreadsheet. Column K is the formula result, Column L is the desired result.

    Any ideas? Thanks.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Matching repetitive numbers that are in sequential order
    By lamlam28 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-15-2021, 11:26 PM
  2. [SOLVED] Matching numbers in non-contagious ranges that are in sequential order
    By lamlam28 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-20-2021, 02:10 AM
  3. [SOLVED] Formula to Return Specific Ranges of Numbers in Order of Frequency
    By runelady in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-16-2016, 11:42 AM
  4. Matching order numbers to user IDs
    By MrKipling43 in forum Excel General
    Replies: 4
    Last Post: 01-13-2015, 09:39 AM
  5. Return a list of all matching numbers
    By samcdavies in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-12-2013, 10:17 AM
  6. [SOLVED] Lookup Vlue and return all matching rows in order
    By TextMonkey in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-24-2013, 03:03 PM
  7. [SOLVED] Matching numbers in columns in different order
    By CLUZADDER in forum Excel General
    Replies: 7
    Last Post: 04-06-2012, 09:34 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