+ Reply to Thread
Results 1 to 7 of 7

Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx))

  1. #1
    Registered User
    Join Date
    03-01-2022
    Location
    Raleigh NC
    MS-Off Ver
    365
    Posts
    19

    Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx))

    My main four formulas are:
    G9: =SORT(UNIQUE(FILTER(Subdivision,Subdivision<>"")))
    H9: =COUNTIFS(Subdivision,G9,New_Construction,H$3,Status,H$4,Days_Closed,"<"&H$5)
    I9: =INDEX(SORT(G9:H100,2,-1,FALSE),SEQUENCE(COUNTIF(H9:H100,">0")),{1,2})

    I have named ranges Subdivision, New_Construction, Status, and Days_Closed. The formula in H9 is copied down the column. I'd like to reduce the multiple formulas to one formula, and the four columns down to two.

    A simplified example is attached.
    Test of Sort Unique and Filter and getting occurances count.xlsm

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx)

    Maybe:

    G9:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I9:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx)

    Note that the formula in I9 is independent of the formula in G9. It is, essentially, the same formula but with the output tweaked.

  4. #4
    Registered User
    Join Date
    03-01-2022
    Location
    Raleigh NC
    MS-Off Ver
    365
    Posts
    19

    Re: Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx)

    Thanks. That's an improvement. The main benefit with your formula is that I don't need to drag the formula I have in H9 down thousands of rows.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx)

    That's an improvement.
    What's the phrase I'm looking for? Damned with faint praise?

    The second formula, unless I am mistaken, does exactly what you said you wanted. Hence the reason for saying it i not dependent on the first. One formula, two columns, final result … 3 rows.

  6. #6
    Registered User
    Join Date
    03-01-2022
    Location
    Raleigh NC
    MS-Off Ver
    365
    Posts
    19

    Re: Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx)

    Yes, you are right. I didn't realize that at first, but then I spent some time understanding what you had and I realized I only needed the second formula. I've incorporated that into my big version of the workbook. It's working good. (The LET function is new to me!). Thanks.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Trying to simplify a SORT(UNIQUE(FILTER(xxxxx))) , a COUNTIFS, and an INDEX(SORT(xxxx)

    You're welcome. Thanks for the rep.

    LET is a function that allows you to declare a variable and then assign a value, a cell, a range, or a formula to it. It can dramatically reduce the length of a complex formula and make it more readable. I try to use short variable names that relate to the range and/or the formula. Here, g was the filtered subdivision (which happened to be in column G. cg was the Count of g. hs was HSTACK, and so on.

+ 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. unique sort function with filter
    By rayted in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-17-2022, 08:47 AM
  2. Replies: 0
    Last Post: 10-27-2021, 04:04 AM
  3. [SOLVED] Index and Filter Functions: Workaround to sort, filter, and format within array?
    By beginner21 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-25-2021, 04:48 PM
  4. Replies: 1
    Last Post: 08-17-2019, 01:11 PM
  5. INDEX/MATCH unique values + Sort
    By oysteinjha in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-20-2018, 01:59 AM
  6. Sort & Filter Unique items
    By olasa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-12-2008, 07:00 AM
  7. [SOLVED] Filter in place question for sort of unique records
    By a in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-08-2005, 10:06 AM

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