+ Reply to Thread
Results 1 to 9 of 9

Remove Duplicates from Drop down selections

  1. #1
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    380

    Remove Duplicates from Drop down selections

    Good afternoon everyone

    I have a sheet using multiple drop down selections, drawing the data from named ranges.

    I am looking for a way to remove duplicate entries in the drop down selections so they only show one of each option, even though the data set it is drawing from must contain multiples of each option.

    See attached: Cells I2 is the initial data set, and J2 has selection criteria dependant on the value in I2.

    Both the I2 and J2 drop down boxes have multiples of the same entry, as it is not until K2 that a unique identifier is found.

    What i am looking for is the drop down in both I2 and J2 to only have one of each entry..

    Is there a way to do this via formula? or do i need to use a seperate reference list as my data validation? I am hoping not as this would mean constantly updating this seperate list as new entries are added, as opposed to it automagically (not a typo!!) updating.

    Thanks in advance

    Darren.
    Attached Files Attached Files
    Handy things to keep in mind:

    Click *, if my suggestion has helped you
    If your problem is solved, then please mark the thread as SOLVED

    Sharing is Caring .... spread the knowledge

  2. #2
    Valued Forum Contributor
    Join Date
    09-04-2012
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    463

    Re: Remove Duplicates from Drop down selections

    will you provide us the expected output in sheet 2 with the above file so that i can more understand your query....?

    Regards
    CA Mahaveer Somani

  3. #3
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Remove Duplicates from Drop down selections

    OOH---this is a good one!!
    Making the brain cells stretch a bit!
    I can get it with the sample data, but I can already see problems with the addresses, so this may take a while to get you a "good" answer...
    Last edited by dredwolf; 02-28-2013 at 05:01 AM.
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Remove Duplicates from Drop down selections

    Hm... not sure in which method you would like to get it done Using Worksheet Functions or using VBA code?

    If the answer is using worksheet function then confirm whether we can use Helper column(s)?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Remove Duplicates from Drop down selections

    Also...some Idea of the number of rows going to be used for the data...I'd like to provide a solution that meets your actual needs,... I'm working with an assumption of 500 rows of data at this time(trying to keep file size down) but if you are looking at 50,000 rows of actual data, that would be good to know

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Remove Duplicates from Drop down selections

    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Remove Duplicates from Drop down selections

    Try this :Remove duplicates from drop downs.so1.xlsx

    As noted before, it is set up for 500 rows of data, if you need more, you'll have to change all the range references of 501 to required number and extend the formulas in columns N-S down

    Hope this helps

  8. #8
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Remove Duplicates from Drop down selections

    Or this new, improved, regular formula (Although, technically, the SUMPRODUCT formula in T2 could be considered an array formula I guess...) version

    Note- you still have to make sure the formulas in Columns N-S are extended down far enough to get all your data accounted for, but that's easier than changing range references

    Hope this helps

    (Added note - I worked quite a bit on this one, as I have a similar project in mind, and needed to work out some detail, so THANK YOU ! )

    Edit-
    You can always hide Columns N-T if you don't want them visible, or insert columns till they go off screen
    Attached Files Attached Files
    Last edited by dredwolf; 03-01-2013 at 11:03 PM.

  9. #9
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2013 and Office 365 Plus
    Posts
    380

    Re: Remove Duplicates from Drop down selections

    Thanks so much for all of your help everyone ...

    A combination of the responses has got me there

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1