Another solution to matching data on two worksheets is the Multiple Consolidation Ranges option in the pivot table. Unfortunately, it is much harder to find in Excel 2007. Episode 928 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:
Alright. Hey. Welcome back. It's Where Is It Wednesday. Thanks to George Wood for that theme. I'm not sure I have to thank George Wood because George Wood writes the theme for every day now, but, still, thanks to George Wood.
We've been talking this week about how to match up data from two different worksheets that have one key in common. CUSTOMER and FORECAST. I also have CUSTOMER, ORDER. I added some new columns here, COST and GROSS PROFIT.
I like to marry all of these up. On Monday, we talked about the double lookup method.
Yesterday, we talked about using a pivot table, and, today, I want to talk about a different way to use the pivot table, but this feature, at least apparently, has been removed from Excel 2007. Let me show you what I'm talking about.
Back here in Excel 2003, when we'd go to DATA, PIVOT TABLE, now, most people would just click FINISH in the wizard, but there were other choices here, including something called MULTIPLE CONSOLIDATION RANGES. Now, this is obscure.
I probably get to use it about twice a year but it really helps to solve this problem, and the problem that we have is it seems that it's gone in Excel 2007. I mean, take a look.
Go to INSERT, PIVOT TABLE. There's nothing there for multiple consolidation ranges. What the heck? I mean, it was there before. Let's see if it's in the dropdown. PIVOT TABLE.
PIVOT CHART. No, of course not. So, how do we get to any command that used to be in the old Excel?
Well, think about it. The old accelerator key started to work. So, here in Excel 2003, the D was underlined. ALT+D would open the DATA menu and then ALT+P would do PIVOT TABLE AND PIVOT CHART REPORT. Pretty wild. If we come here to Excel 2007 and do ALT+D P, it brings back the old wizard, you know. It’s like the wizard had been banished. It's not really banished. It was just removed from the ribbon. So, ALT+D P gets us back to the old wizard, and, check this out, some artist at Microsoft actually redesigned the dialog box, got to do some new artwork there, and then it was removed from the ribbon. I’d hate to be that person, but MULTIPLE CONSOLIDATION RANGES.
Okay. So, let me show you how we can use MULTIPLE CONSOLIDATION RANGES to solve this problem.
We'll choose MULTIPLE CONSOLIDATION RANGES.
We’re in step 1 of 3. Click NEXT, and now we're in step 2a of 3. It’s like they're saying we don't know how many steps there are going to be. Say I'm going to create the page fields, click NEXT, and now we're going to specify our ranges one at a time. So, the first range here on the ORDERS worksheet, A1 to D21, press ADD, and now I want to add a second range, so I'll click on the FORECAST sheet and specify my data there, and click ADD. I don't want any page fields at all so I click, actually, at this point, I can click FINISH, and I get a brand new worksheet. All of the customers that are on either sheet -- either SHEET1 or SHEET2 -- show up along the left-hand side. All of the columns that are on either sheet show up across the top.
Now, they put these in alphabetical order, so COST shows first. That's not how I want this at all. This is a pretty cool feature in pivot tables. I can take that FORECAST heading, and go to the edge of it, and drag it over to column B, and I can rearrange it.
I want ORDERS to show up next. Again, I can take that field and drag it over to column C. COST and GROSS PROFIT, that's okay.
Just like I did yesterday, we would go into pivot table options, fill the empty cells with 0, and get rid of the grand total for rows, and we've solved this problem. The advantage here is we didn't have to copy the data onto the same worksheet, you know. Remember, yesterday, I had to take the ORDERS, and copy them below the FORECAST, and add that to a source field.
Not necessary to do at all here. Using the MULTIPLE CONSOLIDATION RANGES feature of the pivot table allowed us to solve this problem.
Unfortunately, it's a cool feature that we just can't find anymore. It's under ALT+D and then P. Alternatively, there is, by the way, a icon that you can add. If you go in to customize your quick access toolbar, that will bring back the pivot table wizard, although, easy enough just to do ALT+D for data, P for pivot table, and get back to that nicely redesigned dialog box that they removed from the ribbon.
Go figure.
I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel.
We've been talking this week about how to match up data from two different worksheets that have one key in common. CUSTOMER and FORECAST. I also have CUSTOMER, ORDER. I added some new columns here, COST and GROSS PROFIT.
I like to marry all of these up. On Monday, we talked about the double lookup method.
Yesterday, we talked about using a pivot table, and, today, I want to talk about a different way to use the pivot table, but this feature, at least apparently, has been removed from Excel 2007. Let me show you what I'm talking about.
Back here in Excel 2003, when we'd go to DATA, PIVOT TABLE, now, most people would just click FINISH in the wizard, but there were other choices here, including something called MULTIPLE CONSOLIDATION RANGES. Now, this is obscure.
I probably get to use it about twice a year but it really helps to solve this problem, and the problem that we have is it seems that it's gone in Excel 2007. I mean, take a look.
Go to INSERT, PIVOT TABLE. There's nothing there for multiple consolidation ranges. What the heck? I mean, it was there before. Let's see if it's in the dropdown. PIVOT TABLE.
PIVOT CHART. No, of course not. So, how do we get to any command that used to be in the old Excel?
Well, think about it. The old accelerator key started to work. So, here in Excel 2003, the D was underlined. ALT+D would open the DATA menu and then ALT+P would do PIVOT TABLE AND PIVOT CHART REPORT. Pretty wild. If we come here to Excel 2007 and do ALT+D P, it brings back the old wizard, you know. It’s like the wizard had been banished. It's not really banished. It was just removed from the ribbon. So, ALT+D P gets us back to the old wizard, and, check this out, some artist at Microsoft actually redesigned the dialog box, got to do some new artwork there, and then it was removed from the ribbon. I’d hate to be that person, but MULTIPLE CONSOLIDATION RANGES.
Okay. So, let me show you how we can use MULTIPLE CONSOLIDATION RANGES to solve this problem.
We'll choose MULTIPLE CONSOLIDATION RANGES.
We’re in step 1 of 3. Click NEXT, and now we're in step 2a of 3. It’s like they're saying we don't know how many steps there are going to be. Say I'm going to create the page fields, click NEXT, and now we're going to specify our ranges one at a time. So, the first range here on the ORDERS worksheet, A1 to D21, press ADD, and now I want to add a second range, so I'll click on the FORECAST sheet and specify my data there, and click ADD. I don't want any page fields at all so I click, actually, at this point, I can click FINISH, and I get a brand new worksheet. All of the customers that are on either sheet -- either SHEET1 or SHEET2 -- show up along the left-hand side. All of the columns that are on either sheet show up across the top.
Now, they put these in alphabetical order, so COST shows first. That's not how I want this at all. This is a pretty cool feature in pivot tables. I can take that FORECAST heading, and go to the edge of it, and drag it over to column B, and I can rearrange it.
I want ORDERS to show up next. Again, I can take that field and drag it over to column C. COST and GROSS PROFIT, that's okay.
Just like I did yesterday, we would go into pivot table options, fill the empty cells with 0, and get rid of the grand total for rows, and we've solved this problem. The advantage here is we didn't have to copy the data onto the same worksheet, you know. Remember, yesterday, I had to take the ORDERS, and copy them below the FORECAST, and add that to a source field.
Not necessary to do at all here. Using the MULTIPLE CONSOLIDATION RANGES feature of the pivot table allowed us to solve this problem.
Unfortunately, it's a cool feature that we just can't find anymore. It's under ALT+D and then P. Alternatively, there is, by the way, a icon that you can add. If you go in to customize your quick access toolbar, that will bring back the pivot table wizard, although, easy enough just to do ALT+D for data, P for pivot table, and get back to that nicely redesigned dialog box that they removed from the ribbon.
Go figure.
I want to thank you for stopping by. We’ll see you next time for another netcast from MrExcel.
