Some of the most basic tasks data analyst and other data professionals need to do is to split or combine data. In today’s article I will specifically be looking at how to easily split data automatically based on a given amount of criteria using an advanced filter and a macro.

 The Advanced Filter

The advanced filter is a functionality that is often overlooked in Excel, firstly because people don’t see it and secondly because everything you can do with an advanced filter you can just as easily do with a normal filter. So what is the point? Well when it comes to creating macro’s the advanced filter is a MUCH easier tool to work with. So it warrants some discussion. We can find the advanced filter under the  Data tab, in the Sort and Filter group.

To use the advanced filter we need 3 things:

1. A database with proper integrity. Check out my article on The most valuable Excel advice for more information about this.

2. A criteria table. This is a table with some or all of the Headings from your database, plus criteria underneath the headings. You can also duplicate the headings if you would like to create AND criteria, e.g between two dates as seen below. You can also add more rows for OR criteria, but in this exercise my table will look like the one below with only two rows. For more on this visit https://support.office.com/en-us/article/Filter-by-using-advanced-criteria-4c9222fe-8529-4cd7-a898-3f16abdff32b

3. And finally based on your requirements, a place to copy the data to.

For the purpose of this exercise I will be assuming my database is  located in A1:H1901 and my criteria table is on the same sheet in J1:R2. I will be copying the information to J4.

How to use the Advanced Filter

1. To use the advanced filter first select any 1 cell in your main database, then click on the advanced filter icon on data tab.
2. Select the Copy to Another Location option and complete the Criteria Range and copy to range like below (Obviously adjusting for your own scenario)

3. Press OK.

This should copy the data conforming to the criteria table to the requested location. Awesome!

So now if you change the data in the criteria table then… Nothing happens… Well that sucks, but that is what Macros were created for right?

Creating the Macro

1. So all we need to do is to create a Macro by going to the View tab and then clicking on the drop down under the Macros button and select Record Macro.

2. Then create a Macro called FilterData (remember Macro names can only be one word), you can assign a shortcut key, Ctrl + Shift + F (Just put a big F in the little box).

3. When you press OK Excel will start recording. Remember Macros are VERY SENSITIVE and you cannot undo them, so do exactly the steps below say. No more, no less.

4. All we need to do now is to go back to the Data tab and click on the Advanced filter button again under the Sort and Filter group. All the criteria should still be selected (just make sure to select Copy to another Location again), if it is not just select it again as before and press OK.

5. Finally we must stop recording by clicking on the Stop button that will be in the bottom right hand side of your screen.

Ok that’s it. Now you should be able to change the criteria in the criteria table and then just press Ctrl+Shift + F and it should work perfectly. Awesome!

Enjoy Filtering. And change your thinking.

For more training visit CompanionICT.co.za or call our offices at 0119555927.