Excel offers a great tool for consolidating data.
1) To use the tool, move the cell pointer to a blank area of the worksheet. You will need a blank area with several rows and a few columns. In Fig. 792, G1 would be appropriate. Select cell G1. From the menu, select Data – Consolidate.
Notice that both boxes for Use labels in are checked. This means that Excel relies on the headings to be the same and that the customer field is in the left column of each range.
2) Put the cursor in the Reference field. Click the Collapse button at the right end of the Reference field. With the mouse, select the first range: A1:B23. Choose the Collapse button again to return to the Consolidate Dialog. See Fig. 793.
There are times when you will want to consolidate just a single range of data. This would be effective if you needed to combine duplicate customers from one list. However, in this example you need to combine two lists.
3) As shown in Fig. 794, choose the Add button in order to move the first reference from the reference field to the All References box.
4) Once the first reference has been added to the All References box, choose the Collapse button again to specify the second reference, as shown in Fig. 795.
5) Use the mouse to select D1:E23. Choose the Collapse button to return to the Consolidate dialog. As shown in Fig. 796, choose the Add button to add the reference to the All References list.
6) Choose OK. In a few seconds, Excel will return a brand new list that extends down and to the right from cell G1, as shown in Fig. 797. The list will contain one instance of each customer along with the total revenue from the customer.
The new list is not in any sequence. You can see it sort of starts out in the sequence of the first list, but then randomly inserts customers from the second list. You will probably want to sort the list alphabetically or by revenue. However, Excel always fails to fill in the label in the upper left corner of the consolidation. If you want to sort the result, you need to type the word Customer in cell G1.
The function box in the Data Consolidation dialog offers many functions other than SUM, as shown in Fig. 798. For instance, if you wanted to find the largest purchase by each customer, you could use the MAX function.
The results of the consolidation shown in Fig. 797 are all static values. If you change an item in the original list, the consolidation will not automatically update. This is good, as it allows you to delete the original two lists and keep just the new list.
Using Consolidate is one of several methods for combining lists of data.
Data – Consolidate