3 Ways to Extract unique values in Excel using VBA

• Save

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.

Scenario :  

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.

• Save

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.

• Save

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

• Save

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 :

Steps:-

1.Select the cell or range

2. Go to ribbon click on data tab>>in the sort and filter group click on Advanced

• Save

• Save

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

1. Click on Copy to another location.
2. In the copy to box, enter the cell references eg. D1
3. 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.

=INDEX($A$2:$A$27,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$27),0))

The output is like below

• Save

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

• Save

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

• Save

Sub KDataScience_RemoveDuplicate()

    Range("A1").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("I1"), Unique:=True

End Sub

• Save

Krishna Kumar

Krishna is a Subject Matter Expert in the field of Excel and Access Automation using VBA, Data Analytics, BI Reporting, and Software Development. He has 12+ years of experience of handling and presenting Decision Oriented data to Business Leaders and executing end to end software development and Automation projects.