Read Excel Tips Such As How To Copy Matching Records To A New Worksheet At BrightHub.com
Strategy: Use the Advanced Filter command with a Criteria range. Follow these steps.
-
First, copy the Customer heading from F1 to a blank area of the spreadsheet, as shown in Fig. 657. In this case, H1:H2 will be the criteria range for the filter.
-
In cell H2, enter the customer number that you want to extract. Make sure to use the same format in which the data exists in the database. In this case, 406 in F2 is a text value. If you want customer 406, copy cell F2 to H2 to ensure that they are in the same format.
-
Select a single cell in your data range. From the menu, select Data– Filter – Advanced Filter to display the Advanced Filter dialog, as shown in Fig. 658.
-
The Action setting should remain as Filter the List, In-Place. Move the cursor to the Criteria Range column and highlight the criteria range of H1:H2 with the mouse. Click OK to perform the Advanced Filter.
Result: As shown in Fig. 659, Excel will hide all of the rows that do not match the criteria.
In order to copy these cells to a new worksheet, you must select the visible cells only.
-
Highlight the range A1:F118. From the menu, select Edit – Go To. In the lower left corner of the Go To dialog, choose the Special button. On the Go To Special dialog box, in the right column, select Visible cells only, as shown in Fig. 660.
-
When you click OK, the display barely changes. As shown in Fig. 661, you will notice bands of white in between the rows. Excel is indicating to you that there are hidden rows that are not included in the selection.
-
You can now copy those rows with Ctrl+C and paste to a new worksheet.
Result:Only the matching rows are copied, as shown in Fig. 662.
Additional Details: On the original sheet, to clear the Advanced Filter and show all of the rows again, choose Data – Filter – Show All, as shown in Fig. 663.
Summary: One use of the Advanced Filter command is to extract a certain customer from a dataset.
Commands Discussed: Data – Filter – Advanced Filter; Edit – Go To – Special – Visible Cells Only; Data – Filter – Show All
Images
