Filter Names on a WPS Spreadsheet Sheet Missing From Another Sheet
Question details
Display member names from the ALLE sheet that do not appear in the paid-member list on the BETAALD sheet.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user needs to compare names in ALLE!A2:A999 against BETAALD!C17:C999 and identify unpaid or unmatched members.
- Observed behavior
- The desired result is a list containing only nonblank names from ALLE that have no matching entry in BETAALD.
Check that both sheets use the same name format, including spelling, spaces, and punctuation. Choose an empty output range on the ALLE sheet so the returned list can expand without overwriting existing data.
Use FILTER and COUNTIF to return members missing from BETAALD
Use this method when your version of WPS Spreadsheet supports the FILTER function and you want an automatically updating results list.
The formula tests every name in ALLE!A2:A999, excludes blank cells, and returns only names that do not occur in BETAALD!C17:C999.
On the ALLE sheet, click an empty cell where you want the unmatched member list to begin, such as E2.
Type or paste =FILTER(A2:A999,(A2:A999<>"")*(COUNTIF(BETAALD!C17:C999,A2:A999)=0),"No unmatched members") and press Enter.
Check the spilled results below the formula cell. The list shows names in ALLE column A that are not found in BETAALD column C.
Add a helper column and filter for unmatched names
Use this method if FILTER is unavailable or if you prefer to review the match status beside every member name.
Compare Member Lists Across Sheets in WPS Spreadsheet
WPS Spreadsheet can compare member names across worksheets with COUNTIF and FILTER formulas. Create a live list of members who are on ALLE but missing from BETAALD, or filter a helper column to review unmatched records.
- 1. Open the member workbook: Open the workbook in WPS Office and select the ALLE worksheet.
- 2. Choose an output location: Click an empty cell outside the source list, such as E2, to place the unmatched results.
- 3. Enter the comparison formula: Enter =FILTER(A2:A999,(A2:A999<>"")*(COUNTIF(BETAALD!C17:C999,A2:A999)=0),"No unmatched members") and press Enter.
- 4. Check and use the result list: Review the returned names and use the list for follow-up, reporting, or payment-status checks.
Frequently Asked Questions
Why does a name that looks identical appear as missing?
The cells may contain extra spaces or different characters. Check both entries in the formula bar, or standardize names with functions such as TRIM before comparing them.
Can I compare a different pair of columns or sheets?
Yes. Replace A2:A999 with the source-name range and replace BETAALD!C17:C999 with the range that contains the names to check against.
What does the "No unmatched members" message mean?
It is the third FILTER argument. It appears when every nonblank name in ALLE!A2:A999 is found in BETAALD!C17:C999.
How can I show only unmatched rows without using FILTER?
Add =COUNTIF(BETAALD!$C$17:$C$999,A2)=0 in a helper column, fill it down, then choose Data > Filter and filter that helper column for TRUE.
Does COUNTIF distinguish uppercase and lowercase names?
No. COUNTIF is not case-sensitive, so names that differ only by capitalization are treated as matches.
