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.

Say your original list is in Column A, starting in A1.

In B1 enter,
=A1
In B2, enter this *array* formula:
=IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0)),"",INDEX(IF(ISBLANK($A*$
1:$A$200),"",$A$1:$A$200),MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0)))


--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.


*After* the CSE entry, copy down until you get #N/A errors.
This tells you that you have exhausted the unique numbers in Column A.


I have sized the formula to A200.
You can revise that as necessary.


As you add additional values to Column A, those errors will change to
display additional uniques.

You can then remove the formulas, leaving just the data behind by ...
selecting the list in Column B,

Right click in the selection and choose "Copy",

Right click again, and choose "Paste Special",

Click on <Values>, then <OK>, then <Esc>.


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



"raphiel2063" <(E-Mail Removed)> wrote in message
news:5F758D0B-C0D7-4365-ADED-(E-Mail Removed)...
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.

Works a treat. Thanks very much.

"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.

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.

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.

You're welcome, and thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"raphiel2063" <(E-Mail Removed)> wrote in message
news:F1C735C9-2C0C-4C21-9B03-(E-Mail Removed)...
> Works a treat. Thanks very much.
>
> "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.