Advanced Filter

Learn Excel from MrExcel Podcast, Episode 2060: Excel Advanced Filter

Hey, welcome back to MrExcel netcast, I'm Bill Jelen.  Today's question sent in by Mort.  Mort, he has a 100,000 rows of data and he's interested in Columns A, B and D where Column C matches some particular year.  So he wants a person to enter a year and then get Columns A, B and D.  And Mort has some VBA where he’s using arrays to do this and I said, “Wait a second, you know, the advanced filter would do this a lot better.”  Alright, and now just to review, I went back, I looked back through my videos.  I haven't covered advanced filter in a long time so we should talk about this. 

Advanced filter requires an input range and then at least one of these: a criteria range or an output range.  Although today we're going to use both of those.  Alright, so the input range is your data and you have to have headings above the data.  So, Mort does not have headings and so I'm going to temporarily insert a row up here and just do like Field 1.  Mort knows what his data is and so he could put real headings up there.  And we're not using anything's called-these data over in Columns E through O, so I don't have to add headings there, alright?  So now, A1 through D, 100000 becomes my input range.  And then the output range and the criteria range – Well, the output range is just a list of the headings that you want.  So I'm going to put the output range here and we don't need Field 3 so I'll just take that off to the side.  So now, this range right here, A1 through C1 becomes my output range that tells Excel which fields I want from the input range.  And, they could be in a different order if you wanted to reorder the things, like if I want Field 4 first, and then Field 1 then Field 2.  And again, these would be real headings like invoice number.  I just don't know what Mort's data looks like.

And then, the criteria range is a heading and what value you want.  So let's say I was trying to get anything in year 2014.  This becomes the criteria range like that.  Alright, just a word of caution here.  I'm in Excel 2016 and it's possible to do an advanced filter between two sheets in Excel 2016 but if you go way back, and I don't remember what way back is, maybe 2003, I'm not sure.  At some point in the past, it used to be that you couldn't do an advanced filter from one sheet to another sheet so you would have to come here and name your input range.  You would have to create a name here.  MyName or something like that, alright?  And that would be the way that you would be able to pull this off, alright.  Not necessarily in Excel 2016 but again, I'm not sure if Mort’s going to be running this in older versions of the data.   

Alright, so back here at Data, we go to Advanced Filter, alright. And we're going to Copy to another location that enables our output range there.  Alright, so the list range, where is the data?  Because I'm in Excel 2016, I'm going to go point to the Data, instead of using the name range - So that's my input range. The criteria range is those cells right there and then, where we’re going to - output to, it's just going to be these three cells there. And then we click OK.  Alright, and BAM!  That's how fast, fast it is.  And what if we wanted a different year?  If we wanted a different year we would delete the results, put in 2015, and then do an advanced filter again, Copy to another location, click OK and there's all the 2015 records.  Lightning fast.

Alright now, while I'm a fan of advanced filter in regular Excel, I was a massive fan of advanced filter in VBA, alright, because VBA makes advance filter really, really, really simple.  Alright, so we are going to write some code here for Mort, assuming that Mort's data has no headings and we're going to have to temporarily add the headings, alright?  So, I'll switch over to VBA, Alt+F11 and we're going to run this from the worksheet that has the data.  So: Dim WS As Worksheet, Set WS = ActiveSheet.  And then, insert Row 1 and just add some headings: A, B, Year, and D.  Figure out how many rows of data we have today and then starting from cell A1 going out 4 columns down to the final row, name that to be input range.  Alright, and then this is actually Mort's code right here, where he asked for the InputBox, gets the year they want and then he asks what year or what they want to name the new sheet, alright.  So it's going to actually insert a sheet on the Fly and then I- Dimension a new sheet, WSN, as the ActiveSheet.  So I know that WS is the original sheet, WSN is the new sheet that was just added.  On the new sheet, put the criteria range so under Column E there's the heading that matches this heading here, and then, whichever answer they gave us goes in E2.  The output range is going to be my other three headings: A, B and D.  And again, if you or Mort changes these to real headings which is probably a better thing to do than A, B, D, and you'd also change these to real headings, alright?  So all of this is just a little bit of pre-work here.  This one awesome line of code will do the entire advanced filter.  So, from the InputRange we do an AdvancedFilter, we're going to copy.  That's our choice filter in place or copy.  The CriteriaRange is E1 to E2, the CopyToRange is A to C.  Unique values –No, we want all of the values.  Alright, that one line of code there does all of the magic of looping through all the records or replaces looping through all the records or doing the arrays.  And then we're done, we will clear the criteria range and then delete Row 1 back on the original worksheet. 

Okay, so let's switch back here to our data.  We will make it easy to run this, so: Insert, a Shape and call this Filter, Home, Center, Center, Larger, Larger, Larger, right-click, Assign Macro, and assign it to MacroForMort.  Alright, so here we go.  We're going to do a test.  See we're on the data sheet, click the Filter, what year do we want?  We want 2015.  What do I want to call it?  I want to call it 2015, alright.  And BAM! There it’s done.  That's how fast, that's how fast this is. 

Now, since Mort’s original data didn't have headings, maybe this data shouldn't have headings.  So let's go Alt+F11, right here we want to clear the criteria range.  We will also Rows(1).Delete.  Alright, so now the next time we were on this, it will get rid of those headings.  And let's just - Rather than run the whole thing quickly, let's take a look here with the 2014.  So I'll select one cell on the Data, Alt+F11, and I want to run just down to the point where we do the advanced filter.  So we can look and see what the whole macro is doing here.  So we'll click Run, and I want to get 2014.  2014, alright.  And so, press F8, we’re about to do the advanced filter.  We can roll back to Excel here and see what's happened.

First thing that's happened- Now, first thing that’s happened is we've added a new temporary row with the headings.  Inserted this worksheet, built a criteria range with a heading and what year they input, chose the fields that we want to do and then back in VBA, I'll run the next line of codes, that's F8 that does the advanced filter right there.  It's incredibly fast and you'll see that that has actually now brought us all the records.  From there, it's just a bit of cleanup, delete this, delete this.  I'll go back to the data and delete Row 1 and we will be good to go.  So I'll just let the rest of that run, remove that breakpoint, alright?  So there's the VBA.  For me, this is I     think the fastest way, fastest way to go.

Alright, episode recap: The advanced filter is more advanced than the regular filter because it can copy to a new range.  And now, I didn't show it in this video but you can build complex criteria where Field 1 = A or Field 2 = A.  The regular auto filter can't do that and it's fast.  Mort is trying to process 100,000 rows in VBA by using an array or by looping, but it'll always be faster to use Excel building features than writing your own code.  You need to define an input range, criteria range, output range.  You always need an input range in at least one of these, although today I'm using both.   For the input range, single row of headings above the data.  So we're going to add a temporary row of headings.   For the output range, the same headings that you want to extract, alright.  So, you know, if it was A, B, Year and D, we’ll just put A, B and D as the output range. For the criteria range, headings in Row 1.  So this is the field I want to build a criteria on and this is the value I'm looking for.  Complications: Older versions of Excel will not allow the output range to be on another sheet, so, potentially your code will run back then.  You want to use a named range for the input range because from this sheet, you know, the named range, even though it's on another sheet, the sheet believes the name branches on the current sheet.  So that would allow the advanced filter to work.

Alright, well, there you have it.  I want to thank Mort for sending that question in.  I want to thank you for stopping by.  We'll see you next time for another netcast from MrExcel.