Thread: drop down list - duplicates

I am trying to make a drop down list from a table that was formed from an SQL database. The column I want to make the drop down list from has duplicate names in them.

I made a validation list with that column, but it has the duplicate names in it. Is there any way to get rid of the duplicates??

thanks

Originally Posted by enderokc

I am trying to make a drop down list from a table that was formed from an SQL database. The column I want to make the drop down list from has duplicate names in them.

I made a validation list with that column, but it has the duplicate names in it. Is there any way to get rid of the duplicates??

thanks

suppose lists is in the range of A1:A50
put in B1
=IF(COUNTIF($A$1:$A$50,B12)>1,"",1) and copy down to B50
now select B1:B50 copy it and paste it as values (right click after coping, select paste special and select values and press ok)
keeping selected B1:B50 press F5 to open Go To window, click on Special tab and select Blanks, this will select all blank cells within range.
right click on any selected cell, click Delete... and select entire row.
press ok

it will delete all rows which contain duplicate data.

Regards.

would it be possible to do it dynamicly?? so lets say I add a new person into the database and I open the spreadsheet, that person would be already loaded into the list??

Originally Posted by enderokc

would it be possible to do it dynamicly?? so lets say I add a new person into the database and I open the spreadsheet, that person would be already loaded into the list??

if you have entered a Allow list in Data validation as B1:B50 and you need to enter more names later on then extend this list in Validation to say B1:B70. so when you will enter in B51 a name it will automatically be added in validation list but you need to uncheck Ignore blanks in Validation window.

Regards.