Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
DataValidationList - 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... |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
DataValidationList - Unique Entries
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 wrote in message ... 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... |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
DataValidationList - Unique Entries
"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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"COUNTU" function in Excel to count unique entries in a range | Excel Worksheet Functions | |||
Count Unique Entries | Excel Worksheet Functions | |||
Count unique entries | Excel Discussion (Misc queries) | |||
Best way to get a list of unique entries in a field | Excel Worksheet Functions | |||
Counting unique entries in column A but only if specific values appear in columns B and C | Excel Worksheet Functions |