3 Ways to Remove Duplicates to Create a List of Unique Values in Excel

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

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.

Download the File

Download the Excel file that contains the macro.

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:

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.

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.

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:

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