Data>Validation>List - Unique Entries

Hello! Friends

I have a list of names containing duplicate names and empty cells.

Using Data > Validation > List

I want get a drop down list of unique entries having no empty cells.

How can do this??? Please help me...

Hi Rasheed

Use advanced filter to create a unique list and use that list in Data > Validation > List

http://www.contextures.com/xladvfilter01.html#FilterUR


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rasheed Ahmed" <Rasheed Ahmed@discussions.microsoft.com> wrote in message
news:1B410BCA-D2DC-4A53-80B8-64D15ACF1E20@microsoft.com...
> Hello! Friends
>
> I have a list of names containing duplicate names and empty cells.
>
> Using Data > Validation > List
>
> I want get a drop down list of unique entries having no empty cells.
>
> How can do this??? Please help me...

"Rasheed Ahmed" wrote:
> I have a list of names containing duplicate names and empty cells.
> Using Data > Validation > List
> I want get a drop down list of unique entries having no empty cells.

Another option to play with could go something like this ..

Assuming names are listed in sheet: X,
from A2 down to a max expected A2000 (say)

Put in B2:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"",ROW()))

Put in C2:
=IF(ROW(A1)>COUNT(B:B),"",INDEX(A:A,MATCH(SMALL(B:B,ROW(A1)),B:B,0)))

Select B2:C2, copy down to C2000 to cover the max extent in col A
(Leave B1:C1 empty)

Then click Insert > Name > Define and input:
Names in workbook: Names
Refers to:
=OFFSET(X!$C$2,,,SUMPRODUCT(--(X!$C$2:$C$2000<>"")))
Click OK

We can now create DVs in any sheet via Data > Validation, Allow: List,
Source: =Names, and the DVs will yield the required results, ie dropdowns of
only the unique names from col A in X
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---