How To Extract Data From One Sheet To Another In Excel Using Vba

Contextures

Excel Advanced Filter Introduction

Run across how to gear up an Avant-garde Filter in Excel to create a list of unique items, or to excerpt specific items to a unlike worksheet, based on criteria. You can also use complex criteria with an Advanced Filter, so it is useful when a simple Excel AutoFilter tin't do what you demand.

Tip: In Excel 365, you lot can utilize the Excel FILTER office to create dynamic filters based on criteria.

Writer: Debra Dalgleish

Create an Excel Advanced Filter

Watch this video to run into the steps for setting up your criteria range, and running an Advanced Filter. The written instructions for all the steps are beneath the video.

Create an Excel Advanced Filter

Hither are the steps for setting up your data, and creating an Advanced Filter in Microsoft Excel.

-- Gear up the database

-- Prepare the Criteria Range (optional)

-- Ready the Extract Range (optional)

-- Employ the Excel Advanced Filter

1. Set up the database

To set up the database on the spreadsheet, follow these dataset guidelines, so the Advanced Filter can work correctly.

The first row (A1:D1) has headings.
Each column in the data set must accept a unique heading -- duplicate headings will crusade issues when running an Advanced Filter.
Subsequent rows contain data.
There are no blank rows within the database.
There is a blank row at the end of the database, and a blank column at the correct.

Database

2. Ready the Criteria Range (optional)

In the criteria range for an Excel advanced filter, you lot tin can set the rules for the data that should remain visible after the filter is practical. Y'all can use one benchmark, or several.

Alert: When yous employ text criteria for an avant-garde filter, Excel interprets it equally "begins with". If you need an exact match for the text values, meet the Exact Match example below.

In this example, cells F1:F2 are the criteria range.
The heading in F1 exactly matches a column header (D1) in the database.
Cell F2 contains the criterion. The > (greater than) operator is used, with the number 500 (no $ sign is included).

Criteria Range

After the Excel advanced filter is applied, orders with a prison cell value in the Total column, that is greater than $500, will remain visible.

Other operators include:

< less than
<= less than or equal to
>= greater than or equal to
<> not equal to

3. Gear up upwardly the Excerpt Range (optional)

If you plan to copy the information to another location (on the same sheet, or a unlike worksheet), you tin can specify the columns that y'all want to extract.

NOTE: If you want to extract ALL columns, y'all tin can exit the extract range empty for the Excel advanced filter.

Select the cell at the pinnacle left of the range for the extracted information.
Blazon the headings for the columns that you desire to extract.
1. These must be an exact match for the column headings in the header row of the data, in spelling and punctuation
2. Column society tin be different
3. Any or all of columns tin can be included.

Criteria Range

iv. Apply the Excel Advanced Filter

Follow these steps to apply the Avant-garde Filter tool:

NOTE: If you want to ship the information to a different sail, follow the steps in the section Transport Data to Another Sheet, below

Select any cell in the database that y'all want to filter
On the Excel Ribbon, click the Data tab
In the Sort & Filter group, click the Advanced command, to open up the Advanced Filter dialog box

Criteria Range

Advanced Filter Dialog Box

When the Advanced Filter dialog box opens, follow these steps:

In the Action section, you can choose if Excel filters the listing in place, or copy the results to another location.
Excel should automatically observe the list range. If not, you can select the cells on the worksheet.
- Tip: To manually select the List range cells,
- --click on the first heading cell (cell A1),
- --so press Shift, and click on the concluding data cell (cell D55)
Select the criteria range on the worksheet
If yous are copying to a new location, select a starting prison cell for the copy
- Alarm: If you lot copy to another location:
  - ALL cells below the extract range will be cleared when the Advanced Filter is applied.
Click OK, to close the Excel Advanced Filter dialog box

Apply Filter

Filter Unique Records

With an Excel Advanced Filter, y'all can filter the information in place, or to a dissimilar location. When applying the advanced filter, you tin specify that you desire to come across unique items merely. With that pick selected, all the duplicates are removed from the filtered list, and the upshot is a unique list of items.

The written instructions for this technique are beneath the video.

Filter Unique Records

Yous tin can employ an Excel Advanced Filter to extract a list of unique items in the database. For case,

get a listing of customers from an gild listing
compile a list of products sold.

In this example, the unique list is copied to a unlike location, and the original data is unchanged.

Note: The original data must contain a heading, or the first item may be duplicated in the results.

Select a cell in the database.
On the Excel Ribbon'due south Information tab, click the Avant-garde push.
In the Avant-garde Filter dialog box, choose 'Copy to another location'.
For the List range, select the cavalcade(southward) from which y'all want to extract the unique values.
Leave the Criteria Range bare.
Select a starting cell for the Re-create to location.
Add a check mark to the Unique records only box.
Click OK.

Duplicates in Unique Records

