Need to list all values present in the column

[stepan1987]

Hi guys,
I need to get a range of all values present in one column and list them in the other column:

Red
Blue
Red
Red
Green
Grey
Green

Need to get in alphabetical order:

Blue
Green
Grey
Red

Which formula does that? Thanks in advance

 

[venkat1926]

why do you want a formula

suppose data is like this in A1 to A8

colors
Red
Blue
Red
Red
Green
Grey
Green

highlight A1 to A8
clcik data-filter-advancedfilter
choose "copy to another location"
check "unique records only" at the bottom
against "copy to" type B1
click OK
.
now you wil get B1 to B6

colors
Red
Blue
Green
Grey

sort column B ascending.

 

[stepan1987]

Thanks, I need to write a formula to write it into a macros. Is there an appropriate one?

 

[Jameo]

Sub test()
    Range("=OFFSET($A$1,0,0,COUNT($A:$A),1)").Select
    Range("=OFFSET($A$1,0,0,COUNT($A:$A),1)").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "B1"), Unique:=True
    Range("=OFFSET($B$1,0,0,COUNT($B:$B),1)").Select
    Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

Where A is the column your data is in, and B is the column to copy and sort your data to.

 

[stepan1987]

Jameo , thanks for the script, but is there a formula) as I am not an experienced script writer, I use record macros as a donk..