- Excel Book Excerpt
Excel Find Total Sales by Customer by Combining Duplicates
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.
Find Total Sales by Customer
by Combining Duplicates
Problem: I have an invoice register for the month. The report shows account, customer, invoice, sales, cost, and profit for each invoice. I want to combine customers in order to produce a report of sales by customer.
Figure 713 Consolidate the data to one row per customer.
Strategy: It is possible to consolidate a single list by using the labels in the left column. This will produce a report with one line per customer and totals of each numeric field. You can use data consolidation to solve this task:
1. Select a blank section of the worksheet. Select Data, Consolidate. In the Reference field, select the complete range of your data, including the headings. Ensure that the Left Column option is checked and that the Create Links to Source Data check box is unchecked. Click OK.
Figure 714 Specify a single range to consolidate.
Excel will combine all identical account numbers together.
Figure 715 One row per unique account number.
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 716 Use a VLOOKUP to fill text fields.
4. Copy the VLOOKUP function down by double-clicking the fill handle. Change the VLOOKUP formula 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 customer.
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.
Figure 717 Duplicates removed and summarized.
For more resources for Microsoft Excel: