Extract unique values in Excel is the most common data-crunching task in Excel. In this blog, we will learn multiple ways to Extract unique values in Excel.
Suppose We have a list of customer names. The list has some duplicate values. We wish to extract unique values from it.
3 WAYS TO Extract Unique Values In Excel
A column contains 26 records. Data start from A2 and end with A27.
Method to Extract Unique Values In Excel
1. Remove Duplicate
2. Advanced Filter
3. Index- Match Array Formula
4. Excel VBA
1. Remove Duplicate
“Remove Duplicates” option is the easiest way to extract unique values from a range.
Steps to apply to remove duplicates
Select the range where we want to remove duplicates>>Go to Data option in ribbon>>Click on Remove Duplicate>>Click On OK
Note-Remove duplicate removes the source data and contains only a unique list from the source data it means original data is removed.
2. Advance Filter :
1.Select the cell or range
2. Go to ribbon click on data tab>>in the sort and filter group click on Advanced
In the advance filter Dialog box, do the following.
If we want to copy the results of the filter to another location, do the following
- Click on Copy to another location.
- In the copy to box, enter the cell references eg. D1
- Select the unique records only
3. Index Match
For example Range A2:A27 contains duplicate values. We have applied the excel array formula in range D2 to extract unique records from the range.
The output is like below
3.1 IF BLANK VALUES IN A LIST
Suppose there are missing or blank values in our list from which you want to extract unique values. Then use the following formula.
=IFERROR(INDEX($A$2:$A$27, MATCH(0,IF(ISBLANK($A$2:$A$27),1,COUNTIF($F$1:F1, $A$2:$A$27)), 0)),””)
4.Using Excel VBA
The below code to the following
First, the code copies the data that contain duplicate value to the new location in sheet (H1)
Then remove the duplicates from the range H1
Sub KDataScience_RemoveDuplicate() 'To copy the range of data that conatain duplicate to new location Range("A1").CurrentRegion.Copy Range("H1") 'To remove the duplicate Range("H1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes End Sub
4.1 Using Advanced Filter
In this code, we have applied the advanced filter in range A1 and select unique records from them and paste them into the Range I1
Sub KDataScience_RemoveDuplicate() Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("I1"), Unique:=True End Sub