Unique Entries to be dispalyed in drop down list as per renge given in data sheet

[yogeshwarv]

Hello Sir,

I am stuck with a problem with the list function with validation properties. I have applied list command in cell "A2" but it is showing duplicate records also, I wish if the dropdown list menu could display the unique entries only. And in alphbetical order. Is it possible with the help of VBA, if yes Knidly help me out. An urgent reply will be extremely appreciated.

I have enclosed the sample file for better explanation.

Please Help!!!!!!!!!

[Amanda1]

Hi

You don't need VB to solve this. I can suggest two options, first create new list as List1 after copying original list to new column and the just delete each duplicate so you have a unique list for your dropdown. Second option is that you combine col A & col B so that you have item and size on one line as a single product, i.e. Crackfill 500g, Crackfill 1kg etc as one dropdown list.

Cheers

Amanda

[geekgirlau]

While you can easily delete the duplicates in your sample, where there's a lot of data you are better off using the Advanced Filter. Copy the Product Name column to a new column, sort it, then use Data | Filter | Advanced Filter | Unique records only (you will probably also want to copy this to a new location).

[yogeshwarv]

Thanks for your so fast reply Amanda,
ya the options you have suggested are good but don't you think that is a lengthy procedure. and we have sort them in alphbetical order everytime. of using the template.

I am agree this will work with the CONCATENATE function also, but can it be done with the help of VBA script and what about its alphabetical sequence of dropdown list.