Excel Data Cleanup: Remove Duplicates - Episode 2188

Показать описание

Microsoft Excel Tutorial: Summarizing Data in Excel Using Remove Duplicates

Welcome to another episode of the MrExcel Podcast, where we bring you the best tips and tricks for mastering Excel. In today's episode, we will be discussing how to summarize data using the Remove Duplicates function. This tip was suggested by one of our viewers, Adam from Road 2, during a seminar in Dallas. So let's dive in and see how it works.

In yesterday's episode, we used the Subtotal command to summarize data down to one row per customer. However, Adam pointed out that we can achieve the same result using the Remove Duplicates function. So let's start by selecting the data we want to summarize, and then copying it to a new location. Next, we will go to the Data tab and click on Remove Duplicates. In the dialog box, we will choose "Unselect All" and then select the column that contains our customer names. Click OK, and voila! We now have a list of unique customers.

But wait, what about those numbers next to the customer names? Don't worry, we can easily get rid of them by selecting the numbers, pressing Ctrl+Shift+Down to select the entire column, and then pressing Ctrl+Shift+Right to select the numbers. Next, we will enter a SUMIFS formula to calculate the total for each customer. This may seem like a lot of steps, but trust me, it's worth it. Plus, this method is discussed in detail in my latest book, MrExcel LIVe: The 54 Greatest Excel Tips of All Time.

Now, let's compare this method to the one we used in yesterday's episode. While this method may require more keystrokes, it is definitely faster and more efficient. But don't just take my word for it, try it out for yourself and see which method works best for you. And if you're looking for more ways to create a summary table, be sure to tune in for the rest of this week as we explore 5 different methods, including Subtotals, Advanced Filter, Consolidate, and Pivot Tables.

So there you have it, a quick and easy way to summarize data using the Remove Duplicates function. I want to give a special thanks to Adam for suggesting this tip and to all of you for tuning in. Don't forget to check out our other episodes for more Excel tips and tricks. And if you want to learn even more, be sure to grab a copy of my book, MrExcel LIVe: The 54 Greatest Excel Tips of All Time. Thanks for watching and we'll see you next time for another netcast from MrExcel.

#excel
#microsoft
#microsoftexcel
#exceltutorial
#exceltips
#exceltricks
#evergreen
#excelremoveduplicates

This video answers these common search terms:
remove duplicated from a column in excel
how remove duplicate rows in excel
how to remove duplicates in column excel
how to remove duplicates from multiple columns in excel
how to remove duplicate values in excel in column
how to remove duplicate cells excel
how to remove duplicate values in excel
how to remove duplicates excel
how to remove duplicates in an excel coluln
how to remove duplicates in an excel sheet
how to remove duplicates row in excel
how to remove excel duplicate rows
how to remove of duplicates in excel
how does remove duplicates work excel
where is remove duplicated on excel
how does the remove duplicates work excel
which duplicate record does excel remove?
how to find duplicates for two column in excel
how to find duplicate entries excel
how to find duplicates in excel
how to delete duplicate lines in excel
how to eliminate duplicate lines in excel
how can i delete duplicate entries in excel
how to delete duplicate values on excel
how to delete duplicates in a column excel
how to duplicate excell easy
how to erase the duplicates in excel
how to extract duplicates in excel
how to take out duplicates excel
removing duplictes on excel
when removing duplicates on excel
will "delete duplicates" delete entire rows in excel

Table of Contents:
(00:00) Using Remove Duplicates to summarize data
(00:12) Suggestion to use Remove Duplicates from seminar attendee
(00:22) Using Remove Duplicates to get unique customers
(00:43) Removing meaningless numbers and using SUMIFS formula
(01:00) Comparison of methods
(02:01) 5 different ways to create a summary table
(02:13) Clicking Like really helps the algorithm

This is the first of a five-part series on Summarizing Data
This week, I will cover Subtotals, Remove Duplicates, Advanced Filter, Consolidate, & Pivot Tables
How to summarize with Remove Duplicates
1. Copy the data to a new location
2. Data, Remove Duplicates. Click UnSelect All. Click Customer. OK. OK.
3. Click in the first number. Ctrl+Shift+Down+Right. Enter a SUMIFS and Ctrl+Enter.