How to Return a Result Only for the First Matching Row in WPS Spreadsheets
Question details
The user needs a formula that marks only the row with the lowest boat number when the same order number and customer part number appear across multiple rows.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A table contains Order Number, Customer Part Number, and Boat Number columns; duplicate order and customer part combinations occur, and only the row with the smallest boat number for each combination should receive an X.
- Observed behavior
- Multiple rows match the same order number and customer part number, but the formula must return a result only for the first matching row.
Check that your data is in an Excel table or named range, and verify that the boat number column contains numeric values rather than text values.
Mark the first matching row with MINIFS and COUNTIFS
Use this formula when duplicate order number and customer part number pairs exist and you need only the row with the lowest boat number marked.
The formula first confirms that the current order number and customer part number exist in the OPN lookup table. It then compares the current boat number with the minimum boat number for that same order number and customer part number. Only the row matching that minimum receives X.
Click the first empty cell in the column where the mark should appear, such as D2.
Type =IF(AND(COUNTIFS(OPN[ON],[@ON],OPN[CPN],[@CPN])>0,[@B]=MINIFS(CurrentTable[B],CurrentTable[ON],[@ON],CurrentTable[CPN],[@CPN])),"X","") and press Enter. Replace OPN, CurrentTable, ON, CPN, and B with your actual table and column names.
Drag the fill handle from D2 down through the last data row to apply the formula to every row.
Check each duplicate order number and customer part number group; only the row with the smallest boat number should show an X.
Convert text-formatted boat numbers before using MINIFS
Use this if the formula returns no match, returns #VALUE!, or marks the wrong row because boat numbers are stored as text.
Mark Only the First Matching Row in WPS Spreadsheets
Apply the Excel-compatible MINIFS and COUNTIFS formula in WPS Spreadsheets to mark the lowest boat number row for each order and customer part number, then save your file in .xlsx format.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel workbook containing the order number, customer part number, and boat number columns.
- 2. Select the first result cell: Add or locate the column where X should appear, then click the first data cell in that column.
- 3. Enter the formula: Type =IF(AND(COUNTIFS(OPN[ON],[@ON],OPN[CPN],[@CPN])>0,[@B]=MINIFS(CurrentTable[B],CurrentTable[ON],[@ON],CurrentTable[CPN],[@CPN])),"X","") and replace OPN, CurrentTable, ON, CPN, and B with your actual names.
- 4. Fill the formula down: Drag the fill handle down through the last row to apply the formula to all records.
- 5. Save as Excel Workbook: Click Menu > Save As, choose Excel Workbook (*.xlsx), and save the file to keep compatibility with Microsoft Excel.
Frequently Asked Questions
Why does the formula return #VALUE! instead of X?
Usually the boat number column is text, or the table or column names do not match the formula. Select the boat number range, click Home > Number Format > Number, and verify that OPN, CurrentTable, ON, CPN, and B match your workbook.
Can I use this formula without Excel tables?
Yes. Replace table references with fixed ranges, for example =IF(AND(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>0,C2=MINIFS($C$2:$C$100,$A$2:$A$100,A2,$B$2:$B$100,B2)),"X",""). Adjust the column letters and row range to your data.
What if two rows have the same minimum boat number?
The MINIFS formula marks every row tied for the minimum. To mark only one row, add a helper column that counts previous occurrences and include it as a tie-breaker.
Does WPS Spreadsheets support MINIFS and COUNTIFS?
Yes. WPS Spreadsheets supports MINIFS and COUNTIFS in Excel-compatible .xlsx workbooks, so the same formula works in both applications.
How do I apply the mark to newly added rows?
If you are using an Excel table, the formula usually fills down automatically. Otherwise, drag the fill handle from the result cell after adding rows or convert the range to a table with Insert > Table.
