Create a new list of values without duplicates

In B1 add =A1

In B2, add

=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)))

which is an array formula, so commit with Ctrl-Shift-Enter.

Copy B2 down, and adjust the ranges to suit. Then use the new range in
Debra's technique.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"chelle" wrote in message
...
I was just looking here and this is a nice tutorial on how to do the
datavalidation.
http://www.contextures.com/xlDataVal11.html

My problem is that some values I have more than once.

I have noticed from he http://www.contextures.com/xlDataVal07.html
that you can use a COUNTIF to remove duplicates. This is great but it
doesnt seem to have the "in cell dropdown" option if you use custom
instead
of list.

Does anyone know an easy way to do this? I have been trying to figure it
out for weeks now!

I would even consider writing a script in VB to check for duplicates but I
would need some sort of 'walk through' in how to tie it to my validation
box.

Thank you for your help!