Summarize Data with Remove Duplicates
April 10, 2018 - by Bill Jelen
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.
This week will feature five different ways to solve the problem.
- Monday: Summarize Data with Subtotals
- Today: Summarize Data with Remove Duplicates
- Wednesday: Summarize Data with Advanced Filter
- Thursday: Summarize Data with Consolidate
- Friday: Summarize Data with Pivot Tables
- Saturday: Summary of Summarize Data Week
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.
- 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:
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