How to Populate Sheet2 with Duplicate Matching Data from Sheet1
Question details
The user needs to copy rows containing matching data across specific columns from Sheet1 to Sheet2, ensuring that all duplicate matches are retained.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Extracting records from a master sheet to a secondary sheet based on internal column matching rules (like identical chassis numbers or license IDs).
- Observed behavior
- Standard lookup functions only return the first match. The goal is to accurately populate all matching duplicate rows dynamically into a new sheet.
Ensure your dataset is organized with clear column headers in Sheet1. Note the specific column ranges you need to compare (e.g., Column A and Column C) before applying the extraction formula.
Use the FILTER Function to Extract Matching Duplicates
The FILTER function is the most efficient method to extract all matching rows dynamically without losing duplicate entries.
Traditional lookup functions like VLOOKUP or XLOOKUP are generally not suitable for this task because they stop searching once they find the first match. The FILTER function allows you to retrieve an entire array of matches, making it perfect for copying multiple duplicate records.
Open Sheet2 and select the top-left cell of the destination range (e.g., cell A2) where you want the matching data to start.
Enter the FILTER formula to compare your target columns. For example, to extract rows where Column A matches Column C in Sheet1, type: =FILTER(Sheet1!A2:C1000, Sheet1!A2:A1000=Sheet1!C2:C1000, "No matches")
Press Enter. The formula will automatically spill the matching data into the adjacent rows and columns, populating Sheet2 with all matching duplicate records.
Use Advanced Filter to Copy Data to Another Sheet
If you are using an older spreadsheet version that does not support the dynamic FILTER function, you can use the built-in Advanced Filter tool.
Extract and Filter Data Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like FILTER, enabling you to manage large datasets, extract matching duplicates, and organize your workbooks dynamically.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the master data in Sheet1.
- 2. Apply the FILTER formula: Navigate to Sheet2, select your starting cell, and input your array formula, such as =FILTER(Sheet1!A2:C1000, Sheet1!A2:A1000=Sheet1!C2:C1000, "No matches").
- 3. Press Enter to execute: Hit Enter to instantly spill all duplicate matching records directly into the new sheet.
Frequently Asked Questions
Why does XLOOKUP only return the first matching record?
By design, standard lookup functions like XLOOKUP and VLOOKUP evaluate the dataset from top to bottom and stop searching as soon as they find the first exact match. To return multiple matches or duplicate records, you must use array functions like FILTER.
What should I do if my FILTER formula returns a #CALC! or #VALUE! error?
A #CALC! error usually means the FILTER function found no matching records, and the 'if_empty' argument wasn't provided. A #VALUE! error often occurs if the array sizes do not match. Ensure your comparison ranges (e.g., A2:A1000 and C2:C1000) are exactly the same height.
Can I filter based on matches across multiple criteria?
Yes, you can include multiple criteria in the FILTER function by using the asterisk (*) for 'AND' logic or the plus sign (+) for 'OR' logic. For example: =FILTER(A2:C1000, (A2:A1000=C2:C1000)*(B2:B1000="Active")).
