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... |
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... |
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 --- |
All times are GMT +1. The time now is 10:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com