Match two worksheets using a pivot table. We continue on this week with Jim's question about matching two worksheets with a common column. Rather than using VLOOKUP, you can use a pivot table to do the comparison. Episode 927 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. Welcome back to the MrExcel netcast. I'm Bill Jelen.
Again, the tips this week are from a book called Guerilla Data Analysis, one of my first books about Excel.
We’re working on Jim's question, how do we match up data on two different worksheets that are tied together by a common key, and some of the items are in one worksheet and not the other?
Yesterday, I used the two lookup method.
Today, I'm going to take a look at my favorite method which, of course, if you know me, my favorite method is always to use a pivot table.
So, we have our two lists.
We have our FORECAST list here on the next worksheet.
We have our ORDER list.
I'm going to start in the FORECAST list.
This time, I'm going to add a new column here.
It's called SOURCE, SOURCE, and, for all of these records, the source is going to be FC for forecast.
Now, I'm going to go over to the other worksheet -- again, that's CONTROL+PAGEDOWN -- and I'm going to grab all of these records, CONTROL+C, taking the ORDER amount, and I'm pasting it right below the FORECAST, and, here, the SOURCE is going to be ORDER, double click the fill handle to copy that down, and I'm also going to change this heading from FORECAST to REVENUE.
Alright.
Now, we're almost done.
Here's what we're going to do.
We're going to say INSERT, PIVOT TABLE.
I'm actually going to put this right to the…directly to the right of the existing data so we can see it.
So, I'll go to E2, click OK, alright, and, in my pivot table, I want to have CUSTOMER down the left hand side, I want to have REVENUE in the heart of the pivot table, and then -- here's the thing that makes it all work -- take the SOURCE field and move it to the COLUMN LABELS.
Now, check this out.
Without doing any VLOOKUPs whatsoever, we ended up with a nice table.
The table has all of the customers, the customers, in one list or the other list, as well as a column that shows me the FORECAST, shows me the ORDERS.
I really don't need the GRAND TOTAL here, so let's right-click, go to the PIVOT TABLE OPTIONS, and turn off GRAND TOTAL for the rows.
We don't need the GRAND TOTAL at the end of each row, and we now have a list showing FORECAST versus ORDERS.
The blanks, again, drive me crazy.
I should have done this at the same time.
Right-click, PIVOT TABLE OPTIONS, and FOR EMPTY CELLS SHOW 0, click OK, and that’ll fill those blanks in with 0s.
Much easier, in my opinion, to use a pivot table.
Basically, bring those two lists together.
You can imagine, if you had a third list, you would just add the third list below with a different source.
You’d then have 3 columns.
Great way, at a glance, to see the forecasts that have not come in yet, the ORDERS that were unforecasted, and then the items that were both forecasted and ordered.
You know, by the way, if these lists are coming from different systems -- and I should have mentioned this yesterday -- you need to take a second to read through these and see if there's any misspellings.
Like, for example, General Motors and GM, this 1.89million FORECAST and the 1.8 million ORDER are probably the exact same thing just spelled differently.
So, we want to go fix that data in the underlying data set, refresh the pivot table, and we're good to go.
So, there you have it -- another way to solve the problem of matching up data from two different worksheets.
Thanks for stopping by.
We’ll see you next time for another netcast from MrExcel.
Again, the tips this week are from a book called Guerilla Data Analysis, one of my first books about Excel.
We’re working on Jim's question, how do we match up data on two different worksheets that are tied together by a common key, and some of the items are in one worksheet and not the other?
Yesterday, I used the two lookup method.
Today, I'm going to take a look at my favorite method which, of course, if you know me, my favorite method is always to use a pivot table.
So, we have our two lists.
We have our FORECAST list here on the next worksheet.
We have our ORDER list.
I'm going to start in the FORECAST list.
This time, I'm going to add a new column here.
It's called SOURCE, SOURCE, and, for all of these records, the source is going to be FC for forecast.
Now, I'm going to go over to the other worksheet -- again, that's CONTROL+PAGEDOWN -- and I'm going to grab all of these records, CONTROL+C, taking the ORDER amount, and I'm pasting it right below the FORECAST, and, here, the SOURCE is going to be ORDER, double click the fill handle to copy that down, and I'm also going to change this heading from FORECAST to REVENUE.
Alright.
Now, we're almost done.
Here's what we're going to do.
We're going to say INSERT, PIVOT TABLE.
I'm actually going to put this right to the…directly to the right of the existing data so we can see it.
So, I'll go to E2, click OK, alright, and, in my pivot table, I want to have CUSTOMER down the left hand side, I want to have REVENUE in the heart of the pivot table, and then -- here's the thing that makes it all work -- take the SOURCE field and move it to the COLUMN LABELS.
Now, check this out.
Without doing any VLOOKUPs whatsoever, we ended up with a nice table.
The table has all of the customers, the customers, in one list or the other list, as well as a column that shows me the FORECAST, shows me the ORDERS.
I really don't need the GRAND TOTAL here, so let's right-click, go to the PIVOT TABLE OPTIONS, and turn off GRAND TOTAL for the rows.
We don't need the GRAND TOTAL at the end of each row, and we now have a list showing FORECAST versus ORDERS.
The blanks, again, drive me crazy.
I should have done this at the same time.
Right-click, PIVOT TABLE OPTIONS, and FOR EMPTY CELLS SHOW 0, click OK, and that’ll fill those blanks in with 0s.
Much easier, in my opinion, to use a pivot table.
Basically, bring those two lists together.
You can imagine, if you had a third list, you would just add the third list below with a different source.
You’d then have 3 columns.
Great way, at a glance, to see the forecasts that have not come in yet, the ORDERS that were unforecasted, and then the items that were both forecasted and ordered.
You know, by the way, if these lists are coming from different systems -- and I should have mentioned this yesterday -- you need to take a second to read through these and see if there's any misspellings.
Like, for example, General Motors and GM, this 1.89million FORECAST and the 1.8 million ORDER are probably the exact same thing just spelled differently.
So, we want to go fix that data in the underlying data set, refresh the pivot table, and we're good to go.
So, there you have it -- another way to solve the problem of matching up data from two different worksheets.
Thanks for stopping by.
We’ll see you next time for another netcast from MrExcel.
