Quickly filter a table by combination of selected cell values using VBA
Filtering is one of the most used feature in Excel. It is a quick way to take lots of data and narrow down to the subset we want.
But here is one common filtering scenario that is slow as snail.
Imagine you are looking at some sort of sales data (if you can’t imagine, look at the above demo).
Now, you want to filter this list for gender=male, profession=self-employed, product category = chocolates and quantity = 1.
If you use the right click, filter > filter by selected value approach, this will take several clicks.
Wouldn’t it be cool if you can select the entire combination and say filter?
Unfortunately, no such feature exists in Excel.
But you are not aiming to be ordinary in Excel. You are aiming to be awesome in Excel. That means, you don’t take no for answer.
Fortunately, we can quickly write a VBA macro that filters a list by selection. So let’s do that.
Thank you, Houston meetup & Bonus tip
My mom will be very unhappy with this post. She always told me to focus on one thing at a time. But in this post we are talking about 3 things, not one. Sorry mom.
1. Thank you
I want to thank you for visiting chandoo.org & supporting us.
As I am about to leave to USA for attending Excelapalooza conference, I couldn’t help but be amazed at how much you have given me & my family. Almost 4.5 years ago, when I left my plush corporate job to work full time on Chandoo.org, I had no clue how the future will unfold. Today my heart is full of happiness, my family is secure, my site has grown by heaps and our community (especially you) is awesome.
Without your enthusiasm to learn and keen desire to become awesome, I would not have a job (of running this website). You inspire me to learn new things everyday so that I can share them with you.
Thank you for all the visits, clicks, comments, emails, tweets, likes, signups, purchases & love.
Thank you.
Top 10 things we struggle to do in Excel & awesome remedies for them
Recently we asked you, what do you struggle doing in Excel? 170 people responded to this survey and shared their struggles. In this post, lets examine the top 10 struggles according to you and awesome remedies for them.
Matching transactions using formulas [Accounting]
Imagine you are the head of Accounts Receivable department at a large company. Drab, I know, But humor me and imagine.
Now, every month you get a transaction report.
And you want to know which numbers are matching up.
i.e, if your company gave Vendor-0002 $872.34 on 1st of April, 2014 and your received below payments from them subsequently,
- $427.77 on 1st April
- $152.88 on 2nd April
- $291.69 on 2nd April
Then you consider the account matched since the total received is same as total payable.(427.77 + 152.88 + 291.69 = 872.34).
Free Invoice Template using Excel – Download
Anyone running a small business knows the oozing bits of joy when you hear a customer saying, “Can you send me an invoice?”
While creating an invoice is an easy task, if you want something that is professional looking, easy to manage and works well, then you are stuck.
That is where Excel really shines. By using an invoice template, you can quickly create and send invoices.
Today I want to share one such template with you all. Why? Because we are awesome like that.
Robust Dynamic (Cascading) Dropdowns Without VBA
Recently I posted about how you could construct dynamic (cascading) dropdowns that could easily handle multiple levels, like this: …and we saw that users could subsequently change upstream dropdowns in a way that would make downstream choices invalid, like this: In my previous post I used some VBA to clear out any ‘downstream’ choices if […]
Dynamic (Cascading) Dropdowns that reset on change
Dynamic dropdowns are a handy way to get your users to make choices based on what they’ve previously chosen, while steering them away from making invalid choices. Today we’re going to look at one that easily handles multiple levels, and we’ll take a look at what could go wrong. Let’s see one in action, shall […]