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

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

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

 

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 24 comments
Daniel Lamarche - November 10, 2017

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

Reply
Barros - November 9, 2017

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
Tage - November 9, 2017

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.

Reply
Walt - November 9, 2017

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 - November 10, 2017

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

    Reply
Tim - November 9, 2017

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
Muhammad Arif - November 9, 2017

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

Reply
Edil Poulina - October 29, 2017

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!

Reply
Rafay - October 27, 2017

Thank You so very much, You really did an awesome Job,
Looking forward to your help using macros.
Thanks

Reply
Thirstyfresh - October 21, 2017

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

Reply
Charlie - October 20, 2017

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

Reply
Oliver S. Daniel - October 20, 2017

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

Reply
Chin Hooi - October 19, 2017

Thanks for the great Tips!

Reply
Allen - October 19, 2017

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

Reply
Leonid - October 19, 2017

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

Reply
    Jon Acampora - October 19, 2017

    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 - October 19, 2017

      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

      Reply
Michael - October 19, 2017

Just to be clear I need to remove Policy 123 completely from the worksheet because of the flat cancellation.

Reply
Michael - October 19, 2017

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.

Reply
Stefan Johansson - October 19, 2017

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

Reply
    Jon Acampora - October 19, 2017

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

    Reply

Leave a Reply: