How to Filter and Sort a Named Excel Table with SORTBY
Question details
The user wants to filter an Excel table based on multiple criteria and dynamically sort the extracted data by specific columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing and extracting specific records from a named table, such as matching multiple Crew values while excluding certain markers, and then ordering the results.
- Observed behavior
- The goal is to generate a dynamic array that displays only the rows matching the filter logic, sorted accurately by defined fields like Unit and BasketNumber.
Ensure your dataset is formatted as an official Table with clearly named columns, and verify that your spreadsheet software supports dynamic array functions like FILTER and SORTBY.
Method 1: Use SORT wrapped around the FILTER function
This method is highly efficient. It first filters the table based on your multiple conditions, then sorts the resulting array by specifying the column index numbers.
In this approach, you use the FILTER function to extract the relevant rows. To handle multiple conditions, you use multiplication (*) for 'AND' logic and addition (+) for 'OR' logic.
After generating the filtered array, the SORT function takes over to order the data based on the specific column positions (e.g., column 5, then column 1).
Combine your conditions using array logic. For example, to include Crew 'A' or 'Days' but exclude rows where 'Last' is empty and 'NoLight' is 'x', use: (tblBasketList[Last]<>"") * ((tblBasketList[Crew]="A") + (tblBasketList[Crew]="Days")) * (tblBasketList[NoLight]<>"x").
Wrap your criteria inside the FILTER function to extract the data from your named table: =FILTER(tblBasketList, [Criteria]).
Finally, nest the FILTER formula inside SORT. Specify the columns to sort by using an array constant like {5,1} to sort by the 5th column and then the 1st column. The final formula looks like: =SORT(FILTER(tblBasketList,(tblBasketList[Last]<>"")*((tblBasketList[Crew]="A")+(tblBasketList[Crew]="Days"))*(tblBasketList[NoLight]<>"x")),{5,1}).
Method 2: Use FILTER wrapped around the SORTBY function
This alternative sorts the entire table first using explicitly named columns, and then applies the filter to the sorted array.
Filter and Sort Data Easily in WPS Spreadsheet
WPS Spreadsheet provides full support for advanced dynamic array functions like FILTER, SORT, and SORTBY. You can effortlessly manage complex data tables and multi-condition filtering without relying on complicated legacy macros.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your named table.
- 2. Select the output cell: Click on a blank cell where you want the upper-left corner of your filtered and sorted data to begin.
- 3. Enter the formula: Type your combined =SORT(FILTER(...)) or =FILTER(SORTBY(...)) formula into the formula bar.
- 4. Execute the dynamic array: Press Enter. WPS Spreadsheet will calculate the multi-condition logic and spill the sorted results into the neighboring cells automatically.
Frequently Asked Questions
Can I use SORTBY with columns that are not included in the FILTER output?
Yes, SORTBY allows you to sort an array based on an array or range that is outside the returned data, as long as the dimensions (number of rows) match perfectly.
Why am I getting a #CALC! error when using the FILTER function?
The #CALC! error typically occurs when the FILTER function returns an empty array, meaning no records match your criteria. You can fix this by adding the optional [if_empty] argument, like =FILTER(array, include, "No results").
How do I apply 'OR' logic in my FILTER function criteria?
To apply 'OR' logic between criteria, use the plus sign (+). For example, (Table[Crew]="A")+(Table[Crew]="Days") evaluates to true if either condition is met for that row.
Are dynamic array functions like SORT and FILTER supported in older spreadsheet versions?
No, functions like SORT, SORTBY, and FILTER are dynamic array formulas. They are supported in Microsoft 365, Excel 2021, and modern alternatives like WPS Office. Older versions require complex CSE (Ctrl+Shift+Enter) array formulas to achieve similar results.
