How to Filter Excel Data by Multiple Material Numbers
Question details
The user needs to filter a large Excel dataset to show only the rows that match a specific list of multiple material numbers.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing a primary data table against a secondary list of required material numbers to extract or display the matching records.
- Observed behavior
- Instead of selecting filter items manually one by one, the user requires a dynamic method or advanced function to instantly filter the dataset using an existing criteria list.
Ensure your main dataset and your criteria list both have matching column headers, and that there are no empty rows interrupting your data ranges.
Use FILTER and COUNTIF Formulas for Dynamic Extraction
This method dynamically outputs the filtered data to a new range and automatically updates if the source data or criteria list changes.
The FILTER function extracts records based on a specified condition. By wrapping it with COUNTIF, you can check each row's material number against your entire list of required numbers in one formula.
Locate the range for your main data (e.g., A2:T25189), the column containing the material numbers to check (e.g., C2:C25189), and the list of material numbers you want to filter by (e.g., V2:V51).
Click on an empty cell where you want the filtered results to begin. Ensure there is enough blank space below and to the right for the data to spill.
Type the formula: =FILTER(A2:T25189,COUNTIF(V2:V51,C2:C25189)>0) and press Enter.
The filtered data will spill into the adjacent cells, displaying only the rows that match the material numbers in your specific criteria list.
Filter In-Place Using the Advanced Filter Tool
Advanced Filter allows you to filter your existing dataset in-place using a separate criteria range containing multiple values.
Easily Filter and Manage Data with WPS Spreadsheet
WPS Office offers a fully featured Spreadsheet program that supports advanced filtering and complex array formulas like FILTER and COUNTIF. Handle massive datasets and multiple criteria lists smoothly without spending a dime.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the main data and your material numbers.
- 2. Apply the Advanced Filter: Navigate to the Data tab, click on Advanced Filter, and highlight your main data list and criteria range.
- 3. Execute the filter: Select 'Filter the list, in-place' and click OK to instantly view your matched material numbers.
Frequently Asked Questions
Can I filter data and extract it to a different sheet?
Yes. If you are using the FILTER function, you can simply write the formula on a new sheet and reference the ranges on the original sheet. If using Advanced Filter, select 'Copy to another location' and specify the destination cell on the active sheet.
Why is my FILTER function returning a #CALC! error?
The #CALC! error occurs when the FILTER function finds no matches based on your criteria list. You can add a third argument to the formula to handle this gracefully, like =FILTER(A2:T25189,COUNTIF(V2:V51,C2:C25189)>0, "No matches"), which will display a custom message instead of an error.
Does Advanced Filter work if the column headers don't match exactly?
No. The header in your criteria range must perfectly match the spelling and spacing of the corresponding column header in your main dataset for the Advanced Filter to recognize the condition.
