Macros & VBA, Tables & Data, Tools
3 Ways to Remove Duplicates to Create a List of Unique Values in ExcelOctober 18, 2017Jon Acampora53 comments
Bottom line: In this article and video I explain 3 ways to remove duplicates to create a list of unique values. We look at the Remove Duplicates feature and a macro that makes the process much faster. I also added a video on how to use the Advanced Filter to list unique values.
Skill level: IntermediateWatch on Youtube & Subscribe to our Channel
Register for the Free Training on Macros & VBA
Video #2: List Unique Values with Advanced Filter
Thanks to Leonid, Bart, and Charlie for leaving a comment about using Advanced Filter for this process. Here is a video that explains how to use the Unique Records Only feature of the Advanced Filter tool in Excel.Watch on YoutubeCheckout The Filters 101 Course to learn more about Advanced Filter and other time-saving filtering techniques.
Download the File
Download the Excel file that contains the macro.Remove-Duplicates-List-Unique-Values-Macro.zipDownload
Creating a List of Unique Values
One common task we do as data analysts is creating a list of unique values from a column in a data set. We can use the list for a lookup table, summary report, drop-down menu, etc.
We typically want to extract this list of uniques from a column in a table/range that contains duplicate values. This process can require multiple steps and be a bit time consuming.
In this article we'll look at how to use the Remove Duplicates feature of Excel for this task, the time consuming method. Then we will look at a faster method by using a macro to create a list of unique values. That means the entire process can be completed with a click of a button, and save us a lot of time.
Method #1 The Remove Duplicates Feature
Excel has a built-in feature called Remove Duplicates that does most of the work for this task. We can select a range or Table, then press the Remove Duplicates button on the Data tab of the Ribbon.
Keyboard shortcut: Alt+A+M
This brings up the Remove Duplicates window where we can select which column(s) we want Excel to remove duplicates from.
If we leave all checkboxes checked, then Excel will look for entire duplicate rows. That means each cell in the row has to be identical to the values in each cell of another row. If it finds a match then it will delete that row.
We can also use Remove Duplicates on a single column, by unchecking all boxes, then checking the column. This will delete all rows that contain a duplicate value in the single column.
The first row that contains the unique value is kept, and all other rows below that contain a duplicate for that cell in the column are deleted.
This can be useful in some scenarios, but it's not really what we want for our list of unique values. We really just want a list of the unique values on a new sheet in the workbook.
Therefore, we first need to copy the column to a new sheet, then run the Remove Duplicates feature on that single column. There are quite a few steps in this process, and an additional step if your column contains blank cells that you also want to remove
Use Remove Duplicates with Caution
It's really important to know that the Remove Duplicates feature deletes rows on the selected range. It does NOT copy the data to a new sheet first. You have to do that step manually.
This means you could potentially lose data if you run Remove Duplicates on your original source data range/Table. So, I recommend copying your data to a new sheet first or duplicating the sheet.
Checkout my article on 17 Excel Shortcuts for 2017 for tips on how to duplicate a sheet.
It would be great if Excel gave us the option to copy and output the results of Remove Duplicates to a new sheet or range on an existing sheet. You can vote for this feature on the Excel Uservoice site.
Method #2 Create List of Unique Values Macro
Of course we can use our old friend VBA to make this process much faster with a macro. In the video above I show how we can use a macro to complete the entire process with a click of a button.
The List Unique Values macro performs the following actions:
- Copies the selected column to a new sheet
- Runs the RemoveDuplicates method
- Deletes any leftover blank cells
- Autofits the column width.
All the steps we had to do manually can be completed automated. The macro can be run on any column in any open workbook.
Here is the macro code that you can copy to your own VBA Project.Option Explicit Sub List_Unique_Values() 'Create a list of unique values from the selected column'Source: https://www.excelcampus.com/vba/remove-duplicates-list-unique-values Dim rSelection As Range Dim ws As Worksheet Dim vArray() As LongDim i As LongDim iColCount As Long 'Check that a range is selectedIf TypeName(Selection) <> "Range" ThenMsgBox "Please select a range first.", vbOKOnly, "List Unique Values Macro" Exit SubEnd If 'Store the selected rangeSet rSelection = Selection 'Add a new worksheetSet ws = Worksheets.Add 'Copy/paste selection to the new sheetrSelection.Copy With ws.Range("A1") .PasteSpecial xlPasteValues .PasteSpecial xlPasteFormats '.PasteSpecial xlPasteValuesAndNumberFormatsEnd With 'Load array with column count'For use when multiple columns are selectediColCount = rSelection.Columns.Count ReDim vArray(1 To iColCount) For i = 1 To iColCount vArray(i) = i Next i 'Remove duplicatesws.UsedRange.RemoveDuplicates Columns:=vArray(i - 1), Header:=xlGuess 'Remove blank cells (optional)On Error Resume Nextws.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp On Error GoTo 0 'Autofit columnws.Columns("A").AutoFit 'Exit CutCopyModeApplication.CutCopyMode = False End Sub
Note: I updated the macro on 11/10/2017 based on a question from Rich about using it on multiple columns. The macro will now work to remove duplicates on multiple columns when you select more than one column before running the macro.
I also added another macro to the file based on a suggestion from Arun. The second macro is named,List_Uniques_Individual_Columns, and it loop through the selected columns and removes the duplicates from each column individually. Here's a screenshot of the before and after if we select the 3 columns and then run the macro.
How to use the macro
The List Unique Values macro can be stored in your Personal Macro Workbook and assigned to a macro button on the Ribbon. This means you can run it on the selected range by pressing a button or creating a keyboard shortcut for it.
This is one of those simple macros that will save time with this common Excel task.
Checkout my 4-part video series on The Personal Macro Workbook, which includes a video on how to add macro buttons to the ribbon.
Method #3: Advanced Filter
Another option for removing duplicates is using the Advanced Filter. This is a tool that is built into Excel and located on the Data tab of the ribbon.
Advanced Filter has a checkbox labeled Unique records only that will remove duplicates and output a list of unique values to the specified range.
Checkout the video above for an explanation of how to use the Advanced Filter for this technique.
Here's and instruction guide on how to remove duplicates to create a list of unique values with Advanced Filter:
- Click the Advanced Filter button on the Data tab of the Ribbon.
- Select the Copy to another location radio button.
- Select the List range. The range/column that contains the duplicate values.
- Select the Copy to range. The cell where the new list of unique values will be output to. Should be a blank column.
- Click the Unique records only checkbox.
- Click the OK button.
The Advanced Filter will paste the values of the unique items starting in the cell specified in the Copy to range.
Other Ways to Create a List of Unique Values?
There are many ways to approach this task. We can also use a pivot table to create a list of unique values by putting the field in the Rows area.
What technique do you use to remove duplicates. Please share by leaving a comment below. Thank you! Previous 3 Ways to Add or Subtract Days to a DateNext New VB Editor for Excel 2016 for Mac
You may also like
How to Prevent Excel from Freezing or Taking A Long Time when Deleting Rows
Quick Tips and Shortcuts for Renaming Excel Tables
How to Search Data Validation Drop-down Lists in Excel
How to Split Text in Cells with Flash Fill in Excel
Stefan Johansson says:October 19, 2017 at 5:40 am
Another great video. I have a suggestion to further expand the utility of the macro: as a last step in manipulating the data into one column with unique values, how about sorting the values alphabetically? That way, it will be even easier to find a particular value.
Jon Acampora says:October 19, 2017 at 9:48 am
That is a great suggestion! Here is the code to sort column A in ascending order on the new sheet.'Sort the data
ws.Columns("A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess
That code can be added below the Autofit column width line.
Michael says:October 19, 2017 at 8:32 am
Removing duplicates is a big part of my job working with insurance bdx. The paste buddy has been a wonderful tool with its ability to let me add premium together when combining transactions. Right now I need to go through the file manually after using conditional formatting to identify duplicates so that I can combine transactions together as needed.
What would save me time is the ability to identify flat cancellations where the Policy Name and the Transaction Date are the same, but the premium would be the exact opposite.
eg. Policy 123 is Renewed Oct 1 for $100 is on line 1 while on line 2 Policy 123 is Cancelled on Oct 1 for ($100).
Im experimenting with using Paste Buddy to change the Cancellation Premiums to positive by multiplying them by -1 and then running Remove Duplicates based on the columns for Policy #, Transaction Date, and Premium, but Im sure there must be a better way.Reply
Michael says:October 19, 2017 at 8:37 am
Just to be clear I need to remove Policy 123 completely from the worksheet because of the flat cancellation.Reply
Leonid says:October 19, 2017 at 11:05 am
Advanced filter lets us filter selected range and copy it to another location.
Check marking Unique records only removes duplicates.Reply
Jon Acampora says:October 19, 2017 at 1:47 pm
Great suggestion Leonid! Advanced Filter is another good way to create a list of unique values.
The only limitations are that you have to copy the range to the existing sheet, and the formatting does not paste. But still a fast way to go about it.
Thanks for sharing! Reply
Leonid says:October 19, 2017 at 3:30 pm
To copy data without duplicates from the source sheet to the target sheet we should start the advanced filter dialog from the target sheet.
It could be Excel version difference, but I do not have a problem to paste formatting with advanced filter.
Remove Duplicates button is one of my favorite additions to Excel.
Its a big click saver.
Allen says:October 19, 2017 at 2:39 pm
A temporary helper column on the original sheet works for me. I populate the helper column with a CountIf formula. Assuming the column to create the unique list from is in Column A the formula on row two is: =COUNTIF($A$2:A2,A2). This is copied to all the rows that have data. I then use the AutoFilter to filter for rows in the helper column that evaluated to 1. (Each instance of a duplicate beyond the first one will have a number higher than 1 and will be hidden by the AutoFilter.) Then highlighting Column A and using ,H,F,D,S,Y, followed by C to copy the visible rows I can then paste a unique list of data to a new location. Unhide the hidden rows, delete the helper column and you have a unique list of data to use with your original data left undamaged. Once you get the hang of it youll only spend a few seconds. The main drawback is that if your original table is thousands of rows CountIf can slow down a lot. I have successfully done this on spreadsheets with several hundred thousand rows, but it could take 10+ minutes Are you that patient? Reply
Chin Hooi says:October 19, 2017 at 3:57 pm
Thanks for the great Tips!Reply
Oliver S. Daniel says:October 20, 2017 at 2:25 am
I was watching this video and I found it very interesting. especially the macro part. I am not a seasoned user of Excel having got into it only after my retirement and I have written a few macros myself though they are very basic and not complicated. I saw the spread sheet that you were using and it contained a My Macros tab on it next to the Developer tab. How do you create such a tab? All I have managed is to get them inside the Home tab and so the name of the macros cannot be seen in their entirety.
Oliver S. DanielReply
Charlie says:October 20, 2017 at 7:59 am
Thanks for the tips. Something I have been doing for years is to highlight the column you want the unique values from. On the data tab hit advanced to bring up the Advanced Filter dialog. In the Action section select the Copy to Another Location radio button. This will activate the Copy to box, basically asking where do you want to list the unique values.I just use and entire column, something like R:R, as the copy to range. Lastly check the Unique Records Only box, hit OK and there you have a unique list of values
Thirstyfresh says:October 21, 2017 at 11:16 am
Thank you for this valuable information
Could you please make merging the doublicated data to fill the missing information in some rows?Reply
Rafay says:October 27, 2017 at 12:16 am
Thank You so very much, You really did an awesome Job,
Looking forward to your help using macros.
Edil Poulina says:October 29, 2017 at 8:25 am
This was very interesting and useful to me, as I work a lot with data.
I even recorded a macro to remove certain data (e.g. certain values) using an input box where I provide the value (text or number).
Your macro has enlighten me, and I will use it to improve mine, so thank you very much!Reply
Muhammad Arif says:November 9, 2017 at 7:05 am
I was this video nice control in Excel of unique value but I have use more function.
I ask one question sheet 1 sheet 2 how to merged with pivot table please requested for emergency need. Immidiately reply with us
Tim says:November 9, 2017 at 7:29 am
Great information. I always learn something. I have typically used a pivot table to find unique data. This gives additional options and showed me some new things in VBA that will be a big help in macros that could use some cleaning up.Reply
Jon Acampora says:November 10, 2017 at 10:11 am
Great suggestion! Thanks Tim! Im happy to hear you learned some VBA tips too. Reply
Walt says:November 9, 2017 at 8:35 am
Not sure if this has been mentioned but you can create a unique list of values using Power Query. This method has a bonus feature in which it will keep the list up to date if any new records are added to the main data table. Just use the refresh option from the right click menu to update or setup automatic updates for the unique list query so it refreshes when file is opened.
Great video as always.Reply
Jon Acampora says:November 10, 2017 at 10:12 am
Great suggestion! Ill probably do a follow-up video on that technique as well. As you said, its a great solution if your data is changing often and you want to quickly create a more dynamic list of unique values. Thanks! Reply
Tage says:November 9, 2017 at 8:46 am
I always use a pivot table to create a list of unique values. This avoids messing with the raw data, which I like to leave untouched. And its quick / easy to update.Reply
Jon Acampora says:November 10, 2017 at 10:12 am
Great suggestion Tage! Thanks! Reply
Barros says:November 9, 2017 at 10:05 am
Muito bom o seu trabalho, você tem boa didática e um artigo muito interessante e que poucas pessoas conhecem.
Muito obrigado pelas dicas!Reply
Jon Acampora says:November 10, 2017 at 10:14 am
Obrigado Barros! Sorry, thats all the Portuguese I know, but I really appreciate your support. Reply
Daniel Lamarche says:November 10, 2017 at 12:40 pm
Years ago I saw an easy way to have the unique values (using Advanced Filter) appeared in another worksheet instantly! I was amazed because I did say for quite some time that the Advanced Filter could not do that.
First, select a cell in the sheet where you want that unique list to appear (that needs to be the first step!).
Launch the Advanced Filter window and check Copy to another location.
Tab to List Range and go to the source sheet and select the source range.
Tab to Copy To (youre back to the target sheet) and click the cell in step 1 or any other one. The Copy To box now reads: SheetName!Cell
Tab to Unique records only and hit Spacebar.
Hit Enter et Voilà!!
Youre so generous with all your tips, I thought I would show you this one!
Jon Acampora says:April 7, 2018 at 6:03 am
Thanks for the suggestion on keyboard shortcuts for Advanced Filter. I love it! And sorry to not reply sooner. I didnt see your comment.Reply
Jon Acampora says:April 7, 2018 at 6:04 am
btw the Advanced Filter technique is explained in #3 above for anyone reading this.Reply
Conrad Blume says:November 17, 2017 at 1:25 pm
This is FANTASTIC!!
I learn so much from this site, thank you for taking the time to share your knowledge!Reply
Dina says:April 4, 2018 at 11:54 am
Thanks for the video. Can we make a more advanced feature where I can copy the unique values referring to the duplicated values horizontally. For example
A 1 2 3
B 1 3Reply
Jon Acampora says:April 7, 2018 at 6:19 am
Great question! Yes, we could definitely have a macro create that result. Ill think about it and add to my list for future posts.
I believe you could use a pivot table for this if the second column contains numbers only.Reply
Dina says:April 26, 2018 at 8:50 am
I wish, my list is quite complicated and have all kinds of information on thousands of rows and many columns. This would save me HOURS of work sorting the data manually. If you ever developed that macro (not my field of expertise) could you kindly share it here.
Dale Dorsey says:November 19, 2018 at 12:14 pm
Hi Dina, Did you ever obtain a macro for the need you described above? Im needing the same thing as I have thousands of duplicate that I want to maintain only one and place other unique items in the same row but different columns.Reply
Dale Dorsey says:November 19, 2018 at 12:17 pm
Jon, Did you ever create a macro for Dina by chance and if so, is it downloadable. I would gladly donate or pay something to have this tool? ThanksReply
Margaret says:December 13, 2019 at 9:55 am
Oh yes! I need this same thing. Any updates on how to do this?Reply
chaitenya says:April 18, 2018 at 12:57 am
Are you able to do the above macro? Please share me the details.
Paul B says:April 28, 2018 at 3:48 am
Another suggestion is to use TRIM to remove spaces. Doing this before remove duplicates would eliminate even more duplicates depending on on clean your data is on where its coming from.Reply
Carlo says:August 3, 2018 at 8:25 am
How can I make the following:
sandeep kothari says:September 20, 2018 at 7:50 pm
Nirmala says:December 2, 2018 at 9:15 pm
This was really very nice and understandable for person like me who really wants to learn more on excel and implement in my work.
Good learning and very legibly explained.
thanks a lotReply
Alicia says:December 18, 2018 at 1:36 pm
How would I go about deleting duplicate rows: when the names have been text delimimnated? I deliminate them because when the report forms, entries are not standardized. Some have commas between names, and some do not. So, I am unable to highlight and delete duplicates (because they are not the same).
The list will look like this before:
Column A Column B
The second Jane Doe entry row must be deleted, as with the second John James row. Thanks!Reply
Danielle says:February 16, 2019 at 1:34 pm
Hello, thank you for such a helpful source! but I use the Remove Duplicates feature, it misses some items that are supposed to be removed. Each duplicate is exactly identical in terms of spelling and grammatical case. Any clue on how to include those missing duplicates under the feature? Thank you in advance.Reply
Lori L Laska says:February 17, 2019 at 1:43 pm
What if I want to remove all of the duplicates from column a2:a and have that unique list appear in column c of the same sheet, so that I can create a dynamic range of unique values for data validation to create a list?Reply
seth says:May 13, 2019 at 8:46 am
Not sure if this application can help me fix a real world data entry error fix, or nothere you go!
I am an operations engineer for a small manufacturer that uses customer order entries in Excel to generate the production schedule tasks for operators. We need to flag these duplicate order entries to review for production errors and It has been a while since i used VBA to code macros in college so how exactly can i translate your information into removing duplicate order entries by our customer service personnel?Reply
Cmb says:July 2, 2019 at 5:51 am
Jon, thank you very much for all your efforts and explanations and your offering the workbook that includes the code.Reply
Kyle says:September 24, 2019 at 4:19 pm
How do I use Macro to only look for unique values in one column and paste the row of results into a new sheet? for example,
Original sheet New sheet
A B C A B C
1 a b 3 a f
1 a c 4 a g
2 a d
2 a e
3 a f
4 a g
Kyle says:September 24, 2019 at 4:21 pm
That didnt show very clear.
Original sheet (input)
A B C
1 a b
1 a c
2 a d
2 a e
3 a f
4 a g
New sheet (Macro)
A B C
3 a f
4 a gReply
Cindy says:November 15, 2019 at 2:48 am
Hello. We have a file where students sign in for tutoring each afternoon. We are trying to compile a list of how many times each student visited the tutoring center in a month. So, I need to do 2 things: count duplicates and remove duplicates. I can do this on a one-time basis by creating a COUNT formula, copying the results to a new sheet, and then removing duplicates.
Is there a way to accomplish this on an ongoing basis such that, any time a student signs in, his or her number of visits automatically updates, but on a list of unique values?
Anne L Arrowsmith says:December 11, 2019 at 7:15 am
Well, since no one has answered, Ill give it a shot.
Look up Pivot Tables. The attendance table will just contain data, no formulas. The report will be on a second sheet. The values on the report will be unique. There will be no need to (i.e. you should not) keep separate columns, sheets, or workbooks for different months, terms, or years.
Keep two columns: StudentName, TutoringDate. (You should be using a StudentID, but Ill skip that for now.)
Select both columns (whole columns).
Insert > Pivot Table (new sheet). Switch to the new sheet.
Drag StudentName to the Rows box.
Drag TutoringDate to the Columns box.
Drag StudentName again to the Values box.
In the boxes below:
Left click on StudentName in the Columns Box, select Value Field Settings. Make sure Summarize Values By says Count.
In the Pivot Table itself:
Right-click on TutoringDate. Make sure Group says Months and Years.
Left-click on the down-arrow at the right of TutoringDate. Uncheck blanks.
Done. Enter new data, refresh the Pivot Table, and new totals will display.Reply
Roy says:April 30, 2020 at 5:36 pm
Thanks Jon! Great video on creating a list of unique values from a column.Reply
Judy Chu says:February 5, 2021 at 1:53 pm
Your video is really helpful, I have a question regarding the data as below:
Last First Name Employee_Number Department RegHr OvtHr PTOHr Mileage AftHrAV AftHrDM AftHrEV AftHrRG
ALLEN AMANDA 513 200 249.06
ALLEN AMANDA 513 200 59.07
ALLS COLETTE 456 600 4
ALLS COLETTE 456 600 33
ALLS COLETTE 456 600 115.72
Not just I need to remove the duplicate names also need to combine multiple rows into one to be imported to a different system.
Raze says:February 12, 2021 at 4:36 am
HEY IDIOT! Do you even know what Unique means?
It should be no other items that has duplicates being included!
If it has a duplicate then its not unique!
You should also remove the VERY last item of those duplicates!
If you cant understand that you really an IDIOT!Reply
Interested Bystander says:September 23, 2021 at 4:51 am
Well youre a nasty piece of work Raze. Give me an idiot any day than an angry person like you. Keep taking the tables.Reply
Jeff says:February 26, 2021 at 7:06 am
Samuel says:May 23, 2022 at 6:43 am
Hi, my name is Samuel and Im working on a project. Ive been using your formula to get rid of duplicates and its working perfectly. Sadly since my knowledge with coding is not that great, Id like to ask for a small help. Could you tell me how to change code so it pastes duplicates into existing sheet/sheet that I already made and it doesnt create a new one? Would greatly appreciate itReply
More results... Generic filters Hidden labelExact matches only
Excel Shortcuts List
Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.
Excel Shortcuts List
Join Our Weekly Newsletter
The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.Join Our Free Newsletter