All articles with 'consolidation' Tag
Last week, I asked my email newsletter readers to submit “one data analysis problem you are struggling with”. We called it BYOD – Bring your own data. More than 100 people have emailed various interesting (and often very difficult) problems. This week (between 16th of February to 20th of February), let’s take a look at some of these problems and solve them.
Consolidating data in different shapes
We can use either VBA or Excel’s consolidation features to combine data that has same shape (ie same number & type of columns). Here is one way to do it.
But what if we need to consolidate data that is in different shapes?
Something like above.
In such cases, we can use 3 powerful tools.
- Multiple Consolidation Ranges – Pivot Tables
- Power Query
So let’s examine how to use these approaches to consolidate data in different shapes.Continue »
In this post we will learn how to use the Advanced Filter option using VBA to allow us to filter our data on a separate sheet. This has been requested by a lot of our readers and here is how we will use them.Continue »
Last week, we learned how to use SQL and query data inside Excel. This week, lets talk about how we can use VBA to consolidate multiple data sheets from different workbooks into one single worksheet.Continue »
Splitting an Excel file in to many is easier than splitting bill in a restaurant among friends. All you need is advanced filters, a few lines of VBA code and some data. You can go splitting in no time.
Lets say you have lots of data like this in a file. And you want to split this in to multiple files, one per salesperson.
Solution – Split Data in to Multiple Files using Advanced Filters & VBA
The process of splitting data can be broken down to 4 steps …,Continue »
Wow, what a weekend it has been. Indian cricket team has won Cricket world cup 2011. It has been a highly entertaining tournament and the finals were just incredible.
Congratulations to Team India for winning Cricket World-cup 2011.
I am tempted to make a dashboard of sorts to show the journey of Team India in this world cup. But I could not get much time to work on anything. I will post something this week to celebrate the victory.
Read the rest of this post to get 5 recommended Excel resources to learn new things.Continue »
It has been quite a while since we had an excel links post. It doesn’t mean that I am not reading anything new on excel or charting. I have been reading lots of awesome articles and useful content and sharing it on my google reader recommendation list. But it is just that I have been […]Continue »
Long time PHD reader and mother of a lovely kid, Michelle, sent me a question in email that provoked me to write this post, I was wondering how to tabulate large amount of information gathered through surveys. Where I work customers are constantly handed survey sheets in order for us to measure how the service […]Continue »