How to Return Unique Text Matches on Separate Lines in Excel
Question details
The user wants to extract a unique list of values from one column based on multiple criteria across other columns, returning them on separate lines within a single cell or as a spilled array.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting and formatting specific data based on multiple exact matches (e.g., specific text and numbers in different columns).
- Observed behavior
- A need to generate a deduplicated list of values separated by line breaks in a single cell or populated across multiple rows using dynamic array formulas.
Ensure you are using a version of Excel or WPS Spreadsheet that supports Dynamic Array formulas, such as Microsoft 365, Excel 2021, or the latest version of WPS Office.
Use TEXTJOIN, UNIQUE, and FILTER for a Single Cell Result
This method extracts unique matches based on your criteria and combines them into one cell with each item on a new line.
By nesting FILTER inside UNIQUE, you isolate the deduplicated matches. Wrapping them in TEXTJOIN with CHAR(10) allows you to format the results with line breaks in a single cell.
Click on the specific cell where you want the final list to appear.
Enter the formula: =TEXTJOIN(CHAR(10),TRUE,UNIQUE(FILTER(L:L,(D:D="Apples")*(I:I=500)*(Q:Q="Absent")*(S:S=1),""))) and press Enter.
To ensure the CHAR(10) line breaks render correctly, keep the cell selected, navigate to the Home tab on the top ribbon, and click the Wrap Text button.
Use UNIQUE and FILTER for a Spilled Array
If you prefer the results to populate down multiple rows instead of being contained in a single cell, use this simplified formula.
Extract and Deduplicate Data Seamlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE, FILTER, and TEXTJOIN. You can effortlessly process complex criteria and deduplicate data using a familiar interface.
- 1. Open your workbook: Launch WPS Spreadsheet and open the .xlsx file containing your source data.
- 2. Apply the dynamic formula: Select your output cell and input the exact same formula: =UNIQUE(FILTER(L:L,(D:D="Apples")*(I:I=500)*(Q:Q="Absent")*(S:S=1),"")).
- 3. Format if necessary: If you added TEXTJOIN, click the 'Wrap Text' icon in the Home tab to display the line breaks properly.
- 4. Save your work: Click File > Save to securely store your data in the universal .xlsx format.
Frequently Asked Questions
Why is my TEXTJOIN formula not showing data on separate lines?
For the line breaks generated by CHAR(10) to become visible, you must enable the 'Wrap Text' feature. Select your output cell, navigate to the Home tab, and click the 'Wrap Text' button.
What causes a #CALC! or #SPILL! error when using these formulas?
A #CALC! error typically indicates that the FILTER function found no matching results based on your criteria. A #SPILL! error occurs when the formula attempts to return multiple rows, but there is existing text or data blocking the spilled range. Clearing the cells below the formula will resolve the #SPILL! error.
Can I use these dynamic array functions in older versions of Excel?
No. UNIQUE and FILTER are dynamic array functions introduced in Microsoft 365 and Excel 2021. Older versions like Excel 2016 or 2019 do not support them natively. We recommend using a modern alternative like WPS Office to access these functions for free.
