Compare Two Excel Columns to Find Missing Policy Numbers in WPS Office
Question details
The user needs to compare policy numbers in two Excel columns and identify values that are present in the first column but missing from the second.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user has policy numbers from two systems in separate columns and wants to reconcile them to find records unique to the first source.
- Observed behavior
- The workflow should produce a clear Missing or Found result, highlight missing values, or filter a combined list for one-source-only records.
Before comparing columns, confirm that both policy number columns are available in the same worksheet or workbook and that the values are formatted consistently, with no leading or trailing spaces.
Use a COUNTIF Formula to Flag Missing Policy Numbers
Use this method for a quick, reusable check when both columns are in the same worksheet and you need a row-by-row result.
The formula checks each value in column A against the full range in column B. If a policy number does not appear in column B, the helper column returns Missing; otherwise it returns Found. Adjust the column references to match your data.
Click the first empty cell next to your first-column data, such as C2, in your WPS Office Excel worksheet.
Type =IF(COUNTIF($B:$B,A2)=0,"Missing","Found") and press Enter.
Drag the fill handle from C2 down through the last row of column A to check every policy number in the list.
Combine Both Lists with a Source Column and Filter One-Source Records
Use this method when the two lists are in different locations or when you want to see both the missing and extra values in a single combined view.
Highlight Missing Policy Numbers with Conditional Formatting
Use this method for a visual, non-destructive way to flag missing values directly in column A without adding a permanent result column.
Compare Excel Columns and Find Missing Policy Numbers in WPS Office
In WPS Office Excel, you can compare two policy number columns with a COUNTIF helper formula, conditional formatting, or a combined Source column while opening and saving standard .xlsx workbooks.
- 1. Open the workbook: Launch WPS Office and open the Excel file that contains the two policy number columns you want to compare.
- 2. Insert a helper column: Click the first empty cell next to your first-column data, such as C2, and enter =IF(COUNTIF($B:$B,A2)=0,"Missing","Found").
- 3. Fill the formula through all rows: Drag the fill handle from C2 down to the last row of column A to evaluate every policy number.
- 4. Filter the missing results: Select the helper column, go to Data > AutoFilter, and filter for Missing to display only policy numbers absent from column B.
Frequently Asked Questions
Can I compare policy numbers that are in two different workbooks?
Yes. You can either combine both lists into one workbook with a Source column, or use an external reference in COUNTIF such as =IF(COUNTIF([Book2]Sheet1!$B:$B,A2)=0,"Missing","Found").
Why do some matching policy numbers still show as Missing?
This usually happens when formatting or extra spaces differ. Use the TRIM function to remove leading and trailing spaces, and make sure both columns use the same number or text format before comparing.
How can I show only the missing policy numbers after using the formula?
Select the helper column, go to Data > AutoFilter, and filter by the value Missing. Only rows where the first-column value is not found in the second column will remain visible.
Can I highlight missing values without adding a helper column?
Yes. Select column A, go to Home > Conditional Formatting > New Rule > Use a formula, enter =COUNTIF($B:$B,A2)=0, and choose a highlight color.
