How to Return TRUE or FALSE for Duplicate Excel Rows and Blank Dates in WPS Office
Question details
The user needs a formula to evaluate duplicate rows that share a job card number and return TRUE only when every matching row has a date in the Date Received column, and FALSE when any matching date is blank.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with a table of job card records and wants each row to automatically show whether all entries for that job card number are complete.
- Observed behavior
- The goal state is a calculated column that returns TRUE for complete groups and FALSE when any date is missing.
Make sure your data uses a consistent Job Card Number column and Date Received column. If possible, format the data as an Excel Table by selecting the range and pressing Ctrl+T, then verify that blank date cells do not contain spaces or zero-length text.
Use a calculated column formula with COUNTIF and COUNTIFS
Best when your data is formatted as an Excel Table because the structured references make the formula easy to read and it fills the entire column automatically.
The COUNTIF part counts how many rows share the same Job Card Number. The COUNTIFS part counts how many of those rows also have a nonblank Date Received value. If the two counts match, the row returns TRUE; otherwise, it returns FALSE.
In your Excel Table, click the first data cell under the column where you want the TRUE/FALSE result, for example C2.
Type =COUNTIF([Job Card Number],[@[Job Card Number]])=COUNTIFS([Job Card Number],[@[Job Card Number]],[Date Received],"<>") and press Enter.
If the column is inside the table, WPS Spreadsheets fills the formula down to all rows. Verify that complete job card groups show TRUE and groups with any blank date show FALSE.
Apply the same logic to a normal range with absolute references
Use this when your data is not formatted as an Excel Table, or when you need to reference fixed row ranges instead of table columns.
Check duplicate rows and blank dates directly in WPS Office Spreadsheets
WPS Office Spreadsheets supports the same COUNTIF and COUNTIFS functions and structured table references used in Excel, so you can create a TRUE/FALSE completeness check without VBA or add-ins.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, open the workbook containing the job card data, and confirm the worksheet has Job Card Number and Date Received columns.
- 2. Select the result column: Click the first cell where you want the TRUE/FALSE result, typically C2.
- 3. Enter the COUNTIF/COUNTIFS formula: Type =COUNTIF([Job Card Number],[@[Job Card Number]])=COUNTIFS([Job Card Number],[@[Job Card Number]],[Date Received],"<>") and press Enter.
- 4. Review and save the workbook: Check that TRUE appears only for complete job card groups and FALSE appears when any matching row is missing a date, then save the file in .xlsx or .xls format if needed.
Frequently Asked Questions
Why does the formula compare two counts instead of using an IF function?
The comparison COUNTIF(...)=COUNTIFS(...,"<>") directly produces TRUE or FALSE. It avoids a nested IF because TRUE means the number of duplicate job card rows equals the number of those rows with a nonblank Date Received, and FALSE means at least one date is missing.
Will this work if my job card numbers repeat in non-adjacent rows?
Yes. COUNTIF and COUNTIFS evaluate the entire specified column or range, so the rows do not need to be sorted or grouped together.
Can I use this formula in WPS Office when the file was created in Microsoft Excel?
Yes. WPS Office Spreadsheets supports COUNTIF/COUNTIFS and structured table references, and can open .xlsx and .xls files with the same formulas.
What should I check if a date looks blank but the formula still returns TRUE?
Select the blank-looking date cell and press Delete. If the cell contains a space or a zero-length text string, nonblank counting may treat it as present; clearing it should switch the row group to FALSE.
