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.
- Compare these two lists.
The pivot table method is far easier than using two columns of MATCH or VLOOKUP.
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:
- Add the heading Source in C1. Select C2:C21, type Forecast and press Ctrl+Enter to fill column C with the word Forecast.
- Change the heading in B1 to be Amount.
- Cut D2:E21 and paste just below the first list. Type Orders next to all of the List 2 records.
- Add a Source column combining the lists.
- Create a pivot table. Put Customer in the Row Labels, Source in Column Labels, and Amount in the Values area.
- Select Design, Grand Totals, On for Columns Only.
As shown here, you will have a comparison of the two lists.
- 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?