How to Filter Excel Data by Today's Date and Vehicle Make
Question details
Extract specific columns from a dataset using multiple criteria, specifically filtering for records that match today's date and a selected text string (like vehicle make), while removing any duplicate entries.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Generating a daily delivery summary where users need to dynamically pull unique shipment numbers based on the current date and a specific manufacturer.
- Observed behavior
- The goal is to successfully return only the relevant columns (e.g., delivery numbers) that meet both conditions without manual filtering.
Ensure your spreadsheet software supports dynamic array functions. Functions like FILTER, UNIQUE, and CHOOSECOLS are available in Microsoft 365, Excel 2021, and modern versions of WPS Spreadsheet.
Combine UNIQUE, CHOOSECOLS, and FILTER Functions
Use a nested dynamic array formula to apply multiple conditions, select specific columns, and remove duplicate rows instantly.
To filter by multiple criteria in Excel, you can multiply conditions together inside the FILTER function. Multiplying acts as an 'AND' operator, meaning both conditions must be true.
After filtering the rows, the CHOOSECOLS function extracts only the columns you need, and the UNIQUE function ensures no duplicate records are returned.
Locate the columns containing your criteria. For example, assume 'Start Ship Date' is in column I (I2:I1000) and 'Vehicle Make' is in column J (J2:J1000) on a sheet named 'OB Summary Update'.
Create the core filter logic using multiplication for multiple criteria: FILTER('OB Summary Update'!A2:J1000, ('OB Summary Update'!I2:I1000=TODAY()) * ('OB Summary Update'!J2:J1000="FORD")).
Wrap the FILTER function in CHOOSECOLS to return only the specific columns you need. To return column B (the 2nd column) and column J (the 10th column), add CHOOSECOLS(..., 2, 10).
Finally, wrap the entire formula in the UNIQUE function to strip out duplicate records: =UNIQUE(CHOOSECOLS(FILTER('OB Summary Update'!A2:J1000,('OB Summary Update'!I2:I1000=TODAY())*('OB Summary Update'!J2:J1000="FORD")),2,10)).
Use WPS Spreadsheet for Advanced Array Formulas
WPS Spreadsheet fully supports dynamic array functions like FILTER, UNIQUE, and CHOOSECOLS. You can seamlessly perform complex multi-criteria data extraction with zero lag, completely compatible with Excel formulas.
- 1. Open your dataset: Launch WPS Spreadsheet and open your daily delivery log or database.
- 2. Select the target cell: Click on the top-left cell where you want the filtered summary report to appear.
- 3. Enter the nested formula: Type the combined =UNIQUE(CHOOSECOLS(FILTER(...))) formula into the formula bar.
- 4. Press Enter to execute: Press Enter. The results will automatically spill into the adjacent rows and columns.
Frequently Asked Questions
What does the asterisk (*) mean in the FILTER function?
In dynamic array formulas, the asterisk (*) acts as the 'AND' logical operator. By multiplying two arrays of TRUE/FALSE conditions, Excel only returns rows where both conditions are TRUE (1 * 1 = 1).
Can I use 'OR' logic instead of 'AND' for multiple criteria?
Yes. To apply 'OR' logic, use the plus sign (+) instead of the asterisk (*). For example, using (range1=condition1)+(range2=condition2) will return rows where either condition is met.
Why am I getting a #CALC! error?
A #CALC! error typically occurs when the FILTER function finds no records matching your criteria. You can fix this by providing a fallback value in the third argument of the FILTER function, like FILTER(range, criteria, "No Data").
Are CHOOSECOLS and UNIQUE functions available in all Excel versions?
No, these are modern dynamic array functions. They are available in Microsoft 365, Excel 2021, Excel for the Web, and updated versions of WPS Spreadsheet. Older versions like Excel 2016 or 2019 do not support them natively.
