The Excel HelpDesk Support team received the following request for help:
Problem – Need to move filtered rows in a table to another sheet
I built a table in excel where I am comparing items. Some of those items are things will require further action after analysis. I created a column in the table with a drop down menu for yes/no. If the item requires further analysis I select “yes”.
What I wish would happen:
I would like a copy of the items that have a “yes” selected, to move to another tab called “items for deletion”. Is this possible? Where can I go to learn to do it?
Convert the data to ‘normal’ Excel
If you filter in a table object in Excel and then copy the filtered rows to your ‘items for deletion’ sheet and delete the filtered rows all rows in between will get deleted as well.
Some of the advantages of Excel’s table object are easy sorting, filtering, formatting, quick totals, always visible headers and dynamic charts.
One possible solution would be to remove the table by copying the data and pasting the values only to a new sheet (use Paste Special and then select values) then delete the ‘Orders for Review’ sheet and rename the new sheet to ‘Orders for Review’. You can then freeze the top row to keep your headers visible and you only need to add a filter once.
When you have the data without the table object you can just filter on the items that need further analysis (value = “yes”), copy them to the ‘items for deletion’ sheet and delete them from the “orders for review” sheet.