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 932 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:

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 933 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. Right-click the Grand Total heading and choose Remove Grand Total. As shown here, you will have a comparison of the two lists.

5.Change the pivot table to have Dates in the Row Labels and Line in the Columns. Move Defect from the Row Labels to the Report Filter.

6.Choose Fit & Finish – Roof from the Report Filter dropdown in B1. This was the defect that occurred most often.

Results: As shown below, the defect was happening a few times each day until the 28th of the month. On the 28th, line B began having problems. On the 29th, the problem began appearing in lines A, C, and D. By the 30th, all four lines were having massive problems. This doesn’t look like a problem with an isolated employee, so you should probably see if a new batch of material started being used on the 28th.

Figure 937 Even without any numeric data, you can discover trends.

FIX MISSPELLED CUSTOMER NAMES

Problem: I collect data from sales reps. There must be a half dozen ways that they enter General Electric.

Figure 938 Conform all ways to spell these customer names.

Strategy: You can use a pivot table to help solve this problem. Follow these steps:

1. Build a pivot table with Customer in the Row Labels and in the Values area. This will show you each customer and the number of times that this spelling is used.

2. Copy the entire pivot table.

3. Paste Values to convert the pivot table to regular data.

4. Insert a new column between A & B. Copy the customers from A to B with a heading of Good Cus- tomer.

5. Manually scan through the report, looking for different ways to spell the same customer. When you find a duplicate, you can look at column C to see which is more prevalent. For the wrong spelling, copy the correct spelling to column B. The advantage: you only have to change the few customers that have duplicates.

Figure 939 Find duplicates and fix one in column B.

6. Go back to your original data. Add a new column called Fixed Customer. Do a VLOOKUP into the pivot table to get the correct customer.

Figure 940 Do a VLOOKUP.

7. Copy the new column. Paste Values.

Results: Excel will display the PivotTable Field List dialog, with all the fields from your table or query.

Alternate Strategy: The Power Query add-in for Excel 2010/2013 can import data from Access. See "Load and Clean Data with Power Query" on page 368.

WHAT HAPPENED TO MULTIPLE CONSOLIDATION RANGES IN PIVOT TABLES?

Problem: I read your book Pivot Table Data Crunching, which describes an awesome trick for spinning poorly formatted data into transactional data for pivot tables. The trick requires you to choose Multiple

Consolidation Ranges from Step 1 of the PivotTable and PivotChart Wizard. However, Microsoft seems to have eliminated the wizard in Excel 2007, so now how can I select Multiple Consolidation Ranges?

Strategy: Although the PivotTable and PivotChart Wizard has been removed from the ribbon, you can still get to the old wizard:

1. Type Alt+D followed by P.

2. The PivotTable and PivotChart Wizard will appear, complete with new artwork.

Additional Details: Using multiple consolidation ranges can help when your data is not properly format- ted for pivot tables. Below, the data has been summarized with months going across the columns. Each year is on a different worksheet. All of the worksheets have products along column A, but the list of products differs from year to year.