2 Ways to Remove Duplicates to Create List of Unique Values in Excel

The Opinion Lighthouse
32 followers
161 Video Views·Nov 8, 2022
2 Ways to Remove Duplicates to Create List of Unique Values in Excel
Welcome to Excel Campus. My name is John and in this video I'm going to explain how to remove
duplicates to create a list of unique values. So we're going to look at two different ways to do
this. The first way being the remove duplicates feature in Excel and then I'm going to explain
how to make the process even faster using a macro. So here I have a simple data set
and ultimately I want to create a list of unique values for the salesperson column,
all of these items here in column D. So we can see we have repeating values here
and I just want to make a list of unique values that I can use for a lookup table or maybe a
summary report or something like that. So Excel has a remove duplicates feature built in. It's
on the data tab of the ribbon and there's a button right here called remove duplicate.
So clicking that button will open this remove duplicates window here and typically if you
have your data formatted in an Excel table or you have the filters turned on, you'll have this
my data has headers checkbox checked and those are the header, that header row that contains
the column names and right here we have a list of the column names. So if we leave these all
checked right now and click okay, Excel is going to look for matching rows, complete matching rows
and then if it finds any of those it'll remove the duplicates. Now I don't have any of those
in this data set. So if I click okay right now, Excel is just going to tell me that no duplicate
values were found. So I'll click okay. So I'll open remove duplicates again and really we just
want to run this on one column which is the salesperson column. So I'm going to unselect all
then click the salesperson column and then click okay and now Excel will tell me that 361 duplicate
values were found and removed and then eight unique values remain. So we can click okay and
here is our list of unique values right here. So these are all the unique values in the column
for just that salesperson column. Now there's a few issues with this. First of all, this remove
duplicates runs on the existing data set. So we do lose existing data if we run it here. Fortunately
this data set's small and I can undo this and I'm going to undo this but that is one issue. And then
the other issue is I don't really need all of these other columns. What I really wanted to create
was just this salesperson column right here and probably put that on a new sheet and just have
that list on a new sheet. So I'm going to go ahead and undo that remove duplicate. So now we're back
to our data set. So typically what we have to do to create this list of unique values is first
select the salesperson column. We can do that by hovering the mouse over the top half of the
header row here in the table. Just click it twice and then we'll right click copy or control c.
We'll add a new sheet to our workbook here and we'll paste. Right click paste or control v.
So now we have the contents of our column and we want to remove duplicates. So again data tab and
then we'll go remove duplicates. Now we'll only see the one person here. I'm sorry the one column
the salesperson column right here checked and we'll hit ok. And that will again remove all those
duplicate values and then the eight unique values remain. So we can click ok and now we have this
list on a new sheet. We can auto fit that and then we can use this list to create that lookup table
or whatever we want to use it for. But now we have this list of unique values and the data on the
data sheet has not changed. It's still there and we can still use that data. So as we can see there's
quite a few steps there to copy the data to a new sheet, add the new sheet, and then remove the
duplicates. So I've written a macro that does that all in one step with the click of a button and
I've added that macro to my my macros toolbar up here. This list unique buttons will run the macro.
So I'll explain the macro in just a second but first I'll show how it works. So again we'll work
with the salesperson column here. I'm going to hit escape to clear those marching ants and now
we just have the entire salesperson column selected and I'm just going to click the list
unique button and that runs the macro to do everything we just did which is create
the new sheet, copy and paste the data, run remove duplicates, but it does that all with the click
of a button. So it's obviously very fast and it will work on any column that you select. So you
select a column in datasheet click that button or run the macro and you're good to go. So let's
take a look at this macro and see how it works. So I'm going to go to open the VB editor keyboard
shortcut alt f11. We can go to the developer tab here click the visual basic button and here is
the macro and I'll make this a file available for you to download. So you can download the file for
free and copy and paste the macro and you can put it in your personal macro workbook. I have a whole
another video series that explains how to create the personal macro workbook and use it and also
how to add buttons up here. So if we go back to excel how to add a custom toolbar here custom
ribbon with buttons that are assigned to those macros. So I have a whole video series that explains
that in more detail and I'll put the link below this video. All right so if we jump back over to
the visual basic editor here's the macro and it's fairly simple it just does all those steps we just
did manually but it does it with code. So the first thing we do in this section here is check
that a range is selected and not another object like a shape or a chart or something. So make
sure the range is selected then we set a variable equal to the selected range so this variable will
store our selected range. We then add a new worksheet with the worksheets dot add method
and we'll set this variable ws to that new sheet to reference that new sheet. We then copy that
selected range and paste it to the new sheet. So we're pasting it here to cell A1 in the new sheet.
I'm using the paste special method to paste the values and the formats but you can use any of
those options in the paste special method there if you want to just do a regular copy and paste to
everything over. Values and formats we'll just bring over the values in the cells and also the
formats like the fill color and the borders and things like that. If you just want to bring over
the values and number formats you can use this line here just delete the apostrophe in front of
it to uncomment that and use that line instead and then put apostrophes in front of these lines
up here to comment those out. So that's how you can change the paste special command there however
you want to paste to the new sheet and then we use the remove duplicates method on that used
range in the new sheet to remove the duplicates. So that's the same feature that we have in excel
we're just using code to do it instead we're using bba code and then it has some parameters
here for how many columns we just want the first column because we're only doing one single column
and then this header parameter will guess whether or not your data has headers based on if that cell
is bold or has some formatting applied to it up in the header row. So then down here I added this
optional section to also remove blank cells so another thing that happens when we do remove
duplicates is it will count a blank cell or multiple blank cells as a unique value and then
leave that blank cell in the list and typically we don't want that blank cell there so then we
take an extra step to go delete the blank cell. So in this case here this part of the code will
just delete any blank cells or actually just a single blank cell that's left over after we
remove duplicates. Then there's a line of code to auto fit the column so all the contents fit
in the column so we don't have to take that step either and then down here we just exit the cut
copy mode so we don't accidentally paste our copied range again somewhere else. So that's how
the macro works again I'll jump back to Excel we'll go over to our data sheet here and we can
run it on a column that does contain blanks so we can see the category column here does contain
some blank cells so again I'm just going to select the column here. If you select the entire column
the formatting won't travel, the table formatting won't travel, so if we just select the table
column by hovering the top half there of the table header and left click twice that'll just
select the table column. Either way will really work. Go to my macros and run the list uniques
macro and here we have our list of unique values and you can see that blank cell has been removed
as well. So typically you probably see a blank cell in there after we've removed duplicates using
the remove duplicates feature but the macro takes an extra step to remove that blank cell
as well so it makes it much faster and easier you can use this list uniques macro on any column of
data in any workbook and it'll just be a one click operation to get a list of unique values. So I hope
that helps of course there's a lot of ways to remove duplicates in Excel so if you have another
technique please share it in the comments below. If you'd like to learn more about macros then
join me for a free upcoming webinar on the seven steps to getting started with macros in VBA.
During this training I'm going to explain how to write your first macro, what order the code runs
in, what the dots between the words mean, and some of these complex things that are hard to understand
when we're starting out with macros. So this will really help you automate Excel and save a ton of
time with your job. So click the link right below this video to get registered for the free webinar
and I look forward to seeing you there.
