PDA

View Full Version : Remove Duplicates in ComboBox



f2e4
05-12-2008, 03:11 AM
Hi Guys,

I have created a Combobox on on 'Sheet1' thats displays a drop down from 'Projects'

As the length of the list on 'Project list' will change over time, i have used the following function for the name range:


=OFFSET(Projects!$C$4,0,0,MATCH("*",Projects!$C:$C,-1),1)

The problem:

The data on 'Projects' has a list of every project and a name beside it. However, there is many names for each project resulting in a sheet with lots of duplicate projects.

When i open the drop down in the combobox, all of these duplicates are displayed.

Is there any way of removing the duplicates from the drop down list and displaying only 1 entry for each project?

Thanks in advance

F

Bob Phillips
05-12-2008, 03:13 AM
You will need to create another list of unique values and use that list.

f2e4
05-12-2008, 03:31 AM
Hi xld

I have been searching around and have noticed that some people have been recommending VBA code for this but could not get any of their code to work.

Would you recommend your option or code as the most efficient way of keeping the list uptodate everytime the project list is edited?

If it is your option, i don't suppose you could briefly explain how to create the unique list?

Thanks again,

F

Aussiebear
05-12-2008, 04:55 AM
Hi f2e4,

To create a unique list from an existing data range, try the following:

1. Select the range of non unique data you wish to amend.
2. Go to DATA>FILTER>ADVANCED FILTER
3. Ensure that the "Action" is set to "Copy to another location" option.
4. "List Range" is the existing non unique data range you selected earlier, & the Criteria Range is blank.
5. "Copy To" is the top cell of a new column or range where the unique list is to be stored.
6. Check the "unique record only" option and click "OKAY".

mdmackillop
05-12-2008, 05:15 AM
KB Item (http://www.vbaexpress.com/kb/getarticle.php?kb_id=824)