MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Summarize Data with Remove Duplicates

April 10, 2018 - by Bill Jelen

Summarize Data with Remove Duplicates

Your manager needs a summary of total revenue, cost, and profit for each customer in a large data set. Adam from one of the large oil companies in Dallas provides a faster way using Remove Duplicates.

The goal is to summarize this 563-row data set so you have one row per customer.

Create a summary with 1 row per customer
Create a summary with 1 row per customer

This week will feature five different ways to solve the problem.

Watch Video

Here are the steps:

  • Select the Customer heading in D1. Ctrl + Shift + Down Arrow Right Arrow to select the data in D:H
  • Ctrl + C to copy. Select J1. Ctrl + V to paste. Data, Remove Duplicates. Click Unselect All. Select Customer. Click OK.
Get one row per customer.
Get one row per customer.
  • Click OK to dismiss the message box showing how many items remain.
  • Select K2. Ctrl + Shift + Down Arrow and Ctrl + Shift + Right Arrow to select all of the numbers.
  • Enter a formula of =SUMIFS(E$2:E$564,$D$2:$D$564,$J2). Press Ctrl + Enter to fill the selection with a similar formula

After 47 clicks, you have this result:

A summary by customer
A summary by customer

Entering the formula requires a lot of keystrokes. I trust that Adam can probably do these steps as fast as I can do the Subtotals from yesterday. Stop back tomorrow for another method.

This week is Summarizing Data week. Each day, we will look at five different ways to solve a problem.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"A spreadsheet is only as strong as it’s weakest formula"


Title Photo: Matthew Henry / Unsplash

Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.