Dynamically Pulling Unique Values

**starbwoy** · 04-22-2008, 11:03 AM

Hi Guys- I need some help pulling unique records from a list dynamically. Tax Min will be pasted in the sheet every day and will have different values each day. I need to know every unique value within the list.

In the attachment, I need column C to have a sorted list of each value in col A. I need these values to change as the values in col A change daily.

Thanks for any help you can provide.

**martindwilson** · 04-22-2008, 11:59 AM

that can be achieved using advanced autofilter
using the copy and unique records options then sorting
or record macro or use macro like below to do it and assign to button

Sub uniquesort()

    Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
        "C:C"), Unique:=True
    Columns("C:C").Select
    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

**starbwoy** · 04-22-2008, 01:57 PM

But the advanced autofilter is not dynamic to changing data. Isnt that correct?

Thanks

**Portuga** · 04-22-2008, 02:00 PM

You can create a pivot table from that column and everytime you add new values they will be automatically be displayed in the pivot (Once refreshed)

**martindwilson** · 04-22-2008, 07:55 PM

correct its not dynamic..yet
working on macro that will run after paste
vba noob has given me a snippet thet may do the trick

**martindwilson** · 04-23-2008, 07:36 AM

perhaps this will work for you just past into a1 including title,
on attached try it out select column h and paste into a1 then try column j and paste into a1
code is

Private Sub Worksheet_Change(ByVal Target As Range)


Select Case Application.CutCopyMode
    Case xlCopy
        Call uniquesort
    End Select

End Sub

Sub uniquesort()
Application.Wait Now + TimeSerial(0, 0, 2)

    Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _
    "C:C"), Unique:=True
    Columns("C:C").Select
    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

it goes in a worksheet module for sheet you require sorting