Unique Values Advanced Filter

One of Excel’s features which I use frequently is the Advanced Filter – Unique Values.
It’s pretty handy for analysing data and building summary / master tables.

From Excel’s menu: Data > Filter > Advanced Filter…

Copy to another location = selected.
List range: Select the list of values you want to see unique. Include the heading.
Copy to: Select a single cell. You can only copy filtered data to the active sheet.
Unique records only = ticked.

“Copy to” must be the active sheet but “List range” may be another sheet.
Read the comments for more info. Thanks r.venkataraman!

Posted in Uncategorized

5 thoughts on “Unique Values Advanced Filter

  1. quote You can only copy filtered data to the active sheet.

    not necessary Debra Dalgleish has a solution in
    I have made some notes on this webpage so that I and my brother can understand implia;tions easily -given below
    you make sheet 2 as activesheet. choose an empty cell in sheet2. then click data-advance filter. the data range and criteris range you go to sheet1 and choose the ranges. everytime you finish selecing it will come back to sheet2 because that is the activesheet.
    now click and now choose any cell in sheet2
    the filtered data is copied to sheet2 though database and criteria are in sheet1.

  2. I tried to copy filtered data on another sheet but it did not work, though it did work for only active worksheet.

  3. Here is some code that I use to get a list of unique items. Becuase I have to do it so often it is a pain to have to filter… This is a attached to a menu item that I have on a custom menu bar.

    Private Sub GetUniqueItems()
    Dim cell As Range ‘Current cell in range to check
    Dim rngToSearch As Range ‘Cells to be searched
    Dim dic As Scripting.Dictionary ‘Dictionary Object
    Dim dicItem As Variant ‘Items within dictionary object
    Dim wks As Worksheet ‘Worksheet to populate with unique items
    Dim rngPaste As Range ‘Cells where unique items are placed

    ‘Create range to be searched
    Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)

    ‘Confirm there is a relevant range selected
    If Not rngToSearch Is Nothing Then
    ‘Create dictionay object
    Set dic = New Scripting.Dictionary

    ‘Populate dictionary object with unique items (use key to define unique)
    For Each cell In rngToSearch ‘Traverse selected range
    If Not dic.Exists(cell.Value) And cell.Value Empty Then ‘Check the key
    dic.Add cell.Value, cell.Value ‘Add the item if unique
    End If

    If Not dic Is Nothing Then ‘Check for dictionary
    Set wks = Worksheets.Add ‘Create worksheet to populate
    Set rngPaste = wks.Range(“A1?) ‘Create range to populate
    For Each dicItem In dic.Items ‘Loop through dictionary
    rngPaste.NumberFormat = “@” ‘Format cell as text
    rngPaste.Value = dicItem ‘Add items to new sheet
    Set rngPaste = rngPaste.Offset(1, 0) ‘Increment paste range
    Next dicItem
    ‘Clean up objects
    Set wks = Nothing
    Set rngPaste = Nothing
    Set dic = Nothing
    End If
    End If
    End Sub

    You need to reference the Microsoft Scripting Runtime to use this…

  4. Mr John Walkenbach has also offered a possible solution for working with unique items.

    Try http://j-walk.com/ss/excel/tips/tip15.htm

    It is a UDF called UniqueItems (starting at example 4). Since I use it quite often I have exchanged UniqueItems for just Unique and vice versa to reduce the typing involved. I hope this helps.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.