MrExcel.com - Your One Stop for Excel Tips & Solutions

Find Total Sales by Customer by Combining Duplicates

October 25, 2022 - by

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.

Using Consolidate on a single data set that has duplicate Account numbers in column A. This data set has Account in A, Customer in B, Invoice number in C, Sales in D, COGS in E, and Profit in F.
Figure 770. 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.

The Consolidate dialog box with one reference in the All References box.
Figure 771. Specify a single range to consolidate.

Excel will combine all identical account numbers together.

The results of the Consolidate command. Each Account number appears once in A. The column for Customer has a heading but is empty because Consolidate only works with numbers. The column for Invoice Number is silly; there is no value in adding up the invoice numbers. The columns for Sales, COGS, and Profit are all useful: Excel has totaled the various values into a single row for each account.
Figure 772. 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.

Use a VLOOKUP formula to fill in the customer name. Delete the Invoice Number column in the results of Consolidate.
Figure 773. 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.

The final results of the Consolidate have one line per customer with totals for that customer.
Figure 774. Duplicates removed and summarized.

This article is an excerpt from Power Excel With MrExcel

Title photo by Glenn Carstens-Peters on Unsplash