In December 2020, I spent some time writing functions that would allow data to be sorted as I entered it into Excel. Now I've discovered that Excel in my Microsoft 365 subscription has a function that does the same thing. It's called SORTBY.
I'm still trying to think of a practical use I may have for the function. The only thing I've found so far is to sort data as I enter it. I'm not really sure how useful this will be for me, because I can use Excel's built-in sorting features to sort the data after entry. But, one idea that occurred to me is to use SORTBY and conditional formatting to mark duplicate entries as I type. Maybe you'll have other ideas. Email them me at gsrlc.org@outlook.com so that I may share them with everyone.
The SORTBY function has the format SORTBY(array to be sorted,by_array1,[sort order],by_array2,[sort order]........). The square brackets "[ ]" indicate optional data for the sort order. If sort order is not included, ascending order is the default sorting option. However, you must insert a comma in that parameter position of the function or you will get an error.
Below is a picture in which the data I typed is in cells A3 through B12 (A3:B12 in Excel notation). The data that's sorted as I typed is in cells D3 through E12 (D3:E12 in Excel notation). Notice I included some duplicates to ensure data is sorted correctly.
The SORTBY function is entered into and is editable only in cell D3. The function is =SORTBY(A3:B12,A3:A12,1,B3:B12,1). If I click into any other cells in Columns D & E, then I see the formula, but it is grayed out.
The first occurrence of A3:B12 defines the cell range that is to be sorted. The second occurrence of A3:B12 indicates the first sort key - the Region column. The 1 that follows indicates to sort in ascending order. The remaining parameters of the formula, B3:B12,1 indicate to sort next by the Name column in ascending order. If I omit the optional sort order parameters, the function looks like this:
=SORTBY(A3:B15,A3:A15,,B3:B15,)
As the function is used here, you must know the range of data on which to apply the function, A3:A12, for example. However, things change if you convert the data in Columns A & B to an Excel table.
In the pic below, I selected cells A3:B12, clicked Insert on the Ribbon and chose Table. I made sure the "My table has headers" option was selected, because I do have the column headers Region and Name. I added data into row13, the new, last row of the table. The formula in cell D3 has automatically adjusted to become =SORTBY(A3:B13,A3:A13,1,B3:B13,1), reflecting that I added the new row to the table. This indicates I can enter any number of rows of data into the table without having to adjust the SORTBY function.
Here's a hint about adding rows to an Excel table. Press the Tab key while in the bottom, rightmost cell of the table. A new, empty row will be added to the table and the cursor will be placed into the leftmost cell of that new row. To avoid having zeros placed into the sorted data rows of Columns D & E, press Enter when you've added the last row of data into Columns A & B. Pressing Enter does not add a new, blank row to the table.