Sort a Row in Descending Order and Return Ranks in WPS Spreadsheets
Question details
Sort horizontal numeric values in descending order and return the corresponding rank, position, or related value in the matching order.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has values arranged across a row and needs a formula-based descending sort that preserves each value's relationship to its associated rank or position.
- Observed behavior
- The desired result is a spilled horizontal array ordered from largest to smallest, with the related rank or position values kept aligned.
Place the source values and their related ranks or positions in matching horizontal ranges with the same number of cells. Ensure that the cells where the formula will spill are empty.
Use SORTBY to sort the row by its related order in descending order
Use this method when one horizontal row contains numbers and another matching row contains the rank, position, or order used for sorting.
SORTBY returns a dynamic spilled result. The -1 sort order sorts from largest to smallest while keeping each number associated with its corresponding order value.
For example, use A1:F1 for the numbers and A2:F2 for the corresponding ranks, positions, or order values. Both ranges must have the same width.
Click an empty cell where the sorted horizontal result should begin, such as A4. Leave the cells to the right empty for the spilled result.
Type =SORTBY(A1:F1,A2:F2,-1) and press Enter. Replace A1:F1 with the numbers range and A2:F2 with the corresponding order range.
WPS Spreadsheets fills the result across adjacent cells. The values are sorted according to the order range from largest to smallest.
Sort Related Row Data Dynamically with WPS Spreadsheets
WPS Spreadsheets lets you use dynamic-array formulas such as SORTBY to return a descending horizontal sort without manually moving cells. Enter one formula, and the sorted result spills into the adjacent output cells while preserving the relationship between values and their related positions.
- 1. Open the worksheet in WPS Spreadsheets: Open your Excel-compatible workbook in WPS Office and select the sheet containing the horizontal values.
- 2. Set up matching source rows: Place the values in one row and the related rank or position data in a matching row with the same number of columns.
- 3. Choose an empty output cell: Click the cell where the descending sorted row should start, ensuring enough blank cells are available to the right.
- 4. Apply the SORTBY formula: Enter =SORTBY(numbers,order,-1), replacing numbers and order with your source ranges, then press Enter.
Frequently Asked Questions
How do I sort a horizontal row from largest to smallest in WPS Spreadsheets?
Select an empty output cell and enter =SORTBY(A1:F1,A1:F1,-1), replacing A1:F1 with your row range. The -1 argument returns the values in descending order.
How can I keep ranks paired with sorted values?
Use matching ranges in SORTBY. For example, =SORTBY(A1:F1,A2:F2,-1) sorts the values in A1:F1 according to the related order values in A2:F2 while preserving each pairing.
Why does my SORTBY result not appear across the row?
SORTBY spills into neighboring cells. Clear any data, merged cells, or other content in the cells where the result needs to expand.
Can SORTBY return both the values and ranks together?
Yes. If your values are in A1:F1 and ranks are in A2:F2, select an empty cell and use =SORTBY(A1:F2,A1:F1,-1) to sort both rows together by the values in descending order.
