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

Video Transcript

Learn Excel from MrExcel Podcast, Episode 2188: Summarize with Remove Duplicates.

Well, hey, in yesterday's Episode, I showed how to summarize this data down to one row per Customer, using the Subtotal command. I was doing that in a seminar in Dallas, and Adam-- Adam from Road 2-- said, "Hey, wait, why don't you just use Remove Duplicates?" And yeah, Remove Duplicates will work.

So we start here-- we don't have to sort the data to start here in D1-- Ctrl+Shift+Down Arrow, keep your hand on Ctrl+Shift, press Right Arrow, Ctrl+C to Copy. We'll move over here to a blank cell and Ctrl+V to paste, and then Remove Duplicates-- right there-- we're going to choose Unselect All, choose Customer, click OK. What we get are 16 unique Customers. Those numbers over there are meaningless-- we're going to get rid of those numbers. Click here, Ctrl+Shift+Down, Ctrl+Shift+Right, and then =SUMIFS. I guess if I was really going to try to do this in the fewest number of characters, SUMIF would be one character or less, wouldn't it?

But, alright, so we're going to specify the Sum_Range. So I'm going to press the left arrow-- 1, 2, 3, 4, 5-- 6 times; Ctrl+Shift+Down Arrow; press F4 1, 2 times to freeze the rows; and then criteria_range, we'll press the left arrow-- 1, 2, 3, 4, 5, 6-- 7 times; Ctrl+Shift+Down Arrow, press F4 once; Comma, and what do we want that criteria_range to be equal to, we want it to be equal to the Customer. And J2, I would need to freeze just the J, so that's press F4-- 1, 2-- 3 times, closing parenthesis, Ctrl+Enter, and there you are.

Less steps, certainly less steps, although entering the formula requires many more keystrokes. So, comparing yesterday to today, not sure. I'm sure Adam can do his method faster; I can do my method faster.

Both methods discussed in this new book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time.

All day this week-- every day this week-- we're looking at 5 different ways to create that Summary Table. Yesterday, Subtotals; today, Remove Duplicates; tomorrow, Advanced Filter; then Consolidate; and Pivot Tables. How to summarize with Remove Duplicates? Copy the data to a new location; Data; Remove Duplicates; Unselect All; choose Customer; OK; OK to close the dialog-- the results message box; click on the first number; Ctrl+Shift+Down, Ctrl+Shift+Right; Enter a SUMIFS formula; when you finish that with the closing parenthesis, press Ctrl+Enter.

Well, hey, I want to thank Adam for that cool trick, and I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.

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
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.