Excel Data validation list

[alliswell]

Hi everybody
I have lots of names in column A i want them to convert them in data validation List to pick names like drop down list

 

[mamady]

Hi Dear,

There are many ways, and some are optimum to your requirement. I suggest the below basic method

If your data points (names) under column A are already unique in value (no duplication), you can simply:

1. select the cell where you want the drop list
2. select Data tab >> Data Validation >> Allow: List and type the following formula in the Source field (adjust range if required) =A1:A100
3. click ok

However, if your data points (names) under column A are not unique in value (have duplications), you should follow the below steps:

1. select empty column (assuming B), type in the following formula (adjust range if required) and drag down till cells become blank =IFERROR(INDEX($A$1:$A$100,MATCH(0,INDEX(COUNTIF($B$1:B1,$A$1:$A$100),),0)),"")
2. select the cell where you want the drop list
3. select Data tab >> Data Validation >> Allow: List and type the following formula in the Source field (adjust range if required) =B2:B100
4. click ok

Best Regards
M. Yusuf