How to Copy Rows to Another Sheet Based on Cell Value in Excel
Question details
The user needs to copy specific rows from one spreadsheet to another automatically when a cell in a designated column contains specific text values (such as LTI or MTI).
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Filtering and extracting data to a new worksheet based on multiple text criteria using an OR logic operation, while preserving the original date formats.
- Observed behavior
- Rows containing either 'LTI' or 'MTI' in the specified column are successfully extracted to Sheet2, along with their corresponding date entries.
Ensure your source data has clear column headers and identify the exact cell ranges of the data you want to filter before applying array formulas.
Use the FILTER Function to Extract Rows
The FILTER function is the most efficient way to dynamically copy rows to another sheet based on multiple criteria without manual copying and pasting.
By utilizing the FILTER function, you can create a dynamic array that automatically updates if your source data changes.
When using multiple criteria in the FILTER function, the plus sign (+) acts as an 'OR' operator, allowing the formula to return rows that meet any of the specified conditions.
Navigate to Sheet2 (or your target worksheet) and click on the top-left cell where you want the extracted data to appear.
Type the formula =FILTER(Sheet1!A2:B5,(Sheet1!A2:A5="LTI")+(Sheet1!A2:A5="MTI"),"") into the formula bar and press Enter.
Modify the A2:B5 range to match the full dimensions of your source data on Sheet1. Ensure that the A2:A5 range targets the specific column containing the LTI or MTI event types.
Select the newly populated column containing the associated dates, right-click, choose 'Format Cells', and apply the correct Date format to display the numeric values as standard dates.
Easily Filter and Extract Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like FILTER, allowing you to dynamically extract, analyze, and copy rows based on specific conditions with high performance and zero cost.
- 1. Open your workbook: Launch WPS Office and open the Spreadsheet file containing the data you want to filter.
- 2. Apply the dynamic array formula: Navigate to a new sheet, select an empty cell, and type your =FILTER formula to set up your extraction criteria.
- 3. Format and analyze: Right-click the extracted date columns to apply standard date formatting, and seamlessly save your work in your preferred format.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! or #VALUE! error?
A #CALC! error usually occurs if no data matches your criteria. You can prevent this by including a final argument in the formula, such as "", to return a blank cell instead of an error. A #VALUE! error typically means your source data range and criteria range are different sizes.
Can I filter data based on more than two text conditions?
Yes, you can add multiple conditions by extending the pattern with additional plus (+) signs. For example: (Range="LTI")+(Range="MTI")+(Range="Other").
How do I filter rows based on an 'AND' condition instead of 'OR'?
To require multiple conditions to be met simultaneously (AND logic), use an asterisk (*) instead of a plus (+). For example: (Range1="LTI")*(Range2="Active").
Will the copied rows update automatically if the original data changes?
Yes, the FILTER function outputs a dynamic array. Any modifications, additions, or deletions in the source data will instantly update the extracted results on the destination sheet.
