Data>Validation>List - Unique Entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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.
Click to expand...

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

---
 
Back
Top