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: Intermediate

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

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 YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus
Checkout The Filters 101 Course to learn more about Advanced Filter and other time-saving filtering techniques.
Filters 101 Course Quick Overview 2 640x360

Download the File

Download the Excel file that contains the macro.

Remove Duplicates - List Unique Values Macro.xlsm (50.1 KB)

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.

Remove Duplicates to Create List of Unique Values in Excel

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.

Excel Remove Duplicates Feature

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.

Remove Duplicates for a Single Column in Excel

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.

Remove Duplicates for a Single Column in Excel Leaves Data in Other Columns

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…

Copy Column to New Sheet then Remove Duplicates

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:

  1. Copies the selected column to a new sheet
  2. Runs the RemoveDuplicates method
  3. Deletes any leftover blank cells
  4. 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.

Result of List Unique Values Macro

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 Long
Dim i As Long
Dim iColCount As Long

  'Check that a range is selected
  If TypeName(Selection) <> "Range" Then
    MsgBox "Please select a range first.", vbOKOnly, "List Unique Values Macro"
    Exit Sub
  End If
  
  'Store the selected range
  Set rSelection = Selection

  'Add a new worksheet
  Set ws = Worksheets.Add
  
  'Copy/paste selection to the new sheet
  rSelection.Copy
  
  With ws.Range("A1")
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
    '.PasteSpecial xlPasteValuesAndNumberFormats
  End With
  
  'Load array with column count
  'For use when multiple columns are selected
  iColCount = rSelection.Columns.Count
  ReDim vArray(1 To iColCount)
  For i = 1 To iColCount
    vArray(i) = i
  Next i
  
  'Remove duplicates
  ws.UsedRange.RemoveDuplicates Columns:=vArray(i - 1), Header:=xlGuess
  
  'Remove blank cells (optional)
  On Error Resume Next
    ws.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp
  On Error GoTo 0
  
  'Autofit column
  ws.Columns("A").AutoFit
  
  'Exit CutCopyMode
  Application.CutCopyMode = False
    
End Sub

Updates

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.

List_Uniques_Individual_Columns Macro to Remove Duplicates

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.

Add Custom Macro Button to Ribbon for List Unique Values Macro

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.

Advanced Filter to Create List of Uniques - Remove Duplicates - Excel

Here's and instruction guide on how to remove duplicates to create a list of unique values with Advanced Filter:

  1. Click the Advanced Filter button on the Data tab of the Ribbon.
  2. Select the “Copy to another location” radio button.
  3. Select the “List range”.  The range/column that contains the duplicate values.
  4. Select the “Copy to” range.  The cell where the new list of unique values will be output to.  Should be a blank column.
  5. Click the “Unique records only” checkbox.
  6. 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! 🙂

45 comments

Your email address will not be published. Required fields are marked *

  • 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?

    Thanks,
    Cindy

  • 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

    Thank you.

  • Jon, thank you very much for all your efforts and explanations and your offering the workbook that includes the code.

  • Hi Jon,
    Not sure if this application can help me fix a real world data entry error fix, or not…here 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?

  • Hi Jon,

    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?

  • 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.

  • 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
    Jane Doe
    Jane, Doe
    John James
    John, James

    And after:

    Column A Column B
    Jane Doe
    Jane Doe
    John James
    John James

    The second Jane Doe entry row must be deleted, as with the second John James row. Thanks!

  • Hi,

    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 lot

  • 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 it’s coming from.

  • Hi,

    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
    A 2
    A 3
    B 1
    B 3

    To become

    A 1 2 3
    B 1 3

    • Hi Dina,

      Great question! Yes, we could definitely have a macro create that result. I’ll 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.

      • 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.

        Thank you.

        • Hi Dina, Did you ever obtain a macro for the need you described above? I’m 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.

      • 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? Thanks

  • Hi Jon,
    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.
    Try this:

    – 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 (you’re 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à!!

    You’re so generous with all your tips, I thought I would show you this one!

    Cheers

    Daniel

  • Muito bom o seu trabalho, você tem boa didática e um artigo muito interessante e que poucas pessoas conhecem.

    Muito obrigado pelas dicas!

  • 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 it’s quick / easy to update.

  • 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.

    • Hey Walt,
      Great suggestion! I’ll probably do a follow-up video on that technique as well. As you said, it’s a great solution if your data is changing often and you want to quickly create a more dynamic list of unique values. Thanks! 🙂

  • 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.

  • Dear Jon,

    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…

    Thank you

  • Dear Jon,

    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!

  • Thank you for this valuable information
    Could you please make merging the doublicated data to fill the missing information in some rows?

  • 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

    C

  • Sir,

    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.

    Thank you.

    Regards,
    Oliver S. Daniel

  • 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 you’ll 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? 🙂

  • Advanced filter lets us filter selected range and copy it to another location.
    Check marking “Unique records only” removes duplicates.

    • 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! 🙂

      • 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.
        It’s a big click saver.

        Best regards,
        Leonid

  • 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).
    I’m 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 I’m sure there must be a better way.

  • Hi Jon,

    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.

    Best regards,

    Stefan

    • Hi Stefan,

      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.

      Thanks! 🙂

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly