Gotcha: Note that Excel added up the invoice numbers in column J. This makes no sense. 2. Delete column J.

3. The Consolidate command is not smart enough to take the first or last instance of text fields, so fill in the customer name, using a VLOOKUP function.

Figure 710 Use a VLOOKUP to fill text fields.

4. Copy the VLOOKUP function down by double-clicking the fill handle. Change the VLOOKUP for- mula to values by copying I2:I16 and then using Home, Paste dropdown, Paste Values.

5. Excel does not fill in the label in the upper-left corner of the table, so enter Acct in H1. The resulting data set is in the same sequence as the customers in the original list.

6. Choose a single cell in column I and click the AZ sort button to produce an alphabetical list by cus- tomer.

7. Because the column widths are not automatically adjusted as the result of a consolidation, use

Home, Format, AutoFit Column Width to adjust the column widths.

3

Figure 711 Duplicates removed and summarized.

REMOVE DUPLICATES

Problem: I have a data set in which I would like to find every unique combination of customer and prod- uct.

Figure 712 Find unique combinations of customer and product.

Strategy: Although there are several ways to find unique values (advanced filters, pivot tables, Microsoft Query, COUNTIF), Microsoft added a new feature to Excel 2007 called Remove Duplicates.

Remove Duplicates is a powerful feature—sometimes too powerful because it very quickly and destructively removes the duplicated rows.

To use the Remove Duplicates command, follow these steps:

1.Make a copy of your data. Copy it to a new range, a new worksheet, or a new workbook.

2.Select one cell in your data set.

3.Select Data, Remove Duplicates. Excel will display the Remove Duplicates dialog.

4.Click Unselect All. Select Product and Customer.

Figure 713 Choose which columns should be analyzed.

5. Click OK. Excel will confirm how many duplicates were found and removed.

Figure 714 Duplicates removed.

Results: Excel will delete hundreds of rows of data! If you didn’t make a copy in step 1 and you need that data, press Ctrl+Z to undo.

PREVIEW REMOVE DUPLICATES WITHOUT REMOVING THEM

Problem: I want to preview which rows will be deleted before deleting them with Remove Duplicates.

Strategy: Use the Formula version of conditional formatting to highlight the cells that will be deleted.

Continuing the previous example, say that you want to remove all duplicates of Product+Customer.

Follow these steps: 1. Select A2:H564

2. Home, Conditional Formatting, New Rule, Use a Formula to Determine Which Cells to Format

3. In the Formula box, type =COUNTIFS($B$1:$B1,$B2,$D$1:$D1,$D2)>0. Note the four references with only a single dollar sign. Those missing dollar signs create an expanding range.

4. Click the Format... button.

5. Choose a Fill color. Click OK once for each open dialog box.

Figure 715 The key to this formula are the eight dollar signs.

Excel will highlight which rows would be deleted by Remove Duplicates.

3

Figure 716 Anything highlighted is a duplicate of Product/Customer.