This tutorial describes multiple ways to extract a unique or distinct list from a column in Excel. This post also covers a method to remove duplicates from a range. It's one of the most common data crunching task in Excel.
Scenario
Suppose you have a list of customer names. The list has some duplicate values. You wish to extract unique values from it. Unique values would be a distinct list. To make it more clear, unique values are the values that appear in a column only once.
Sample File
Click on the link below and download the excel file for reference. We will use this workbook to demonstrate methods to find unique values from a column.
The dataset contains 13 records. Data starts from cell B3 and ends with cell B15. Header of the list exists in cell B2. LIST1 Jhonson Jhonson Jhonson Dave Dave Deepanshu Sohan Neha Deepanshu Neha Deepanshu Neha Sohan See the snapshot of actual data in images below.
4 Methods to Extract Unique Values
Advanced Filter
Index- Match Array Formula
Excel Macro (VBA)
Remove Duplicates
The above methods are explained in detail in the following sections.
Solutions
1. Advanced Filter
Follow the steps shown in the animation below
Steps to extract unique values using Advanced Filter
Go toDatatab in the menu
In Sort and Filter box, ClickAdvanced button
Choose "Copy to another location"
In"List range :"box, select a range from which unique values need to be extracted (including header)
In"Copy to :"box, select a range in which final output to be put
CheckUnique records only
Click Ok
2. INDEX-MATCH (Array Formula)
Extract Unique Values - Formula
FORMULA
=IFERROR(INDEX($B$3:$B$15, MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0)),"") HitCTRL+ SHIFT + ENTERto 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 :
Copy the above formula and paste it into cell D3. And paste it down till the cell D12 (Select the range D3:D12 and press Ctrl+D).
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 -
Extract Unique Values given blank values
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. And paste it down till the cell D12 (Shortcut key :Ctrl+D).
You need to pressCTRL+ SHIFT + ENTER to submit this formula. It is different than the standardENTERbutton to enter a formula. If you do it right, MS Excel will put curly braces {...} around the formula. It would view like this :
First we need to understand the meaning and use of array formula.
Array formulaallows you to process a certain operation on multiple values using a single function. In other words, we can perform some calculation on more than one value without doing it manually on each cell. For example, you want to multiply each value by 5 and then sum all of the returned values.from multiplication. Suppose following values are stored in cell A1:A3 25 35 45 Enter the formula =SUM(A1:A3*5) withCTRL+SHIFT+ENTER. It returns 525. In this case, it is doing matrix multiplication and then adds all the numbers.
Functioning of Formula : Step by Step
Step 1 :COUNTIF($D$2:D2, $B$3:$B$15)
Syntax :COUNTIF(range, condition) It counts the number of cells within a range that meet the given condition
COUNTIF($D$2:D2, $B$3:$B$15) returns 1 if $D$2:D2 is found in $B$3:$B$15 else 0.
For example, for the second distinct record Dave, the formula becomes COUNTIF($D$2:D3, $B$3:$B$15). It is searching values D2 and D3 in the range B3:B15. The array becomes
={1;1;1;0;0;0;0;0;0;0;0;0;0}. It is 1 when values of D2 and D3 are found and 0 where it is not found.
Step 2 : In this step, we are checking the position of item that has an array value 0 in Step I.
It gives the relative position of an item in an array that matches a specified value.
MATCH(0,COUNTIF($D$2:D2, $B$3:$B$15), 0) returns 4 for the second distinct value. It is 4 because the value Dave is placed in the fourth position of the list. [Also see 0 is the fourth value of the step 1 array - {1;1;1;0;0;0;0;0;0;0;0;0;0}]
Step 3 : In this step, we extract the desired distinct value. The INDEX function helps to achieve it.
Syntax :INDEX(array,row_num,[column_num])
The INDEX function returns the reference of cell meeting row and column number in a given range.
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
How to create unique list using macro
1. Go to excel sheet where data exists. 2. Press Alt + F11 to open VB editor window
3. Go toInsert menu>> Module.It will create a module.
4. In the module, copy and paste the above vba code into the window 5. Close VB Editor Window 6. Go back to your sheet 7. Press Alt + F8. SelectCreateUniqueListunder Macro name box and HitRunbutton.
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?
ChangeActiveSheet.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 Explicit Sub CreateUniqueList() Dim lastrow As Long Dim ws As String ws = ActiveSheet.Name lastrow = Cells(Rows.Count, "B").End(xlUp).Row Sheets.Add.Name = "mysheet" Sheets(ws).Range("B2:B" & lastrow).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=Sheets("mysheet").Range("D2"), _ UNIQUE:=True End Sub
I have added one more way to extract unique values from a range [Updated : June 2016]
4. Remove Duplicates Option
The most easiest way to extract unique values from a range is to use"Remove Duplicates"option. See the snapshot below -
Unique values : Remove Duplicates Option
Warning : If you want to keep your original data (not overwrite unique values), make a copy of it (Paste original data to another range or tab) Otherwise original data would be removed. Steps to remove duplicates
Select range >> Go toData option>> Click onRemove Duplicates>> Select the column that contains duplicates >>Ok
Important Note :
If you have multiple columns in a range and you want to remove duplicates based on a single column, make sure only the column that contains duplicates is selected.
Remove Duplicates by a column
2. If you want to remove duplicates based on all the columns (whole row), make sure all the columns are selected.
For i = 3 To lngE If Range("e" & i) = "여" Then If rngD Is Nothing Then Set rngD = Range("e" & i).Offset(0, -3).Resize(1, 10) Else Set rngD = Union(rngD, Range("e" & i).Offset(0, -3).Resize(1, 10)) End If End If Next
If rngD Is Nothing Then MsgBox "복사할 범위가 없습니다." Else Range("p3").CurrentRegion.Offset(1, 0).Clear rngD.Copy Range("p3") End If Range("a1").Select End Sub
Sub Test_03()
Dim lngE As Long Dim rngD As Range Dim i As Long
lngE = Cells(Rows.Count, "b").End(xlUp).Row
For i = 3 To lngE If Range("e" & i) = "여" And Range("k" & i) >= 60 Then '여학생이면서 평균 60점 이상 If rngD Is Nothing Then Set rngD = Range("e" & i).Offset(0, -3).Resize(1, 10) 'rngD.Select '단계별 실행에서 확인용 Else Set rngD = Union(rngD, Range("e" & i).Offset(0, -3).Resize(1, 10)) 'rngD.Select '단계별 실행에서 확인용 End If End If Next
If rngD Is Nothing Then MsgBox "복사할 범위가 없습니다." Else Range("p3").CurrentRegion.Offset(1, 0).Clear rngD.Copy Range("p3") End If
