+ Reply to Thread
Results 1 to 7 of 7

Extract all values that matches 2 criteria and sort

  1. #1
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Extract all values that matches 2 criteria and sort

    As the title says, can the list of uniques extracted based on 2 criteria be sorted A to Z as well within the formula?
    Currently I use this formula:
    =IFERROR(INDEX('Data'!$A$2:$A$350,AGGREGATE(15,6,ROW(' Data'!$A$2:$A$350)/('Data'!$D$2:$D$350=A$1)/('Data'!$H$2:$H$350="Buy"),ROW(1:1))),"")
    Click the * to say thanks.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,448

    Re: Extract all values that matches 2 criteria and sort

    A sample sheet perhaps?

  3. #3
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Extract all values that matches 2 criteria and sort

    I've attached a sample with 2 sheets
    Attached Files Attached Files

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extract all values that matches 2 criteria and sort

    Please try at C2
    =IFNA(INDEX(Data!$A$2:$A$51,MATCH(0,INDEX(COUNTIFS(Data!$A$2:$A$51,"<"&Data!$A$2:$A$51,Data!$H$2:$H$51,"Buy")/(Data!$H$2:$H$51="buy")-SUMPRODUCT(COUNTIFS(Data!$A$2:$A$51,C$1:C1)),,),)),"")
    Attached Files Attached Files

  5. #5
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Extract all values that matches 2 criteria and sort

    Hi Rorya. Is it possible to add a second criteria to your formula? Sample attached
    Attached Files Attached Files

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extract all values that matches 2 criteria and sort

    Try at C2 for Newyork
    =IFNA(INDEX(Data!$A$2:$A$51,MATCH(0,INDEX(COUNTIFS(Data!$A$2:$A$51,"<"&Data!$A$2:$A$51,Data!$D$2:$D$51,$C$1)/(Data!$D$2:$D$51=$C$1)-SUMPRODUCT(COUNTIFS(Data!$A$2:$A$51,C$1:C1)),,),)),"")

    For Newyork and Buy
    =IFNA(INDEX(Data!$A$2:$A$51,MATCH(0,INDEX(COUNTIFS(Data!$A$2:$A$51,"<"&Data!$A$2:$A$51,Data!$H$2:$H$51,"Buy",Data!$D$2:$D$51,$C$1)/(Data!$H$2:$H$51="buy")/(Data!$D$2:$D$51=$C$1)-SUMPRODUCT(COUNTIFS(Data!$A$2:$A$51,C$1:C1)),,),)),"")
    Last edited by Bo_Ry; 07-31-2020 at 05:21 AM.

  7. #7
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Extract all values that matches 2 criteria and sort

    This is exactly what I needed Rorya. Many thanks for your help, works like a treat!

+ 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. [SOLVED] Extract values with criteria then sort them
    By Villalobos in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-25-2020, 05:24 AM
  2. Replies: 4
    Last Post: 05-31-2017, 09:41 AM
  3. Replies: 9
    Last Post: 12-09-2016, 10:43 AM
  4. Formula(s) to extract string that matches certain criteria from a text
    By Turtler in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 01-27-2014, 05:18 AM
  5. Filtering information to extract top 5 matches but excluding certain criteria as well
    By MeerkatMatt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-17-2013, 06:01 AM
  6. Find Some Matches Data In column With Criteria And Extract In ROW
    By a-a_m_ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-05-2012, 07:03 AM
  7. Replies: 2
    Last Post: 07-19-2011, 04:08 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