All right, welcome back to the Mr. Excel net cast. I’m Bill Jelen. Getting the tip this week from our book called Correlate Data Analysis; it was my first book about Excel.
I’m working on Jim’s question, how we match up data on two different worksheets that are tied together by common key, and some of the items are one worksheet and not the other?
Yesterday I used the two look at 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 lists 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 its called source, and for these records to the source is going to be FC for forecast.
Now I'm going to go over the other worksheet again, that's control page down, 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 orders. Double click the fill handle and copy that down. 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 put this right directly to the right of the data so we can see it, so I’ll go to E2 click okay.
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 view look up or whatsoever. We ended with a nice table. The table has all the customers, the customers in one list or the other list. As well as the column that you been forecast shows me the orders. I really don't need to grand total here, so let's right click, go to pivot table options and turn off grand total for the rows. We don’t need the grand total to the end of each row. And we now have a list showing forecast which is orders.
The blanks again drive me crazy, I should have done this at the same time right click pivot table options and for and for empty cells show zero, I’ll click okay and that fills those blanks in with zero. It is much easier in my opinion to use a pivot table basically I bring those two lists together. You can imagine if you had a third list you will just add the third list below with the different source you’ll then have three columns.
A great way at a glance to see the forecasts that are not coming yet the orders that were un-forecasted when then the items that were both forecasted and ordered. By the way, if these lists are coming from different systems I should have mention 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.89 million forecast, and then 1.8 million orders are probably the exact same thing just spelled differently, so we want to go fix that data in the other line. Data said refresh pivot table and we’re good to go.
So we have another way to solve the problem with matching up data from two different worksheet.
Thanks for stopping by we’ll see you next time for another net cast from Mr. Excel.
Transcription by:
Scribe4you Transcription Services