Yet another solution to matching data from two different worksheets...the Data Consolidate command. Episode 929 shows you how.
This video is the 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 video is the 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:
Yeah. Alright. Welcome back to the MrExcel netcast.
This week, let me talk about how to match up data from 2 different worksheets.
They have one field in common, for example, CUSTOMER.
CUSTOMERS and FORECAST here.
CUSTOMERS, ORDER, COST, and GROSS PROFIT.
They're not the same list of customers on both sheets.
So, yet another way to solve this is a very, very old feature, a feature that's been around since…well, for a long time anyway.
I know it was back in Excel ’93 which wasn't called Excel ’93.
The one before Excel ’95 had this feature.
I remember using it back then.
It's called the CONSOLIDATE feature.
It’s on the DATA tab.
So, we go to DATA, CONSOLIDATE.
You actually want to start in a blank section of the spreadsheet.
This is where output report’s going to go, so come here to CONSOLIDATE on the DATA menu, and we're going to build references to both sets of data.
So, the first reference here is on the forecast sheet.
We’ll click ADD, and then the second reference, I’ll click on the ORDERS tab.
Make sure that it has the same left column.
So in both cases, CUSTOMER was the left column.
I want to USE LABELS IN the TOP ROW and LEFT COLUMN, and click OK, and you see that, very quickly, without using the multiple consolidation range in the pivot table, we get CUSTOMERS along the left-hand side.
It's a superset of all customers that appear in either one list or the other, and then, very nicely here, they don't alphabetize.
It shows up in the exact same order that we added things -- so, FORECAST first and then ORDER and then COST and then GROSS PROFIT.
Now, one thing in the pivot table, we had a couple of annoyances, a couple of annoyances here.
Of course, we need to put the CUSTOMER heading in.
For some reason, it doesn't put that heading in, and then we have lots of data that show up as blank.
I would like to have 0s in there.
Great trick.
I'm going to choose all of the cells, the cells that are blank, the cells that have numbers, and then use GO TO SPECIAL.
Now, you can always get to this using CONTROL+G and then clicking SPECIAL.
Now, in Excel 2007, over here on the right hand side, FIND & SELECT, GO TO SPECIAL, and I'm going to say that I want to SELECT just the BLANKS within that selection.
So, now I've selected one of the blank cells, type 0, CONTROL+ENTER, and I've filled in all of the blanks with a 0.
So, using the consolidate feature, this is not high-tech, it's been around forever.
I think it's very unused, something we don't use a lot.
The data consolidate feature certainly solves this problem and does it very quickly.
Well, I want to thank you for stopping by.
We'll see next time for another netcast from MrExcel.
This week, let me talk about how to match up data from 2 different worksheets.
They have one field in common, for example, CUSTOMER.
CUSTOMERS and FORECAST here.
CUSTOMERS, ORDER, COST, and GROSS PROFIT.
They're not the same list of customers on both sheets.
So, yet another way to solve this is a very, very old feature, a feature that's been around since…well, for a long time anyway.
I know it was back in Excel ’93 which wasn't called Excel ’93.
The one before Excel ’95 had this feature.
I remember using it back then.
It's called the CONSOLIDATE feature.
It’s on the DATA tab.
So, we go to DATA, CONSOLIDATE.
You actually want to start in a blank section of the spreadsheet.
This is where output report’s going to go, so come here to CONSOLIDATE on the DATA menu, and we're going to build references to both sets of data.
So, the first reference here is on the forecast sheet.
We’ll click ADD, and then the second reference, I’ll click on the ORDERS tab.
Make sure that it has the same left column.
So in both cases, CUSTOMER was the left column.
I want to USE LABELS IN the TOP ROW and LEFT COLUMN, and click OK, and you see that, very quickly, without using the multiple consolidation range in the pivot table, we get CUSTOMERS along the left-hand side.
It's a superset of all customers that appear in either one list or the other, and then, very nicely here, they don't alphabetize.
It shows up in the exact same order that we added things -- so, FORECAST first and then ORDER and then COST and then GROSS PROFIT.
Now, one thing in the pivot table, we had a couple of annoyances, a couple of annoyances here.
Of course, we need to put the CUSTOMER heading in.
For some reason, it doesn't put that heading in, and then we have lots of data that show up as blank.
I would like to have 0s in there.
Great trick.
I'm going to choose all of the cells, the cells that are blank, the cells that have numbers, and then use GO TO SPECIAL.
Now, you can always get to this using CONTROL+G and then clicking SPECIAL.
Now, in Excel 2007, over here on the right hand side, FIND & SELECT, GO TO SPECIAL, and I'm going to say that I want to SELECT just the BLANKS within that selection.
So, now I've selected one of the blank cells, type 0, CONTROL+ENTER, and I've filled in all of the blanks with a 0.
So, using the consolidate feature, this is not high-tech, it's been around forever.
I think it's very unused, something we don't use a lot.
The data consolidate feature certainly solves this problem and does it very quickly.
Well, I want to thank you for stopping by.
We'll see next time for another netcast from MrExcel.
