WPS Spreadsheet: Find Rows Shared by Four Excel Tables
Question details
Identify and return only the rows whose four related values appear in all four Excel tables.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Four tables contain approximately 8,000 records each, and matching rows need to be found by comparing four related values.
- Observed behavior
- The required result is a filtered list containing only rows that exist in all four tables.
Confirm that the four comparison columns use the same data type and formatting in every table. Place each table in a separate range and verify the first and last data rows before editing the formulas.
Use COUNTIFS and FILTER to return rows shared by all four tables
Use this formula-based method when the four tables have matching key columns and your WPS Spreadsheet version supports FILTER.
The helper formula checks whether all four values in each row occur together in each of the other tables. FILTER then returns only the rows marked TRUE. Replace the example ranges with the actual four-column ranges for your workbook.
In a blank column beside the first table, select the first data cell and enter a formula such as =AND(COUNTIFS($A$2:$A$8001,A2,$B$2:$B$8001,B2,$C$2:$C$8001,C2,$D$2:$D$8001,D2)>0,COUNTIFS($F$2:$F$8001,A2,$G$2:$G$8001,B2,$H$2:$H$8001,C2,$I$2:$I$8001,D2)>0,COUNTIFS($K$2:$K$8001,A2,$L$2:$L$8001,B2,$M$2:$M$8001,C2,$N$2:$N$8001,D2)>0,COUNTIFS($P$2:$P$8001,A2,$Q$2:$Q$8001,B2,$R$2:$R$8001,C2,$S$2:$S$8001,D2)>0).
Press Enter, then drag the fill handle down through all rows in the first table, or copy the formula to the end of the approximately 8,000-row range. TRUE indicates that the four-value combination exists in every table.
Select an empty output cell and enter =FILTER(A2:D8001,E2:E8001=TRUE,"No matching rows"), changing A2:D8001 to the first table's four value columns and E2:E8001 to the match-check column.
Check the spilled results for the expected four-column combinations, then use Home > Copy and Paste Special > Values if you need a fixed, non-formula result.
Merge the tables with Inner Join operations
Use a query-based merge when each table has thousands of rows or when you need a repeatable import and matching process.
Match and Extract Shared Records in WPS Spreadsheet
WPS Spreadsheet can compare four key values across large Excel-compatible tables with COUNTIFS, mark complete matches, and extract the matching rows with FILTER. For repeatable workflows, merge tables with Inner Join operations when the required query tools are available.
- 1. Import or open the workbook: Open WPS Spreadsheet and choose Menu > Open to load the Excel workbook containing the four tables.
- 2. Prepare the comparison ranges: Confirm the four key columns and place the tables in separate ranges with consistent headers and data types.
- 3. Check each row across all tables: Use COUNTIFS in a helper column to test whether the four values in each row occur in every table.
- 4. Extract the matches: Enter a FILTER formula in an empty output area to return only rows marked TRUE by the helper column.
Frequently Asked Questions
How do I compare four columns instead of one key column?
Include all four column pairs in each COUNTIFS expression, using the same row's four cells as criteria. Combine the four COUNTIFS tests with AND so a row is accepted only when the complete combination exists in every table.
What should I do if FILTER is not available in my WPS Spreadsheet version?
Use the helper column to mark TRUE matches, then turn on Data > AutoFilter and filter that column to TRUE. You can copy the visible rows to a separate worksheet.
Can I use Excel table names instead of cell ranges?
Yes. If your tables are named, replace cell ranges with structured references such as Table2[Key1] and Table2[Key2]. Keep the criteria columns aligned across all four tables.
Why are expected matches missing from the result?
Check for extra spaces, different number or text formats, inconsistent capitalization, and blank cells in the four key columns. Clean or standardize the values before running COUNTIFS.
Is Power Query better for four tables with about 8,000 rows each?
A sequence of Inner Join merges is often easier to refresh for large or recurring datasets. Merge the tables on all four key columns, using Inner Join at each stage, and load the final result.
