Distinct Values from Excel

Some time we may have some requirement to extract the distinct values from an excel column. There is no direct formulas to do that. Instead of that we can do this in 2 ways. one is through a simple VB code and the second one is through Advanced Filter. This is applicable for Excel 2007 and higher versions as of my knowledge.

Solution 1: VB Program:

1. Press Alt + F11 to open VB editor window

2. Go to Insert menu >> Module

3. Copy and paste the below vba code into the window

 

Option Explicit

Sub CreateUniqueList()
Dim lastrow As Long

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

 Here it takes the distinct values from Column B and writing into Column D. Change accordingly.

 

 

Solution 2: 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