This article is probably the most interesting and exciting article we’ve written so far.
This is because, in just a minute, I will reveal how VBA ‘geniuses’ come up with brilliant solutions to programming problems.
And I am not talking about small insights either; I am talking about coming up with ideas that will make your co-workers/boss/clients gasp in wonder.
So without further ado, here is a first glimpse into the mind of the programming genius.
So, Do I need to memorize 500 formulas?
Well… No!
You don’t need to memorize formulas or VBA syntax to be able to come up with creative ideas. You do, however, need to understand and use the ‘creative thinking process’ that these VBA ‘geniuses’ employ.
Now, it’s important to understand that 99% of these ‘geniuses’ use intuition. Thus, they cannot fully describe the steps they take to come up with a new idea.
Once they learn of a problem they tend to obsessively think about it, toss and turn it in their heads, until finally a solution ‘comes to them ‘ (usually, in the shower).
But today, we are going to lift the veil and give you an inside look into the minds of those geniuses. We are going to show you a…
Simple and easy system to create brilliant VBA/Formula solutions
It’s the same system we’ve been using here at PDF2XL to consistently create productivity-boosting solutions for our customers.
This system almost always works and, in most cases, it has a considerable impact on the productivity of our clients.
Are you ready? Let’s go.
“Creativity is a fresh pair of eyes” (Woodrow Wilson)
In order to come up with a new idea, you need to look at the problem (or the work process) in a different way.
Or, in other words … to “re-frame” the problem.
By systematically re-framing problems, we can easily come up with solutions.
By systematically re-framing processes, we can easily find ways to improve them.
And so, to come up with a brilliant new solution, all you have to do is apply a set of re-framing techniques to a problem or a process until the brilliant idea ‘comes to you’.
So let’s go to the first (and one of the most powerful) re-framing techniques…
The 80/20 Creativity Turbo-Engine
You know the 80/20 rule (the Pareto principle),
80% of the wealth is owned by 20% of the population.
80% of the sales come from 20% of the clients.
And so on.
Well, The Pareto principle is my favorite re-framing technique. It means that you use VBA/Formulas to automate just 20% of the work and get a huge productivity increase (sometimes even more than 80%).
So, whenever we work with a client we ask ourselves:
“Can we use VBA/Formulas to automate just a small part of the process, but in such a way that will significantly increase the client’s overall productivity?”
Here’s an example…
How we cut this retailer’s work-time in half
One of our clients (let’s call him Ted) owns a hardware retail business. When we first started working with him, he was pulling 12-hour days, 7 days a week.
Most of Ted’s work centers on preparing price quotes for existing or new customers. Each RFP (request for proposal) Ted receives usually includes several items.
When preparing a quote, Ted used to search his vendor catalogs for each of the items and then select the item from the vendor that offered the lowest price for it.
Searching for the lowest-priced item across his entire collection of vendor catalogs took most of Ted’s day, but since Ted is working in a very price-competitive niche, he had no other choice.
Ted asked us if we could help him convert all his vendor catalogs into a database where he would just enter the item ID and get the lowest priced vendor item.
But that was too much work.
Ted works with over 40 vendors. Each of those vendors manufactures thousands of items. And to top it all, every once in a while the vendors issue new catalogs and update their prices.
A database that could completely solve Ted’s problem would contain more than 100,000 items and Ted would have to hire a full-time employee whose only job would be to keep the database updated.
But here is where the 80/20 rule comes to the rescue
After we discussed the problem with Ted, we discovered that 50% of the items ordered were the same 100 items.
So we created a database just for those 100 items.
And while this was a partial solution, a solution that solved less than 1% of the problem, it was responsible for reducing Ted’s workload by half.
Bear in mind that this database didn’t solve the entire problem.
Ted still had to look up item prices almost every time he received an RFP, but it did save him more than 50% of the time he used to spend on the quote creation process.
Needless to say that Ted was overjoyed.
To sum up
Whenever you are faced with an Excel problem that seems unsolvable or with a process that defies automation try to re-frame the problem by asking yourself:
“Can I write a VBA Macro or come up with a formula that will automate a small part of the work but in a way that will create a big productivity increase?”
It’s not a fancy technique, but it has worked wonders for us, and if you use it, it will do the same for you.
P.S.
If you’d like to get an immediate boost in productivity try our PDF to Excel Converter. It’ll free up most of the time you spend on re-keying PDF data.
