Excel pull data from another sheet based on criteria VBA

If you’ve got a long list of sales orders on an Excel worksheet, how can you send one sales rep’s orders to a different sheet? There are lots of creative ways to do that, but my favourite solution is to use an Excel Advanced Filter. Would you use that solution too, or something different?

Inhaltsverzeichnis Show

Advanced Filter Button
Advanced Filter Dialog Box
Why Bother With Advanced Filters?
Advanced Filter Setting to Send Data
List of Unique Records
Advanced Filter Macros
Flexible Filter Macro
Try Advanced Filters
Video: Send Data to Different Sheet
Accessing the Visual Basic Editor
Running the Macro

Advanced Filter Button

Advanced filters are one of Excel’s most under-rated and under-used features, and we can blame the Excel Ribbon for that.

Here’s the proof. If you didn’t already know how awesome Advanced Filters are, would you click that “Advanced” button on the Ribbon’s Data tab?

“Ooooh – Options for filtering using complex criteria! Let me try that!”

Said no one, ever.

Advanced Filter Dialog Box

For the adventurous few Excel users who DO click that Advanced button, here’s the hidden “treasure” that they discover – a tiny, confusing, old-fashioned dialog box.

Most people will click the X at the top right corner, and never go back there again!

Why Bother With Advanced Filters?

Since Advanced Filters look so confusing and complex, why should you bother learning how to use them?

The best thing about Advanced Filters is that they can:

magically send data to a different worksheet
based on simple or complex criteria
without using macros!

Advanced Filter Setting to Send Data

Here’s an example of the filter setting you’d enter, to send data to a different sheet, if the customer name is MegaMart

With the settings in the screen shot below, the filter will:

Send a copy of the data
from Table1
to the sheet named Filtered Data, starting in cell A1
based on the criteria in cell F1:F2

In the criteria range:

Cell F1 has a column heading from Table1 – Customer
Cell F2 has a customer name – MegaMart

List of Unique Records

The second-best thing about Advanced Filters is that they can create a list of unique records, using one column or multiple columns.

Here’s an example of the filter setting you’d enter, to send a unique list of customer names to a different sheet

With the settings in the screen shot below, the filter will:

Send a copy of the data
from the Customer column of Table 1
to the sheet named Filtered Data, starting in cell A1
with NO criteria
sending unique records only

Advanced Filter Macros

Advanced filters work well with macros too, and they’re lightning fast! There are sample files on my Contextures site that you can test, to see how speedy Advanced Filters are.

One sample file – Send List to Sheet or File (Specific Settings) – has buttons on the worksheet, so it’s easy for you to test the macros.

Flexible Filter Macro

Here’s a screen shot from the Send List to Sheets (Flexible) sample file. When you run the macro in this workbook, it prompts you to enter a column name. You could choose a different column each time, to create whatever data extracts you need.

I typed “region”, and the macro ran two Advanced Filters:

Created a unique list of regions
Sent each region’s data to a new sheet, named for that region

Try Advanced Filters

If you haven’t tried Advanced Filters yet, I hope you’ll give them a chance! There are basic steps and a video on my Advanced Filters Intro page.

If you’re ready to try the macros, get the sample files on the Advanced Filter Macros page, and see how quick and efficient Advanced Filters are at extracting data.

Video: Send Data to Different Sheet

With an advanced filter, you can extract data to a different sheet. Watch this video to see the steps.

There are written steps on the Advanced Filters page, and sample files to download

_______________________________

Hi Rob,

In addition to PivotTable solution which Argelo suggested here are couple more. Which one to use depends on many factors, from Excel versions and do you use predefined forms or not; to personal preferences.

Anyway, first one using array formulas (Ctrl+Shift+Enter) to add. Let assume your data is located as

orders

month

The pattern for the formula which extract data from master list based on criteria is described here http://www.exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/4/ and in many other places.

In our case in monthly sheet in A2 let add

=IFERROR( INDEX(Orders!$A$2:$C$660, SMALL( IF((MONTH(Orders!$A$2:$A$660)=$H$1)*(Orders!$A$2:$A$660>1), ROW(Orders!$A$2:$A$660) - ROW(Orders!$A$2)+1 ),ROW(1:1) ),1 ),"" )

We take 660-rows range in Orders to have some gap. If fortunatelly you have more it is to be increased. Formula return first date in orders for defined in cell $H$1 month. Dates is first column in the range, in bold above in formula.

Copy this formula in B2 and change 1 on 3 to receive data from third column "Make".

To receive the cost by type for this record in C2 add

