Find Total Sales by Customer by Combining Duplicates
October 25, 2022 - by Bill Jelen

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.

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.

Excel will combine all identical account numbers together.

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. Copy the
VLOOKUP
function down by double-clicking the fill handle. Change theVLOOKUP
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.

This article is an excerpt from Power Excel With MrExcel
Title photo by Glenn Carstens-Peters on Unsplash