4 Ways to Extract Unique Values in Excel (2024)

1. Advanced Filter

Follow the steps shown in the animation below

Steps to extract unique values using Advanced Filter

1. Go to Data tab in the menu
2. In Sort and Filter box, Click Advanced button
3. Choose "Copy to another location"
4. In "List range :" box, select a range from which unique values need to be extracted (including header)
5. In "Copy to :" box, select a range in which final output to be put
6. Check Unique records only
7. Click Ok

2. Remove Duplicates Option

The most easiest way to extract unique values from a range is to use "Remove Duplicates" option. See the snapshot below -

Warning : If you want to keep your original data without replacing unique values, make a copy of it by pasting the data into another location. Otherwise, the original data will be deleted.

Steps to remove duplicates

1. Select the Range of cells from which you want to get unique values.
2. Go to the Data Tab in the ribbon at the top of Excel.
3. Click on "Remove Duplicates" under the "Data Tools" group.
4. Select the column that contains duplicates and Click Ok. If you have multiple columns in a range, make sure only the column that contains duplicates is selected.

Note: If you want to remove duplicates based on all the columns (whole row), make sure all the columns are selected.

3. INDEX-MATCH (Array Formula)

FORMULA

=IFERROR(INDEX($B$3:$B$15, MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0)),"")

Copy the above formula and paste it into cell D3. Hit CTRL+ SHIFT + ENTER to confirm this formula as it's an array formula. If done correctly, Excel will automatically place curly braces {...} around the formula. After placing curly braces, the above formula would look like this :

{=IFERROR(INDEX($B$3:$B$15, MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0)),"")}

To paste it down till the cell D12, select the range D3:D12 and press Ctrl+D shortcut key to fill down.

HOW TO USE

The functioning of this method is visible in the animated image below.

Version 2 : IF BLANK VALUES IN A LIST

Suppose there are missing or blank values in your list from which you want to extract unique values. In this case, you need to tweak your formula. The modified formula is explained below -

FORMULA

=IFERROR(INDEX($B$3:$B$15, MATCH(0,IF(ISBLANK($B$3:$B$15),1,COUNTIF($D$2:D2, $B$3:$B$15)), 0)),"")

Copy the above formula and paste it into cell D3. You need to press CTRL+ SHIFT + ENTER to submit this formula. To paste it down till the cell D12, you can select the range D3:D12 and use Shortcut key : Ctrl+D.

How this formula works

See the steps below how the function extracts second distinct value.

Step 1: Use COUNTIF($D$2:D3, $B$3:$B$15) returns 1 against the first distinct value (Jhonson) in range $B$3:$B$15. For example COUNTIF($D$2:D3, $B$3:$B$15) becomes ={1;1;1;0;0;0;0;0;0;0;0;0;0}.

Step 2: Check the position of 0 using the MATCH function. It returns 4 for the second distinct value.

Step 3: Extracts the fourth name from the original list (Column B) using the INDEX function. This returns Dave.

Tutorial : Excel Array Formula with Examples

4. MACRO (Advanced Filter)

It's an excel macro to find distinct values from a column in Excel. In this method, we are using the same logic as we have done in first method i.e. Advanced filter. Here, we are applying advanced filter via excel macro rather than doing it manually.

VBA CODE

Option ExplicitSub CreateUniqueList()Dim lastrow As Longlastrow = Cells(Rows.Count, "B").End(xlUp).Row ActiveSheet.Range("B2:B" & lastrow).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=ActiveSheet.Range("D2"), _ Unique:=True End Sub

How to create unique list using macro

1. Go to excel sheet where data exists.
2. Press Alt + F11 to open Visual Basic Editor.
3. Go to Insert menu >> Module. It will create a module.
4. In the module, copy and paste the above VBA code into the window
5. Close the VB Editor Window
6. Go back to your sheet
7. Press Alt + F8. Select CreateUniqueList under Macro name box and Hit Run button.

Download the workbook

Customise Macro Code

The following are two most frequently asked questions about above excel macro with solutions. If you have any other question regarding the macro, post your question on comment box below.

Q. How to paste unique values to another existing worksheet?

Change ActiveSheet.Range("D2") to Sheets("newsheet").Range("D2")

In the above code, change "newsheet" to the name of the existing sheet wherein you want to paste unique values.Q. How to paste unique values in a new worksheet?Use the program below. It will paste distinct values to a new worksheet named "mysheet". You can change it to any name you want -

Option ExplicitSub CreateUniqueList()Dim lastrow As LongDim ws As Stringws = ActiveSheet.Namelastrow = Cells(Rows.Count, "B").End(xlUp).RowSheets.Add.Name = "mysheet"Sheets(ws).Range("B2:B" & lastrow).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Sheets("mysheet").Range("D2"), _ UNIQUE:=TrueEnd Sub