An introduction to using Data Consolidate to add two data sets. Episode 786 introduces the concept. Tomorrows episode will throw in an interesting twist.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast. I’m Bill Jelen.
Today, we're going to talk about using data consolidation.
I actually have a question that came in but we'll talk about that question tomorrow.
First, I want to introduce data consolidation.
This is a feature that's been around for a long time and, frankly, you know, once pivot tables came around, I started using consolidation less, but it does have its place.
It's a very interesting, powerful command.
We have here a data set with sales reps going down the left-hand side and the time period going across the top, Q1, Q2, Q3, and then I have another data set, same sales reps, although, you know, not necessarily in the same order [ unintelligible – 00:44 ] that, and I actually have fewer of them, and time period going across the top, and not the same time periods.
The first data set has Q1, Q2, Q3, the second data set has Q3, Q4, and I want to smash all these together into one single data set.
So, I'm going to come here to a blank section.
I'm going to use DATA and then CONSOLIDATE, and basically I'm going to specify each REFERENCE.
So, I will specify, basically, A1 through D27 and click ADD, and then the second, so, F1 through H15, and click ADD, and I say that I want to use the LABELS in the TOP ROW and the LEFT COLUMN, and when I click OK, the CONSOLIDATE command is going to take all the time periods from the first set, Q1, Q2, Q4, and the time periods from the second set, Q3, Q4, and create a superset going across the top.
We're also going to have every unique occurrence of sales rep that appears down the left-hand side, and wherever a sales rep appeared a couple of times, it's going to add those numbers up.
So, here in Q3, we should find some values that are the consolidation of the other two data sets.
DATA CONSOLIDATE, very, very powerful, and we'll talk more about DATA CONSOLIDATE in tomorrow's netcast.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Today, we're going to talk about using data consolidation.
I actually have a question that came in but we'll talk about that question tomorrow.
First, I want to introduce data consolidation.
This is a feature that's been around for a long time and, frankly, you know, once pivot tables came around, I started using consolidation less, but it does have its place.
It's a very interesting, powerful command.
We have here a data set with sales reps going down the left-hand side and the time period going across the top, Q1, Q2, Q3, and then I have another data set, same sales reps, although, you know, not necessarily in the same order [ unintelligible – 00:44 ] that, and I actually have fewer of them, and time period going across the top, and not the same time periods.
The first data set has Q1, Q2, Q3, the second data set has Q3, Q4, and I want to smash all these together into one single data set.
So, I'm going to come here to a blank section.
I'm going to use DATA and then CONSOLIDATE, and basically I'm going to specify each REFERENCE.
So, I will specify, basically, A1 through D27 and click ADD, and then the second, so, F1 through H15, and click ADD, and I say that I want to use the LABELS in the TOP ROW and the LEFT COLUMN, and when I click OK, the CONSOLIDATE command is going to take all the time periods from the first set, Q1, Q2, Q4, and the time periods from the second set, Q3, Q4, and create a superset going across the top.
We're also going to have every unique occurrence of sales rep that appears down the left-hand side, and wherever a sales rep appeared a couple of times, it's going to add those numbers up.
So, here in Q3, we should find some values that are the consolidation of the other two data sets.
DATA CONSOLIDATE, very, very powerful, and we'll talk more about DATA CONSOLIDATE in tomorrow's netcast.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
