+ Reply to Thread
Results 1 to 8 of 8

Using SORTBY with nested FILTER to sort two columns

  1. #1
    Registered User
    Join Date
    06-07-2023
    Location
    Baltimore, MD
    MS-Off Ver
    Office 365
    Posts
    4

    Using SORTBY with nested FILTER to sort two columns

    Hello, I just joined today in order to address my problem.

    I am using Windows 11, with Office 365; the OS and Apps are fully current with the latest patches, and very recently rebooted.
    I have a budget spreadsheet, and it has a tab for Current State of my Debts.
    I'm struggling to get just this one part to work; the rest is good.

    I have a formula in E70 (=SORT(FILTER($E$4:$G$55,($G$4:$G$55<>0)),2,-1,FALSE)); which correctly produces the following SOURCE data to my problems/questions:
    - Col E (rows 70:121) contains Debt Title text
    - Col F (rows 70:121) contains the Interest Rate to two decimal places
    - Col G (rows 70:121) contains a positive Current Balance value
    - This source data is already in alphabetical order by Debt Title text, with relevant debts listed 'at the top' of the list, and blank lines 'falling' out to the rest of the list, at the bottom. The matching cells in F and G are blank, when E is blank.

    The desired outcome results in the target area, $AB$4;$AD$55, containing a sorted and filtered subset of data sourced from $E$70:$G$121.
    Both the source data ($E$70:$G$121) and target area ($AB$4;$AD$55) are already properly formatted for TEXT (E & AB), PERCENTAGE (F & AC), and ACCOUNTING to 2 decimal places (G & AD).

    GOAL#1: In the target area ($AB$4;$AD$55) it should include only data lines at the top of the list from the source area (i.e. blank lines remain blank at the bottom of the list), it is to be sorted FIRST by the Interest Rate in decending order, and then sorted SECOND by the Current Balance, also in decending order.

    I have taken incremental steps to place a working formula in cell AB4:
    1. This formula works and brings over the list (data lines at the top, blanks below), still sorted alphabetically, as is:
    =FILTER($E$70:$G$121,($E$70:$E$121<>""))

    2. This formula works and also brings over the list, now sorted by APR only; however, all rows/cells of the target area are filled with something, even when the source rows/cells are blank (i.e. the 'blank' text cells actually contain '0').
    =SORT($E$70:$G$121,2,-1,FALSE)

    3. This formula works and brings over the list, sorted by APR first, and then Current Balance second; however, all rows/cells of the target area are filled with something, even when the source rows/cells are blank (i.e. the 'blank' text cells actually contain '0'). This is what I am currently living with.
    =SORTBY($E$70:$G$121,$F$70:$F$121,-1,$G$70:$G$121,-1)

    4. This formula does NOT work, but SEEMS to make sense to me as the solution, yet results in an "#VALUE!" error:
    =SORTBY(FILTER($E$70:$G$121,($E$70:$E$121<>"")),$F$70:$F$121,-1,$G$70:$G$121,-1)

    5. When I receive the "#VALUE!" error, no manner of troubleshooting helps to resolve the problem, and it really isn't clear to me just WHAT the real problem is exactly.
    - Using the "Evaluate Formula" pop-up, when selecting "Show the Calculation Steps" is of no help. The "Evaluate" button faithfully reports the "#VALUE!" error. The "Step in" and "Step out" buttons do nothing.
    - "Ignoring the error" does no good, either.
    - "Editing in the Formula Bar" is mildly useful. I have ensured as I step through the #4 formula, character by character, that it appears to be syntactically correct, that it is in fact referring to the correct row/cell ranges, as appropriate, as I do visually see the correct source area(s) highlighted. But, I still receive the "#VALUE!" error, every time.
    - I have researched via google and its suggested websites how to correctly construct SORT, SORTBY, FILTER and SORTBY(FILTER()) formulas.
    - #1 up above does produce the correct subset of the source data, filtering out blank lines.
    - #3 up above does produce the correct sorting by APR and then Current Balance.
    - Bizzarely, combining the two results in the "#VALUE!" error; yet it appears syntactically correct.
    Last edited by JoeWulf; 06-07-2023 at 02:00 PM.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,795

    Re: Using SORTBY with nested FILTER to sort two columns

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

  3. #3
    Registered User
    Join Date
    06-07-2023
    Location
    Baltimore, MD
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Using SORTBY with nested FILTER to sort two columns

    Fluff13, thank you! That is excellent, and showed me a nuance ("{2,3}") that I've never seen before.

    Extending that a bit further.... How can I transform the list resulting from the formula you gave me.... such that listed entries, which have a zero balance, fall to the bottom.
    I'd still want the rest of the list which has positive balances above $0.00, to be properly sorted by APR first, then their current positive balance.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,795

    Re: Using SORTBY with nested FILTER to sort two columns

    Can you provide a sample workbook with expected results?

  5. #5
    Registered User
    Join Date
    06-07-2023
    Location
    Baltimore, MD
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Using SORTBY with nested FILTER to sort two columns

    Fluff13,

    The attached XLS is a work in progress, and has evolved based on lessons learned.
    - (#1) P4:X55 is the everything for the source data (anonymized); this is composed/structured as it works today

    - (#2) P4:R55 if the source data for this issue
    - E4, F4 and G4 are the resulting lists (of P4:R55) with blank entries FILTERed out
    E4 has: =FILTER($P$4:$P$55,($P$4:$P$55<>""))
    F4 has: =FILTER($Q$4:$Q$55,($Q$4:$Q$55<>""))
    G4 has: =FILTER($R$4:$R$55,($R$4:$R$55<>""))
    - (#3) E68 is essentially the formula you gave me earlier
    E68 has: =SORT(FILTER($E$4:$G$55,($E$4:$E$55<>"")),{2,3},-1)


    I COPIED the values from E68:G119 to AB4:AD55 (#4).
    I then manually moved the four entries down which have zero balances.

    As the number of debts changes (adds or removes), and as the debts are paid off (or grow), I'm looking to find a way to dynamically visualize the debts by APR,
    which have balances at the top, as is shown, and then the remaining existing (potential) debts I have which are at a zero balance, to 'fall' down to the bottom.

    This is how I wish to view the data.

    I have manually set the background color for the 4 zero balance entries to light grey in color to emphasize them (not necessary).

    Thank you.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,795

    Re: Using SORTBY with nested FILTER to sort two columns

    Thanks for that.
    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-07-2023
    Location
    Baltimore, MD
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Using SORTBY with nested FILTER to sort two columns

    That works perfectly, exactly as is.

    I'm going to mark this as solved.

    Thank you so much for the assistance, very much appreciated!!

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,795

    Re: Using SORTBY with nested FILTER to sort two columns

    You're welcome & thanks for the feedback

+ 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] Combining Sortby with Filter
    By mstgier in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-27-2024, 02:32 AM
  2. [SOLVED] FILTER and SORTBY
    By kallam in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2023, 11:40 AM
  3. [SOLVED] How to Custom sort by 'Sortby' function?
    By Ariful Islam in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-26-2023, 06:27 AM
  4. Combining FILTER and SORTBY formulas
    By Ctromb in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-10-2021, 09:39 AM
  5. [SOLVED] SortBy + Filter Formula
    By ionelz in forum Excel General
    Replies: 9
    Last Post: 10-09-2021, 06:56 AM
  6. [SOLVED] New formulas SORT & SORTBY - can I use it with not neighboring columns?
    By afgi in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-28-2021, 07:44 AM
  7. [SOLVED] Sort data using filter or sortby function (excel 365)
    By sunboy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-25-2021, 04:36 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