Occasionally, if you run an Avant-garde Filter for Unique Records Only, you might see duplicates in the results.

This tin happen when in that location are numbers in the data, and Excel detects hidden differences between the numbers.

To learn more than about this problem, go to the Remove Duplicates Problem page. Y'all'll see why the duplicates are listed (floating point precision), and how to fix the trouble by using the ROUND role.

advanced filter extract unique records

Video: Remove Duplicates

In Excel 2007, and later versions, there is a Remove Duplicates tool. Unlike the Avant-garde Filter's Unique Records Only option, this does non leave the original list unchanged -- it completely removes all duplicate items from the list. Only the commencement example of each item is left.

You can download the sample file that was used in this video.

Extract Data to Some other Worksheet

With an advanced filter, yous tin extract data to a different canvass. Scout this video to encounter the steps, and the written instructions are beneath the video, for sending the filtered data to a split up sheet.

Video Timeline

0:00 Introduction
0:11 Sales Orders
0:35 Top Orders
1:00 Excerpt & Criteria
1:44 Advanced Filter
3:44 Check the Data
4:16 Warnings
4:52 Become the Excel File

Extract Data to Another Worksheet

If your Excel data is on 1 sheet, you lot can transport filtered information to a different sail, past using an Advanced Filter. In this instance, the data is on Sheet1, and volition exist copied to Sheet2.

Become to Sheet2 (encounter the steps on the video above)
Select a cell in an unused part of the sheet (prison cell C4 in this example).
On the Excel Ribbon's Data tab, click Advanced
Choose Copy to another location.
Click in the List Range box
Select Sheet1, and select the database.
(optional) Click in the Criteria range box.
Select the criteria range
Click in the Copy to box.
Select the jail cell on Sheet2 in which you want the results to start, or select the headings that y'all accept typed on Sheet2.
(optional) Cheque the box for Unique Values Only
Click OK, to close the dialogue box, and apply the filter

Extract Data to Another Sheet

Setting up the Criteria Range

These examples show how to set upwardly criteria ranges for one criterion, or multiple criteria, using text, numbers, and wildcards. You tin besides use formulas in the criteria range -- see examples on this folio: Advanced Filters -- Circuitous Criteria

WARNING: When you enter text criteria, Excel interprets it as "begins with". If you need an exact lucifer, run across the Exact Lucifer example beneath.

Criteria Range Location

The examples on this page show the criteria range nigh the information, but that isn't required.

The criteria range can be on the same canvass as the information, or on a different sheet.
The criteria range can exist direct above the data, or in columns to the left or right of the data.

Notation:

If the criteria range is on the same sail, and in the aforementioned rows as the data, the criteria cells might be subconscious when y'all apply an "In Identify" advanced filter in.

That will non affect the avant-garde filter -- you just won't be able to encounter some of the criteria range.

AND vs OR

If a record meets all criteria on one row in the criteria area, it will pass through the Excel advanced filter.

AND Rule

In this case, there is a single row of criteria rules.

advanced filter AND criteria

Because all criteria are in the same row, the Advanced filter uses AND logic:

customer name must begin with "MegaMart"
AND product proper noun must begin with "Cookies"
AND full must exist greater than 500

OR Rules

In this example, at that place are 3 rows with criteria rules, with i criterion in each row.

advanced filter OR

Criteria on dissimilar rows are joined with an OR operator. In the second case, the post-obit OR logic is practical:

customer name must brainstorm with "MegaMart"
OR product name must begin with "Cookies"
OR full must be greater than 500.

AND/OR Rules

Past using multiple rows, you can combine the AND and OR operators, as in this case:

advanced filter AND OR

The following AND / OR logic is applied past the Advanced Filter:

customer name must begin with "MegaMart" AND product name must begin with "Cookies"
- OR
product proper name must brainstorm with "Cookies" AND total must be greater than 500.

Using Wildcards in Criteria

Use wildcard characters to filter for a text string in a cell.

-- The * Wildcard (asterisk)

-- The ? wildcard (question mark)

-- The ~ wildcard (tilde)

The * wildcard

The asterisk (*) wildcard character represents whatsoever number of characters in that position, including zero characters.

NOTE: Because Excel interprets text criteria as "begins with", y'all don't demand to add the * wildcard at the stop of the string

In this case, the Client wildcard criteria is: *o*r

Two client names, FoodMart and Mart-o-rama, incorporate an "o", followed by an "r" anywhere later on the "o". The records for those ii customers appear in the filtered list.

advanced filter wildcard asterisk

The ? wildcard

The question mark (?) wildcard grapheme represents i characters in that position.

In this case whatsoever product proper name that begins with c, followed past any unmarried grapheme, and then ke, will laissez passer through the Excel avant-garde filter. Both Coke and Cake are in the filtered results.

advanced filter wildcard question mark

The ~ wildcard

The tilde (~) wildcard character lets you search for characters that are used as wildcards.

