All articles with 'consolidation' Tag

Recently I had to extract data from multiple credit card statements and combine them to one Excel table. In this tutorial, let me share you simple steps to deal with PDF data and extracting it to Excel.

Power Query (Get & Transform data in Excel) is a true game changer ?. It can simplify and automate various data activities. In this in-depth blog tutorial, learn all about this powerful time-saving technology. You will learn:

* What is Power Query and how to launch it from Excel or Power BI?
* How to connect and load data from any type of sources – excel files, CSVs, text files, web pages, PDF, folders etc.
* How to perform common data cleansing activities with Power Query
* Adding columns using rules
* Automating data collection, clean-up and publishing processes with Power Query
* Web scraping with Power Query
* SQL style Joins, merges, appends and unions with Power Query
* Consolidating data in a folder
* Data load, save & publish process
* Tips & tricks on using Power Query efficiently

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.

What now?

Of course Power Query to the rescue.

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.

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.

1. Multiple Consolidation Ranges – Pivot Tables
2. VBA
3. Power Query

So let’s examine how to use these approaches to consolidate data in different shapes.

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.

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.

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.

Context:

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 …,

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.

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 […]

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 […]