All articles with 'consolidation' Tag
Say you want to combine multiple Excel files, but there is a twist. Each file has few tabs (worksheets) and you want to combine like for like, ie , all Sheet1s to one dataset, all Sheet2s to another dataset…
To make matters interesting each sheet has a different format.
Of course Power Query to the rescue.Continue »
Do you work with accounting or finance data? You are going to love, no scratch that, adore Power Query. It can save you precious time, make you look like a hero in-front of clients and keep you sane.
In this masterclass, learn all about Power Query for accountants (and other kinds of finance people) & 5 tips.
Why Power Query?
Power Query is a data processing software. Using Power Query, you can create process driven programs (queries) to collect data, clean or reshape it, calculate things and publish final output as a table. Once you create a query in PQ, when the underlying data changes, you just refresh the query and brand new data will be fetched, cleaned and published for you.
For example, you can use Power Query to combine ledger files from various accounts to one master file with Power Query. No more VBA or manual copy pasting. Just one query and you are done.Continue »
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 »