How to Create a Unique Filtered List from Another Sheet in Excel
Question details
The user wants to extract a unique list of values, such as customer delivery numbers, from another worksheet using matching criteria, while avoiding common formula errors.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting and filtering specific data records from a summary sheet into a clean, unique list on a different worksheet.
- Observed behavior
- Formula errors occur due to empty source data sheets, incorrect array sizes, or misaligned criteria ranges when applying dynamic array functions.
Ensure that your source worksheet contains actual data and that you have identified the exact bounded ranges (e.g., A2:A100 instead of entire columns) for your criteria.
Combine UNIQUE and FILTER Functions with Bounded Ranges
Use the dynamic array functions UNIQUE and FILTER together to pull distinct values matching your criteria from another worksheet.
To prevent calculation lag and matching errors, it is best to use bounded ranges instead of referencing entire columns. Wrapping the formula with IFERROR or using the built-in empty handler of the FILTER function ensures clean output even when no records match.
Open your workbook and identify the exact ranges on your source sheet, such as 'OB Summary'!A2:A500 for the criteria column and 'OB Summary'!B2:B500 for the return values.
Click the target cell on your new sheet and type =FILTER('OB Summary'!B2:B500, 'OB Summary'!A2:A500="YourCriteria", "") to pull matching records. The final pair of quotes returns a blank if nothing is found.
Modify the formula to remove duplicates by wrapping it in UNIQUE: =UNIQUE(FILTER('OB Summary'!B2:B500, 'OB Summary'!A2:A500="YourCriteria", "")).
If you need specific columns from a wider filtered array, wrap your formula with CHOOSECOLS, or use BYROW and XMATCH to handle multiple complex criteria lookups simultaneously.
Create Unique Filtered Lists Easily with WPS Office
WPS Spreadsheet fully supports advanced dynamic array formulas including UNIQUE, FILTER, BYROW, and CHOOSECOLS, making it incredibly simple to pull clean, filtered data from other sheets without complex workarounds.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the file containing your source data and target sheets.
- 2. Select the target cell: Navigate to the sheet where you want your unique list to appear and click the starting cell.
- 3. Input the combined formula: Type =UNIQUE(FILTER('SourceSheet'!B2:B100, 'SourceSheet'!A2:A100="Criteria", "")) into the formula bar.
- 4. Press Enter to execute: Press Enter. The dynamic array will automatically spill the filtered, unique values down the column instantly.
Frequently Asked Questions
Why does my FILTER function return a #CALC! or #VALUE! error?
A #CALC! error typically means the FILTER function returned empty results. You can fix this by adding a value like "" to the [if_empty] argument of the FILTER function. A #VALUE! error usually occurs when the criteria range size does not match the source data range exactly.
Can I filter data from multiple columns at once?
Yes, the FILTER function allows you to select a multi-column range for the array argument. The UNIQUE function will then evaluate the entire row across those columns to ensure uniqueness. You can also nest CHOOSECOLS to return only specific columns from the filtered array.
Does the UNIQUE function update automatically when the source data changes?
Yes. Because UNIQUE and FILTER are dynamic array formulas, whenever the source data in the other sheet updates, the resulting filtered list will automatically recalculate and adjust its spilled range to reflect the new data.
How can I filter out blank rows from my source data?
You can add an additional condition to your FILTER formula using multiplication for AND logic to exclude blanks. For example: =FILTER(B2:B100, (A2:A100="Criteria")*(B2:B100<>""), "").