In the starting time example, an asterisk is in the criteria cell -- Skillful*Eats -- so whatsoever customer name that brainstorm with Good, then whatsoever characters, followed past Eats, will pass through the Excel advanced filter.

advanced filter with wildcard asterisk

To find only the customer name that begins with Good*Eats, use a tilde character (~) in front of the asterisk in the criteria cell: Good~*Eats

advanced filter tilde

Criteria Examples

Shown below, there are a couple of other Advance Filter criteria examples:

-- Extract Items in a Range

-- Excerpt Items with Specific Text

For many more examples, get to the post-obit page

-- More than Advanced Filter Criteria Examples

Extract Items in a Range

To excerpt a list of items in a range of numbers or dates, you can use two columns for one of the fields. In this example, two columns are used for the Total field.

If y'all enter 2 criteria on the same row in the criteria range, you lot create an AND statement. In this example, any records that are extracted must be:

greater than or equal to 500
- AND
less than or equal to 750

Extract Items

Extract Items with Specific Text

When you use text equally criteria with an Excel avant-garde filter, Excel finds all items that begin with that text.

For example, if yous type "Water ice" as a criterion, Excel finds "Water ice", "Ice Cream" and "Water ice Milk", because they all begin with "Ice".

exact match for ice

To excerpt only the records that are an verbal match for the criteria text, apply the following format in the criteria range (upper or lower case won't bear upon the results):

="=ice"

The offset equal sign starts the cell formula, and the 2nd equal sign sets the criteria equally "equal to" the text value.

Multiple Conditions

More than Advanced Filter Criteria Examples

There are many more than criteria examples on the Advanced Filters -- Complex Criteria page.

For example, run across these criteria examples, and many more:

use formulas with Excel functions and jail cell reference in criteria cells
filter top 5 records
filter top records after specific date
filter items that lucifer a listing
observe records where data in specific cells does not friction match, such as invoice amount, and amount paid
- The video beneath shows this example

Invoice Amount Does Not Lucifer Payment Amount

This short video shows the "unmatched data" criteria case. The written instructions are on the Avant-garde Filters -- Complex Criteria folio, along with many other Advanced Filter criteria examples, and files to download.

Where Should Criteria Range Exist?

Some Excel lessons insist that you should put the criteria range directly above the information. This is non necessary -- utilise the location that works best for you.

The examples on this page bear witness the criteria range virtually the data, merely you lot don't need to get out it there.

The criteria range can exist on the same sheet as the data, or on a different sheet.
The criteria range tin can be directly above the data, or in columns to the left or correct of the data.

Note

If the criteria range is on the same sheet, and in the same rows as the data, the criteria cells might be hidden when you utilize an "In Place" advanced filter in.

The hidden criteria rows volition not affect the advanced filter -- you just won't be able to see some of the criteria range.

Using Advanced Filter Macros

Advanced filters piece of work well with macros besides, they will help yous save time, for filters that you lot need to run often. For example, use Advance Filter macros for your weekly reports, instead of doing steps manually every week.

If y'all're set up to effort the macros, go to the sample files on the Avant-garde Filter Macros page, and see how to get started. That page shows you:

how to tape a macro while you run an Advanced Filter
make simple changes to the macro's VBA code
run that macro later, to chop-chop apply an Advanced Filter

Advanced Filter macros run quickly, compared to other methods of moving data in Excel, then it's worth your time to use them when you can!

Advanced Filter Macro Buttons

For case, here's a screen shot from one of the Avant-garde Filter macro sample files on the Advanced Filter Macros page.

The sample file, named Transport List to Sheet or File (Specific Settings), has buttons on the worksheet, then information technology'south easy for yous to exam the macros.

Merely click on of the worksheet buttons to run a macro, that will:

Create a separate canvass with the records for each sales representative
Or, create a separate Excel workbook with the records for each sales representative

send list to named sheet or file

Advanced Filter Format & Formulas

In another i of the sample files for Avant-garde Filter Macros, named Filter & Re-create Formatting, there is VBA code that copies the formatting and formulas from the original information.

In the screen shot below, y'all tin can see that the extracted information has all of cell formatting, formulas, hyperlinks, and other items. This can exist important, if you're extracting part of the original information, to sent to someone else.

send list to named sheet or file

Save Time with Advanced Filter Macros

If you're ready to try the macros, and salvage time in your Excel work, go to the sample files on the Advanced Filter Macros page, and see how quick and efficient Advanced Filters are at extracting data.

Get the Sample Workbooks

Advanced Filter Examples: Download zipped Excel advanced filter workbook with sample data and criteria. The zipped Excel file is in xlsx format, and does not comprise whatsoever VBA or macros.
Send Information to Dissimilar Sheet: Download the Excel workbook with a named table, where you can send the information to a different worksheet. The zipped Excel file is in xlsx format, and does not incorporate any VBA or macros.