=IFERROR( INDEX(Orders!$F$2:$F$660, MATCH(1, (Orders!$A$2:$A$660=$A2)* (Orders!$C$2:$C$660=$B2)* (Orders!$B$2:$B$660=C$1), 0) ), 0)

which finds the cost for the order for given Date and Make (A2 and B2), and Type for this column (C1).

Copy this cell to the right till last column type (F2).

Select cells from C2 to F2 and apply cusom format (Ctrl+1) to them

[$$-en-US]#,##0.00;;;@

which 'hides' zeroes in the cells.

Now select all cells in row 2 and drag them down till out of your ordes in the month, better more. Finally select sheet tab and copy it for another months. Only H1 is enough to change for each given month.

In formulas above size of orders list could be defined dynamically, but bit easier if you use for orders Excel Table (that's only one of pros). Let name that table as Orders, when in monthly tabs alternatives to above formulas will be

=IFERROR( INDEX(Orders[[Order Date]:[Make]], SMALL( IF((MONTH(Orders[Order Date])=$H$1)*(Orders[Order Date]>1), ROW(Orders[Order Date]) - ROW(Orders[[#Headers],[Order Date]]) ),ROW(1:1) ),1 ),"" )

and

=IFERROR( INDEX(Orders[Cost], MATCH(1, (Orders[Order Date]=$A2)* (Orders[Make]=$B2)* (Orders[Type]=C$1), 0) ), 0)

And finally the option with using Power Query (aka Get & Transform in Excel 2016). First, we load and pivot for types Orders table. Could be done in few clicks in user interface, here is code generated

let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content], ChangeType = Table.TransformColumnTypes(Source,{ {"Order Date", type date}, {"Type", type text}, {"Make", type text}, {"Model", type text}, {"Year", Int64.Type}, {"Cost", type number} }), RemoveUnused = Table.RemoveColumns(ChangeType,{"Model", "Year"}), ReordereColumns = Table.ReorderColumns(RemoveUnused, {"Order Date", "Make", "Type", "Cost"} ), PivotType = Table.Pivot(ReordereColumns, List.Distinct(ReordereColumns[Type]), "Type", "Cost") in PivotType

Load the query as connection. After that make the reference on that query fro each month adding filter for the monh

let Source = Orders, FilterMonth = Table.SelectRows(Source, each Date.Month([Order Date]) = 9) in FilterMonth

and load each of them into Excel sheet

Fileas are attached

Excel is extremely powerful even using just the basic functionality of adding data to cells and spreadsheets, sorting and cultivating that data into a beautiful work of cellular delight. Yet Excel is capable of far more than the standard editing of cell contents allows, through the magic of Visual Basic for Applications scripts, or VBA.

We’ll briefly explore one simple example of using VBA in Excel to extract values from one worksheet, but this is just the tip of the iceberg for what Excel and VBA can accomplish together.

Accessing the Visual Basic Editor

To begin, you’ll need to locate the Visual Basic Editor in Excel. Finding this will depend on what version of Excel you are running, but for most modern versions, the Visual Basic Editor can be found under the Developer tab of the menu ribbon.

Once located, simply click Macros, enter a macro name (we’ll use MySum for this example), then click create to open the editor and begin scripting.

The Script

Once inside the editor you’ll see the declaration of your MySum macro, which is just a standard vba function that we can edit to do our bidding.

For our example, perhaps we have a worksheet named Sales and we want to use this macro to total (sum) the first 25 values in column B.

Thus to begin our script, we need to select the appropriate worksheet named Sales, and then using that selected object, narrow our selection by grabbing the specific cells (known as a range of cells) that we want to total.

Sub MySum() Worksheets("Sales").Range("B1:B25") End Sub

Now we actually need to get the total or sum of all those values, so we wrap the previous object call in a WorksheetFunction of Sum, like so:

Sub MySum() WorksheetFunction.Sum(Worksheets("Sales").Range("B1:B25")) End Sub

Yet that’s not the final step. In our example, we want to extract this sum total from the Sales worksheet and then insert that total value into the current cell we have selected when running this macro.

To accomplish this, we’ll use the ActiveCell object, setting its Value to be equal to our summed total:

Sub MySum() ActiveCell.Value = WorksheetFunction.Sum(Worksheets("Sales").Range("B1:B25")) End Sub

Alright, now our macro is complete, so let’s save and go back to our actual spreadsheet to test it out.

Running the Macro

To ensure it is functional, select a different spreadsheet, then select a single cell where you want the sum to be inserted.

To run a saved macro, just go back to the Developer tab, select Macros again, and choose your MySum macro. The cell contents should now be replaced with the sum total of the Sales column B values we specified.