- Excel Book Excerpt
Excel Use a Pivot Table to Compare Two Lists
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Problem: I have two lists of data. One is from a forecasting system. One is from our order entry system. I want to compare both list. Although both lists happen to have twenty customers, they are not the same twenty customers.
Figure 929 Compare these two lists.
There is a solution in Part II of this book, “Combine Two Lists Using VLOOKUP” on page 219. That method requires adding two columns of MATCH or VLOOKUP formulas. The pivot table method is far easier.
Strategy: You need to copy the two lists into a single list, with a third column to indicate whether the forecast is from this week or last week. Then you create a pivot table, and the new, deleted, and changed forecasts will be readily apparent. Follow these steps:
1. Add the heading Source in C1. Select C2:C21, type Forecast and press Ctrl+Enter to fill column C with the word Forecast.
2. Change the heading in B1 to be Amount.
3. Cut D2:E21 and paste just below the first list. Type Orders next to all of the List 2 records.
Figure 930 Add a Source column combining the lists.
4. Create a pivot table. Put Customer in the Row Labels, Source in Column Labels, and Amount in the Values area.
5. Select Design, Grand Totals, On for Columns Only.
As shown here, you will have a comparison of the two lists.
Figure 931 Excel merges the lists.
In this view, you can spot many interesting facts. It looks like the IBM misspelling in row 20 is causing problems. That forecast is most likely associated with the order in row 19. I would also be concerned with the Exxon forecast and order in row 13. Did the forecast accidentally type an extra zero when submitting his forecast?
For more resources for Microsoft Excel: