How to Extract Rows with Duplicate Values Across Multiple Columns in Excel
Question details
The user needs to extract records from a large dataset of over 40,000 rows where the combination of two specific fields ('Product' and 'Decrement') appears more than once.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a massive dataset to return only the rows that share identical combinations across two designated columns without causing severe lag.
- Observed behavior
- The user requires an efficient formula or method to isolate repeated combinations, as processing 40,000+ rows with single heavy array formulas can slow down calculation significantly.
Verify that there are no hidden trailing spaces in your 'Product' and 'Decrement' columns, as extraneous spaces will cause identical text combinations to be treated as unique by the calculation formulas.
Use Helper Columns with UNIQUE and FILTER
For large datasets exceeding 40,000 rows, separating the array calculation into helper columns significantly speeds up the workbook, reducing calculation time from several seconds to a fraction of a second.
This method avoids forcing Excel to calculate the entire array in memory at once. By writing intermediate arrays to worksheet ranges, you leverage Excel's optimized cell-level calculation engine.
In an empty column (e.g., Cell G3), combine the Product and Decrement fields using a delimiter. Enter the formula: =C3:C40002&"|"&D3:D40002
In the next column (e.g., Cell H3), extract combinations that appear exactly once. Enter the formula: =UNIQUE(G3#,,TRUE)
In your destination cell (e.g., Cell J3), use the FILTER and XMATCH functions to return rows that are NOT in the unique list. Enter: =FILTER(B3:E40002,ISNA(XMATCH(G3#,H3#)))
Use a Single LET Formula
A consolidated formula using the LET function keeps your worksheet clean by performing all concatenations and matches in memory. It is highly convenient but best suited for smaller datasets or fast processors.
Extract Duplicate Combinations Using Power Query
Power Query is highly efficient for heavy data processing and applies SQL-like logic (GROUP BY and JOIN) to handle 40,000+ rows without slowing down your workbook.
Extract and Filter Duplicates Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like LET, UNIQUE, FILTER, and XMATCH. You can use the exact same formulas to process datasets with over 40,000 rows quickly and efficiently without switching applications.
- 1. Open your dataset: Launch WPS Spreadsheet and open your document containing the 40,000+ rows.
- 2. Apply dynamic array functions: Select an empty cell and input your =UNIQUE() or =FILTER() formula just as you would in standard spreadsheet logic.
- 3. Process instantly: Press Enter to execute. WPS Spreadsheet will calculate the arrays and automatically spill the extracted duplicate rows into the adjacent cells.
Frequently Asked Questions
Why does my spreadsheet freeze when using LET on 40,000 rows?
Processing large array functions entirely in memory requires significant computational power. For huge datasets, storing intermediate calculations (like the concatenated keys) in worksheet cells as helper columns severely reduces the calculation time by letting the software cache the partial results.
What does the exactly_once argument in UNIQUE do?
By setting the third argument of the UNIQUE function to TRUE (e.g., UNIQUE(range, , TRUE)), the function returns only the items that appear exactly one time in the source array. This isolates the non-duplicates, making it very easy to filter them out using XMATCH when your goal is to locate the duplicates.
Why use a pipe symbol ('|') when concatenating columns?
Inserting a distinct delimiter like a pipe symbol prevents false positive matches. For instance, without a delimiter, concatenating '12' and '3' creates '123', which is identical to concatenating '1' and '23'. Using a delimiter results in '12|3' and '1|23', keeping the combinations uniquely identifiable.
Can I use Conditional Formatting instead of formulas to find duplicate combinations?
Yes. You can create a single helper column that concatenates the two criteria columns, then select that helper column and go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values. However, this method only colors the duplicate cells visually rather than extracting them to a new, clean list.
