Share via

Excel - preventing duplicate entries from drop down list

Anonymous
2021-05-07T08:54:28+00:00

I have created a drop down list ( 150 number values )  for a range of cells in one column ( column A ). Now I would like to make sure that I will not duplicate the entries from the drop down list in any of the cells in this column - in example: if I choose from drop down list number 55 in A3 cell then I should not be able to enter the same number (55) to any of the cells in column A. How do I accomplish this task.
The data validation source field does not allow me to enter the criteria for having the drop down list and formula preventing duplicates.

I am using Excel 2013 and Excel online.

I am beginner.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Lz365 38,191 Reputation points Volunteer Moderator
    2021-05-11T11:05:24+00:00

    Hi @DominikDom

    In my first reply, in the linked workbook, a sheet was hidden, it's now visible in this version. If you look at the formulas in column [Items] of the table that sits in Sheet2, nowhere I used the FILTER function (and I used another column - [Available] - that you deleted in the pictures I see above)

    I later talked about FILTER option just for the record and talking to Jeovany CV (only). You said your environment is a mix of Excel 2013 and Excel Online. The FILTER option can be used with Excel online but not with Excel 2013, hence why I took an approach that'll work in both case

    And be aware that IF the result of a FILTER returns more than 1 element, the formula can't not be put in a Table (=> #SPILL). Same goes for all other dynamic array functions

    To make it work in your conditions, 3 simple things to do:

    • Resize the table in Sheet2 so it has 150 rows (= your 150 number values)
    • Fill down column [ID] so it has unique numbers (in order) from 1 to 150
    • Enter your 150 unique values in column [Num]

    That's it and that must work according to your initial problem description. Hope this makes sense & helps.

    Any question let me know

    Nice day...

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments

10 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lz365 38,191 Reputation points Volunteer Moderator
    2021-05-08T10:24:23+00:00

    Hi @DominikDom

    Play with this sample and confirm this does what you expect

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2021-05-08T12:42:57+00:00

    @Lz

    Brilliant!!,   5 stars

    Was this answer helpful?

    0 comments
  3. Anonymous
    2021-05-08T08:32:59+00:00

    Thank you for the advice - I will learn more stuff from it for sure. This resolves only half of the problem I have.

    Here is more details:

    My drop down list contains only unique data - there is no duplicates in drop down list data.

    If I create criterion for "anti-duplicate" in "Data Validation" window then I am not able to create the drop down list with my data within. I wont to make a drop down list with choice of say: 150 predefined numbers ( or any unique type of data). This drop down list must be associated with a range of cells ( about 500 cells) in my column A . If I choose any cell in this range to fill it with my data then I can use the drop down list and enter the data from it or type some data manually . I am able in one case to create the drop down list with my predefined 150 different numbers ( for example from 1 to 150 ), then when I click on the cell , small box appears on right of the cell. This box is my access to drop down list. By clicking it the drop down list pops "down" and gives me a choice of the predefined number in it. In this scenario I would like to prevent entries of the same number ( from drop down list or even typed manually ) to any of the other cells in my cells range in column A . I don't know how "anti-duplicate" formula can be added to work together with drop down list. I can successfully set up only one formula in Data Validation window.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2021-05-07T09:24:10+00:00

    Sure, there are several techniques you can use to remove duplicates from drop down lists. Some depend on 365 features that are not available in 2013. 

    @ add drop down list to validate data               2019 09 27
    https://chandoo.org/wp/excel-add-drop-down-list/
    https://youtu.be/4Rv5twNqrGM (9min)
    Validating your data as you type can prevent any surprises when you are doing analysis / follow-up on the data. Thankfully, excel has the right tools to do it. Excel drop down list can assist you in picking up a value from a valid list to enter in a cell. Here is a short how-to guide to get you started on data validation in excel.
    .  *  set up Drop Down list in Excel
    .  *  ignore duplicates while setting up validation list
    .  *  Using Dynamic Arrays (365)
    .  *  Using Pivot Tables for drop down without duplicates
    .  *  Drop-down list without duplicates – Video
    .  *  Best Practice for Drop-downs
    .

    Create a drop-down list from a table without duplicate in Excel? ****https://www.extendoffice.com/documents/excel/5231-drop-down-list-without-duplicates.html
    Supposing you want to create a drop- down list based on a list of a table contains some duplicate values, how can you exclude these duplicates in the drop-down list in Excel？Actually, there are three methods can help you.
    .  *  Create drop down list without duplicates by using PivotTable
    .  *  Create drop down list without duplicates by using Remove Duplicates
    .

    Unique Data Validation Drop-Down From Duplicate Table Data           2014 09 10
    https://www.excel-university.com/unique-data-validation-drop-down-from-duplicate-table-data/
    In this post, we’ll explore a method for generating a drop-down that contains a unique list of choices derived from a table column with duplicate values.
    .

    Searchable Drop Down List in Excel (using PivotTable)             2020 04 09    Mynda Treacy
    https://www.myonlinetraininghub.com/searchable-drop-down-list-in-excel
    Creating this searchable drop down list in Excel is so easy I’m wondering why I never thought of it before. It doesn’t require any formulas or VBA and it has all the bells and whistles you’d expect from a searchable drop down list, including automatic sorting, ignoring duplicates and an option to select ‘All’:
    .

    Was this answer helpful?

    0 comments