Excel - sorting rows by balance due

Generally speaking, the answer to such questions depends on what your use case is - do you just need to see the open balances or do you need to be able to interact with the rows that have the open balances?   If you just need to see them, you can write a dynamic array formula that uses sortby, filter, etc. and set up your data view however makes sense.  If you need to edit the data, that is more complicated since there is not, so far as I know, a way to have data in a table automatically sort as you add/change data.

The solution for viewing/editing the data in place (outside of a VBA type solution or other more "outside" excel tools as NikolinoDE suggested) is to manually update your sort.  This can be accomplished in a couple of ways, but a relatively simple way (I believe PeterBartholomew1 uses this one) is to concatenate the columns you wish to sort by into a single helper column and then sort by that column.  I believe he uses a character like "-" or some such to split the text strings and takes advantage of textjoin, but I could be wrong.  Despite the helper column, I tend to think that this method is probably the most conceptually efficient as involves a single, straightforward join formula combined with sorting as needed on the helper column.  Keep in mind that Peter offered this solution before sortby was generally available.

What I would likely do, depending on the particular need, is write a formula to assign an integer to a helper column representing the sort order of that row and then sort by the helper column.  So, for instance, I might take the table range which includes the columns I wish to sort by and includes an additional "primary id" or "control number" type column, i.e. a column with relatively simple data to search and find unique values, preferably sortable.  So if you don't have a primary column already, just add one, number it from 1 to whatever, but make sure the values are fixed (meaning you copy and paste the integer values if you number the columns using a formula).  Then do something like use sortby for the columns of interest, and then add a similar control number to the sorted array.  You then xlookup the control number for the original array in the sorted array and return the sorted array's control number.  After that setup, from then on you can simply sort by the column with the sorted array's control number and get your current sorted state.  Only thing you have to be sure to do is to continue adding control numbers to each new row of an array.

Note that the general difference between my approach and Peters (besides my taking advantage of the new sortby forumula) is that he sorts by the data directly whereas I create an "id" that sort of hides the data behind something that looks prettier.  In both cases, the impact is the same - you are adding a new column to your data array that you have to manually sort by every time you want the most up-to-date ordering of your data.

So say you have an array named "sample_array" with these headers and values:

you add a column called "sorted_id" and insert the following formula (I used a match to get the column index for a header in case you aren't using excel's table feature - if you are, you could just use, e.g., sample_array[orig_unsorted_id]):

=LET(
    target_orig_id, [@[orig_unsorted_id]],
    source_array, sample_array[#All],
    primary_id_col_name, "orig_unsorted_id",
    sort_by_col_name, "balance",
    source_array_data, DROP(source_array, 1),
    source_array_headers, TAKE(source_array, 1),
    balance_col, CHOOSECOLS(source_array_data, MATCH(sort_by_col_name, source_array_headers, 0)),
    orig_id_col, CHOOSECOLS(source_array_data, MATCH(primary_id_col_name, source_array_headers, 0)),
    sorted_orig_id_col, SORTBY(orig_id_col, balance_col, -1),
    return_array, SEQUENCE(ROWS(source_array_data)),
    XLOOKUP(target_orig_id, sorted_orig_id_col, return_array, "check_values")
)

you should end up with:

you then sort by the "sorted_id" column.  Every time you update a value or add a new row, you just have to re-sort by the sorted_id column.

You should get the following after the first sort:

You can enhance this sort method as necessary, including filtering the original array before sorting, filtering it after and resorting, etc.  

To automatically sort rows so that rows with an open balance stay on top and rows with a balance of $0 move to the bottom, you can use a combination of Excel functions.

While Excel formulas alone will not automatically re-sort the data, you can use the following approaches to achieve your goal.

Approaches such as using the FILTER function or Use Conditional Sorting with VBA (Automatic Sorting) or Using Power Query (Semi-Automated).

Here is an example using VBA.

To have the rows sort automatically as you update the "Total Owed" column, you can use a VBA macro. Here’s a simple macro that sorts the data whenever there is a change in the worksheet:

1. Open the VBA Editor:

• Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

1. Insert the Macro:

• Double-click on the sheet where your data is (e.g., Sheet1) in the Project Explorer.
• Paste the following code into the code window:

Vba Code is untested backup your file first

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("YourColumnRange")) Is Nothing Then
        Application.EnableEvents = False
        Me.Range("YourDataRange").Sort Key1:=Me.Range("LastColumnHeader"), Order1:=xlDescending, Header:=xlYes
        Application.EnableEvents = True
    End If
End Sub

Replace YourColumnRange with the actual range for the "Total Owed" column (e.g., J:J if it's in column J).

• Replace YourDataRange with the range of the entire data table (e.g., A1:J1000).
• Replace LastColumnHeader with the specific cell reference for the header of the "Total Owed" column (e.g., J1).

1. Save and Close:

• Save the macro and close the VBA editor.

My answers are voluntary and without guarantee!

Hope this will help you.

NikolinoDE

I know I don't know anything (Socrates)