Removing Duplicates from a dependent drop-down list

Posted on 2007-08-02
Last Modified: 2013-11-25
Does anyone know if it's possible to remove duplicates from a name list?  My data is set up very much like it is set up on this contextures spreadsheet.

If you go into that spreadsheet and click in the drop-down list for Prod where the selection is currently "Large Cap Equity", you'll see that there are duplicates.  

My data that I'm making duplicate lists for is set up just like this spreadsheet.  All my data is in a table form like this contextures example.  I would like to use the code that is in this spreadsheet for my spreadsheet but I need to get rid of the duplicates.  Is there away to code that Validation Source reference so that it removes duplicates?  The code for that particular cell is:

Question by:tkeiffer
    LVL 23

    Accepted Solution

    Hi tkeiffer,

    I'm afraid the answer is simply "no".

    You cannot use the formula to only get unique references, you actually need to remove them from the original data. There are several ways, from manual to coded to addins, which will enable you to do this, plenty of which can be found by searching EE for "get unique values in excel".

    LVL 23

    Expert Comment

    Thanks for the grade! :)

    Expert Comment

    I am currently working on this problem.  I followed a video on line and it worked on his spreadsheet but when I tried to duplicate it, it didn't work for me.  I have attached both his video and my spreadsheet.  If anyone has any idea why this is not working for me please let me know, I've already spent like 2 hours on it.

    Website: Duplicate-Spreadsheet.xlsx

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now