Excel 2007 offers a fantastic new way to remove duplicates from your data set. Episode 651 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Welcome back to the Mr. Excel netcast.
I'm Bill Jelen.
Taking a look again at some of the new features in Excel 2007.
They added a great new feature called remove duplicates.
Now, before to get a unique list of items.
We would have had to go through either data filter advanced filter choosing unique items only or creating a pivot table.
Well, now it's pretty much gotten down to a couple of clicks.
We select one cell in our data set choose Remove Duplicates.
It says you know, what columns do you want to remove the duplicates from and maybe, I say I only want to see one occurrence of every customer, click OK and that fast it removed 536 duplicates and 27 unique values remain .
Now, let me undo that control+Z, and we'll go back to the original and say that we have to do something more complex.
For example, every occurrence of customer and product so remove duplicates choose unselect all and then say customer and product. click OK And now we have 81 unique values remain, those 27 customers and the products that they bought.
So, it now becomes very very simple to create unique lists of any combination things.
Definitely, something that was a little bit harder to do in excel 2003.
because you usually had to go to the advanced filter and frankly.
I'm doing my seminars I find hardly anyone will ever venture into the advanced filter it's pretty hard to use.
So, Excel 2007 here on the Data tab now have the remove duplicates button great improvement.
Thanks for stopping by.
We'll see you next time for another netcast for MrExcel.
I'm Bill Jelen.
Taking a look again at some of the new features in Excel 2007.
They added a great new feature called remove duplicates.
Now, before to get a unique list of items.
We would have had to go through either data filter advanced filter choosing unique items only or creating a pivot table.
Well, now it's pretty much gotten down to a couple of clicks.
We select one cell in our data set choose Remove Duplicates.
It says you know, what columns do you want to remove the duplicates from and maybe, I say I only want to see one occurrence of every customer, click OK and that fast it removed 536 duplicates and 27 unique values remain .
Now, let me undo that control+Z, and we'll go back to the original and say that we have to do something more complex.
For example, every occurrence of customer and product so remove duplicates choose unselect all and then say customer and product. click OK And now we have 81 unique values remain, those 27 customers and the products that they bought.
So, it now becomes very very simple to create unique lists of any combination things.
Definitely, something that was a little bit harder to do in excel 2003.
because you usually had to go to the advanced filter and frankly.
I'm doing my seminars I find hardly anyone will ever venture into the advanced filter it's pretty hard to use.
So, Excel 2007 here on the Data tab now have the remove duplicates button great improvement.
Thanks for stopping by.
We'll see you next time for another netcast for MrExcel.
