Uh, sorry, that function that I posted removes blank spaces...
Try this (I got it on this DG a while back):

Sub Uniques()
Dim i As Integer
i = 1
Do Until Cells(i, 1).Value = "" '(as long as your data is in column 1)
If Cells(i, 1) = Cells(i + 1, 1) Then
Else
Cells(i, 1).Copy
Cells(i, 5).PasteSpecial xlValues '(this pastes into column E)
End If
i = i + 1

Loop
Range("E5:E1000").Sort Key1:=Range("E5"), Order1:=xlAscending

Columns("E:E").Select
Selection.Sort Key1:=Range("E1"), Order1:=xlAscending
Range("A1").Select

End Sub




--
RyGuy


"ryguy7272" wrote:

Try this:

=IF(ROWS($1:1)<=COUNTA(A1:A20),INDEX(A1:A20,SMALL( IF(A1:A20<"",ROW(A1:A20)-MIN(ROW(A1:A20))+1),ROWS($1:1))),"")

This is a CSE function (Ctrl + Shift + Enter)



--
RyGuy


"raphiel2063" wrote:

Is there a function/formula I can put into a series of cells which can look
at a list (with repeat entries) and simply return the unique one.

E.g.

Tomato
Tomato
Banana
Tomato
Pear
Banana
Apple
Pear
Apple
Tomato

Would be displayed as

Tomato
Banana
Pear
Apple

Then I can use Validation on this 'filtered' selection to create a drop down
selection box.