Anyone who has worked with large sets of data in excel has used filtering to get a better look at the data. Take an example where I have the list of the top 200 female tennis players in the world and need to find players that fit a specific criteria.

The easier method is to simply select the cells at the top of the columns:

And then click: “Data/Filter/Auto-Filter

Then, I can simply select a criteria such as a specific country or even select a “custom filter” where I can select up to two conditions that must be met.

That certainly gives you a lot of different possibilities right? Take a look at a filter on all Russian players:

There are however many reasons why that might not be enough. What if for example you wanted to specify 3 different countries? Or you wanted to quickly process a list of different queries? Or you have very custom data set queries?

In Comes Advanced Filters In Excel VBA

It is a simple feature, very easy to use but it’s one of the most powerful functions in excel in my opinion. How does it work? You need a few things to have this work. If you are not familiar with excel macros, it might be a good idea to take a look at our excel macros introduction. Back to advanced filtering though, in order to get it done, you need:

-For the data set to have headers
-Right next to it (or it can be located elsewhere), you need to have the same headers, here is my example:

Then, simply enter the criteria that you would like. In this example, I would like all players from these countries:

RUS
ITA
CZE
POL
GER

You can see a screenshot here:

Here is the code that I’m using, as you can see it’s very simple.

There are 3 ranges that you can “edit”:

-the first one is where the data that you are filtering is found
-the second one is where the criteria should be found. You need to edit the end of the range (in this case O6) to only include lines that have a criteria. If I had put O7, that would have meant including an “empty” criteria – this is critical
-the third one is where you’d like to display results

The result is quite extensive, you can see a shot here.

I could also add additional criteria points, for example, only including players that have played over 25 tournaments as you can see here:

The new result is:

It is extremely powerful and you can use it very large sets of data, adding multiple types of crtieria, etc. It is also very quick.

As always, you can download this spreadhseet here!

A Commentary

If you follow the news, it seems like you can’t avoid the Occupy Wall Street movement.  Is it mob behavior, or merely an expression of freedom of speech?  The truth of course is probably somewhere in the vast middle.  What upsets me about the movement, however, is the Robin Hood Fallacy that it is right and proper to “take from the rich and give to the poor.”  These are simply my own views and not those of the owner of this site or anyone else, but I think that a society that rewards mediocrity  will never thrive.  And isn’t that why we’re in the current state of affairs in the first place?  Let me put it another way: Freud once said that the two basic desires behind almost everything men or women do are to:

1) Have sex.  (of course, this is Freud after all).

2) To be important.

So should we now stop rewarding hard work, ingenuity, and the other intangibles that make one rich and/or successful?  That make one important?

If we do, then what is left for the masses to strive for?

Now let’s take a step back for a moment.  I understand that some people genuinely need assistance.  I am not saying we should all rely upon a complete 100% Darwin survival of the fittest capitalist society.  Things happen in life, and there are illnesses, children that are orphaned, and untreatable mental issues, to name a few circumstances, where the greater assistance of society–whether it be governmental or charitable is required.

Nor am I saying that all “rich people” have earned their position.  Some people have received their money through ill-gotten gains.  Others have been the benefactors of generational wealth being passed down, like how an ocean flows through tributaries and deposits salt on distant creeks and lakes.

But what could be potentially dangerous about a movement of the “99% v. the 1%” is the belief that we cannot take accountability for our own stations in life.  It’s a slippery slope to travel once the “rich” are vilified, because most of the “rich” have obtained their success–despite popular perception–from hard work, creativity, and the taking of risks.

You can spend your free time hating such people, but I suggest you are better served trying to emulate them.

By now some of you are probably thinking that I was born with a silver spoon, and maybe even that this silver spoon should be shoved up my ass.  I cannot deny that I have had opportunities not available to everyone in the world.  But I did grow up in a lower middle class “blue collar family.”  And I did have to work for what little I have in this world.  And I will continue to work and to do everything I can to better position life for me, my wife, and my own children and grandchildren that hopefully will follow.  To me, if I cannot do these things, then I wonder what’s the point in living?

I have six figure student loan debt from college and graduate school.  Few would stand to benefit more than I if “all debts were to be forgiven.”  But I made those choices, and so long as I have my health and mind, I will do everything I have to in order to repay my debts, just as I have been doing for several years now.  And when the job market got bleak, I set out to start my own company.  And in my free time I do freelance writing work to put food on the table.  And I scrimp and save every dollar that I can to meet my obligations.

Now I’m beginning to worry that I sound like I’m up on a soapbox.  I understand that people are suffering right now, and I’m not saying it’s their fault.  But I do think we have been raised to look to the government–or our parents–or just about anything outside ourselves for sustenance when things get difficult.  And just like a domesticated animal, something is lost in that process.  And part of that something is freedom.

Dear Readers,

We have been fortunate to get many different emails about your excel questions and issues and would love to get even more feedback from you.

What type of information are you looking to get?

Which functions do you have trouble using?

Do you also use macros and if so, do you need any assistance doing so?

What would you like to do through excel but have trouble doing?

Are there tasks that you perform very often that should/could be automated but that you do not know how to do?

Please either write a comment or email at thefinancialblogger (at) gmail (dot) com.

You can also give us the questions here!

There’s an issue that seems to inflict every man in my family (although in a Simpons-type twist, the women are spared)–my term for it is interest deficit disorder.  You see, we can’t be satisfied with just one hobby or interest.  Instead, we must collect them throughout our lives.  Maybe our hobby is the collection of hobbies?  But the problem with this “Renaissance Man” existence is that we never truly excel at everything.

Sure, we can play a half-decent guitar, set-up the average tropical aquarium, and play a solid if unspectacular round of golf, but at the end of the day what does this all add up to besides a way to kill time?

Most true geniuses spend their entire lives fixated on one field, or even one detail.  Meanwhile, I’m writing half-written novels and hand-crafting barely respectable home-made beer.  What gives?

Unfortunately, the tunnel-vision, pick one thing and become great at it point of view also has its critics.  Some argue that a wide variety of pursuits is normal, valuable, and a better endeavor than one singular purpose.  They argue the old saying, that variety is the spice of life.  This all becomes very frustrating and confusing.

So what I decided to do is test out sticking to one focus v. my usual scatterbrained approach to the world.  I focused for one month entirely upon my business.  Here is what I noticed from my little personal test:

•  In the beginning, I was on a “new self improvement” high just based upon pouring 100% of myself into something.  It became novel, sort of like starting a new interest.
• Therefore, in the beginning the results were beyond my wildest expectations.  I was either working, reading to improve my skills in my field, or trying to find new clients literally 90% of my waking hours.
• But then, after two weeks I started to feel an overwhelming tiredness.  I missed blogging, and working on my novel, and going jogging.
• Productivity began to decline.   I would begin to spend all my time doing one thing, but in all reality my head would be in a fog and my concentration would be poor.