Excel: 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.

1. 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.

   

2. Specify a single range to consolidate.

   Excel will combine all identical account numbers together.

   

3. 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.

   

4. 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.

   

5. Duplicates removed and summarized.