How to Extract Records That Appear in Only One of Two Excel Tables in WPS Spreadsheets
Question details
The user needs to combine two Excel property databases and keep only records that appear in one source, while normalizing address formatting such as commas and extra spaces before duplicate detection.
- Product
- WPS Spreadsheets / Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is comparing two property tables and wants to remove duplicate addresses across sources while retaining unique-only records, even when address fields are formatted differently.
- Observed behavior
- Using UNIQUE alone may require all fields to match, so records with the same address but different other attributes are not being removed as expected.
Open both Excel tables in WPS Spreadsheets and identify the address columns that will be used for matching. Create a helper column for normalized address values before applying the unique-record formulas.
Normalize Addresses and Flag Unique Records with TRIM, SUBSTITUTE, and COUNTIF in WPS Spreadsheets
Use this worksheet method when address fields contain commas or extra spaces and you need to identify records that appear only once by normalized address.
Normalizing the address column removes formatting differences that can make the same property look different. After normalization, a COUNTIF check flags whichever normalized addresses occur exactly once in the combined address list.
In WPS Spreadsheets, click an empty cell in the first data row and enter =TRIM(SUBSTITUTE(G2,",","")), then press Enter. Drag the fill handle down to apply the formula to every address row.
In the next empty column, enter =COUNTIF(AD:AD,AD2)=1 and press Enter. This returns TRUE when the normalized address in AD2 occurs only once in column AD.
Select the header row, go to the Home tab, click Filter, and then filter the COUNTIF column by TRUE to isolate records that appear in only one table.
Return Rows That Occur Exactly Once with UNIQUE and VSTACK
Use this formula when your version of WPS Spreadsheets supports dynamic arrays and you want a single formula to stack two tables and keep only rows that occur exactly once.
Match Address Fields Only with a Composite Key and COUNTIFS
Use this method when records should be considered duplicates if key fields such as address and city match, even when other fields differ.
Extract Only-Once Records from Two Excel Tables in WPS Spreadsheets
WPS Spreadsheets can directly complete this task with built-in formulas and filters. Open your Excel workbook, normalize the address column, flag unique records with COUNTIF, and keep only the rows that appear in one of the two source tables.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, go to Menu > Open, select the Excel workbook that contains the two property tables, and wait for both tables to load.
- 2. Create a normalized address helper column: Click an empty cell in the first data row and enter =TRIM(SUBSTITUTE(G2,",","")) to remove commas and extra spaces, then drag the fill handle down through all address cells.
- 3. Flag records that occur only once: In a new column, enter =COUNTIF(AD:AD,AD2)=1 to return TRUE for normalized addresses that occur exactly once in column AD.
- 4. Filter and keep unique records: Select the header row, go to Home > Filter, and filter the COUNTIF column by TRUE to isolate records appearing in only one of the two tables.
- 5. Save the extracted result: Go to Menu > Save As, choose Excel format (.xlsx), and save the workbook to preserve the extracted unique records.
Frequently Asked Questions
How do I extract rows that appear in only one of two Excel tables in WPS Spreadsheets?
Normalize the address column with =TRIM(SUBSTITUTE(G2,",","")), then add a COUNTIF check such as =COUNTIF(AD:AD,AD2)=1 and filter the helper column by TRUE to keep unique-only records.
What formula can I use to normalize addresses before comparing two property databases?
Use =TRIM(SUBSTITUTE(G2,",","")) to remove commas and trim extra spaces from the address cells. For multiple key fields, combine them with a delimiter such as =TRIM(SUBSTITUTE(A2&"|"&B2,",","")).
Does the UNIQUE and VSTACK method work when duplicate addresses have different attributes?
No, =UNIQUE(VSTACK(Table1,Table2),,TRUE) compares entire rows. If you need address-only matching with different attributes, use a normalized helper column with COUNTIF or COUNTIFS instead.
Can WPS Spreadsheets open and save Microsoft Excel files without losing data?
Yes, WPS Spreadsheets supports .xlsx, .xls, and other Excel formats. You can open, edit, filter, and save the workbook while maintaining compatibility with Microsoft Excel.
What should I do if my WPS version does not support the VSTACK function?
Use the recommended helper-column method: normalize the address with TRIM and SUBSTITUTE, add a COUNTIF or COUNTIFS check column, and filter by TRUE to retain records that occur only once.
