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!
=====================================================
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.
