How to Create a Dynamic Top 10 List That Skips Sold Items in WPS Spreadsheets
Question details
The user needs to build a formula-driven top-10 list in WPS Spreadsheets that ranks the highest-priced unsold items, excludes rows where a selling price is present, handles duplicate prices, and refreshes automatically when price or sale status changes.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a pricing or inventory sheet and wants a dynamic top-10 list of unsold items without manually filtering out sold rows or breaking duplicate-price ties.
- Observed behavior
- A dynamic top-10 list should return only unsold records, give a unique rank even with duplicate prices, and update automatically as data changes.
Arrange your source data with at least columns for item name or model, price, and selling price. Make sure sold items have a value in the selling-price column and unsold items leave that cell blank.
Use a Helper Column with LARGE, INDEX, and MATCH to Rank Only Unsold Items
Use this formula-based method when the top-10 list must exclude any row that has a selling price and must update automatically without manual sorting.
This method assigns a near-invisible tie-breaker to each price so duplicate prices still return distinct rows. The helper column blanks sold rows, so the LARGE function ranks only unsold records.
Open WPS Spreadsheets and confirm the source range contains columns such as Item (A), Price (B), and Selling Price (C). Ensure column C is blank for unsold items and filled for sold items.
In D2 enter =IF($C2="",$B2-ROW()/10000000000,"") and fill it down through the last data row. This returns a unique adjusted price for unsold rows and a blank for sold rows.
On your top-10 output sheet, enter 1 through 10 in cells E2:E11. These numbers tell LARGE which largest adjusted price to find.
In F2 enter =IFERROR(INDEX($A$2:$A$100,MATCH(LARGE($D$2:$D$100,$E2),$D$2:$D$100,0)),"") and fill down to F11.
In G2 enter =IFERROR(INDEX($B$2:$B$100,MATCH(LARGE($D$2:$D$100,$E2),$D$2:$D$100,0)),"") and fill down to G11.
Create a Dynamic Unsold Top-10 List in WPS Spreadsheets
Use WPS Spreadsheets to build a formula-driven top-10 list that ranks only unsold items, excludes rows with a selling price, handles duplicate prices through a helper column, and recalculates automatically when data changes.
- 1. Launch WPS Spreadsheets and open your workbook: Open WPS Spreadsheets and load the .xlsx file that contains your item, price, and selling-price data.
- 2. Insert the adjusted-price helper column: Enter =IF($C2="",$B2-ROW()/10000000000,"") in D2 and fill it down to create a blank for sold rows and a unique adjusted price for unsold rows.
- 3. Enter the top-10 ranking formulas: Set up rank numbers 1-10, then use INDEX, MATCH, and LARGE formulas to return the item and price for only unsold rows.
- 4. Save the workbook: Save the file as .xlsx in WPS Spreadsheets to preserve formulas and Excel compatibility.
Frequently Asked Questions
Why does my top-10 formula include sold items?
Because the ranking range includes the original price column for all rows. Replace it with a helper column that returns a blank when the selling-price cell is filled so sold rows are ignored by LARGE.
How can duplicate prices return different unsold items?
Subtract a tiny fraction based on ROW() in the helper column, such as ROW()/10000000000. This keeps prices in order while giving each row a unique adjusted value for MATCH.
Will the list update automatically when a selling price is entered?
Yes. When the selling-price cell changes, the helper column recalculates and the LARGE, INDEX, and MATCH formulas return the next unsold item immediately.
Can I place the top-10 list on another sheet?
Yes. In the output sheet formulas, reference the source sheet name before the ranges, for example Source!$A$2:$A$100.
What if my data has more than 100 rows?
Change every $2:$100 range in the helper and output formulas to cover your full data range, such as $2:$1000 or a formatted table's entire column.
