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.

Objective

Before we get started on the mechanics, let’s take a look at what we are trying to achieve.

We have a data table that is exported from our accounting system. It contains the rep, the transaction date, and the amount, as shown below.

20140910a

On another sheet, we want the user to be able to select a rep from a drop-down.

20140910b

We want the drop-down list to contain a unique list of reps from the table.

The goals for our solution are that it is fast and easy to maintain over time, even when new reps may appear in the data table, that we don’t use VBA macros, that we can move the workbook to other folder locations in future periods, and that we don’t need to update any formulas in future periods.

Ready? Me too…let’s do this thing.

NOTE: An alternative to the technique presented below is to use Power Query instead of a PivotTable as discussed in this post: https://www.excel-university.com/use-power-query-to-create-a-drop-down-list-without-duplicates/

Technique

This technique involves creating a unique list of reps in a worksheet with a PivotTable. The PivotTable is an intermediate step that creates the rep list that we can use to feed the data validation drop-down list. In a way, the data flow for this technique can be visualized as follows:

  • Table > PivotTable > Named Reference > Data Validation

Let’s take these steps one at a time.

Table

First, we store the source data in a Table. This is easily accomplished by selecting the ordinary range and then the Insert > Table Ribbon icon.

PivotTable

With the table in place, it is time to create the unique list of choices. We select any cell in the table and then the Insert > PivotTable icon. We build a simple PivotTable on a new sheet by placing the field that contains the drop-down choices into the Rows area and removing the field headers and grand totals, as shown below.

20140910-a

Any time you change the underlying table data, such as adding new rows in future periods, you’ll need to update the rep list by clicking the Refresh button (PivotTable Tools > Refresh, or, right-click and Refresh). If you want the list to be sorted as it is refreshed, you can update the sort order of the PivotTable by right-clicking a rep cell and selecting the desired Sort option. If you delete data from the table, and the deleted reps still appear in the PivotTable, head into the PivotTable Options dialog and change the Number of items to retain per field to None.

Now that we have generated a unique list of reps, it is time to put them into a named range that can be used with the data validation feature.

Named Reference

Now, we’ll set up a named range that can be used by the data validation feature to create the drop-down. There are many ways to create a dynamic named range to reference the PivotTable results, and I’ll provide one such solution. If you have any others, please share by posting a comment below.

The named reference needs to dynamically adjust as the number of PivotTable items change. We’ll set up a new name, dd_reps, that uses the following formula:

=Lists!$C$9:INDEX(Lists!$C:$C,MATCH("*",Lists!$C:$C,-1),1)

In summary, this formula creates a range reference that begins with cell C9 on the Lists worksheet (the first cell in the PivotTable) and continues through the cell returned by the INDEX function. The INDEX function dynamically returns the last data cell in column C by using the MATCH function.

To set up the new name, click the New name button in the Name Manager (Formulas > Name Manager) and then enter the name information as shown below.

20140910c

The resulting name, dd_reps, contains a list of the PivotTable results, even if the number of reps changes over time.

Data Validation

Now, we are home free. All that remains is to set up the drop-down box with the data validation feature (Data > Data Validation).

We simply Allow a List equal to the name, dd_reps, as shown below.

20140910d

The resulting drop-down contains a list of choices from the PivotTable, as shown below.

20140910-c

Once the workbook is set up, as new data is added to the table each period, you just need to Refresh the PivotTable and any new reps will flow through the PivotTable into the drop-down.

Since this is Excel, there are many ways to accomplish any task, so the method presented above is only one such solution. If you prefer a different solution, we’d love to hear it…please post a comment below and share!

Additional Resources

Get a quick email notice when a new Excel article is available

  • This field is for validation purposes and should be left unchanged.

This article was written by Jeff Lenning

20 comments:

  1. VENKAT
    Reply

    for validation i used the formula as list

    =$C$9:OFFSET($C$9,COUNTA($C$9:$C$100),0,1,1)

    I agree I use C100 which means max 100 rows from C9

    1. jefflenning Post author
      Reply

      Venkat,
      Thanks for sharing this named reference alternative, which uses the OFFSET/COUNTA combination instead of the INDEX/MATCH combination…thanks!
      Thanks
      Jeff

      1. KS
        Reply

        I am getting an error when i paste above formula in data validation

        Error:

        you may not use reference formula or array

  2. sean
    Reply

    Very nice! Thanks!

  3. Gary
    Reply

    Jeff
    Your solution was my first thought but with the users intended to operate this, they couldn’t be relied upon to update the Pivot.
    Thanks anyway.
    Gary

    1. jefflenning Post author
      Reply

      Gary,
      Ah…I see…if you figure out an approach that works for your workbook, please share by posting a comment…thanks!
      Thanks
      Jeff

  4. Felipe
    Reply

    Found out a better solution so you don’t have to create a pivot table (if you can include new columns in your database – you can hide everything later):
    It will require 3 more columns
    Assuming RepID in cell A1; Date cell B1 and Amount cell C1:

    1) In column D you will have to exclude duplicated values using IF+IFERROR+VLOOKUP as below:
    =IF(A2=IFERROR(VLOOKUP(A2,$A$1:$A1,1,0),””),””,A2)
    *** Please note the relative and absolute references carefully
    Apply this formula for the whole table

    2) In column E you will sum only the values that are shown in column D:
    =IF(D2=””,E1,E1+1)
    *** In this case the column header must to be empty or equal to zero
    Apply this formula for the whole table

    3) In column F you will exclude the duplicated numbers:
    =IF(E2=E1,””,E2)
    Apply this formula for the whole table

    4) In another column or sheet (I’ve included in column H of the same sheet):
    Include sequential numbers starting by 1 (1, 2, 3, and so on in each line)
    You can include as many numbers as you want

    5) In column I (in my case) you will lookup the sequential numbers in your table:
    =IFERROR(INDEX($D$2:$D$28,MATCH($H2,$F$2:$F$28,0)),””)
    Apply this formula for the whole sequence of numbers you created in column H

    6) Edit the dd_reps using the new reference:
    =Sheet1!G2:INDEX(Sheet1!G:G,MATCH(“*”,Sheet1!G:G,-1),1)

    7) Include the validation list with this new sequence

    Felipe Maier

    1. jefflenning Post author
      Reply

      Felipe – Thanks for sharing your alternative!
      Thanks,
      Jeff

  5. Brian
    Reply

    I recently did something very similar to Filipe. My slight variation was that if it was the first occurrence (using a similar “if error” reference) and then returned the row(). This gave each first occurrence a unique identifying number. I then used rank() to turn that list of unique but spread numbers into a 1, 2, 3, 4, 5…. On a separate page I had a column of 1, 2, 3, 4, 5, 6, 7 and used it to look up the the row. I think they ways listed here are a bit more elegant, but it is another solution, particularly if you are going to want to make any other references to the list.

    I hope this is something excel adds natively in the future. I was recently using Google Sheets and realized that it automatically turns a validation from a duplicated list into an unduplicated drop down. I have to assume an unduplicated list is the goal more often than not.

    1. jefflenning Post author
      Reply

      Thanks!

  6. Thanks
    Reply

    Thanks for this technique, it was the best one in my search

    1. Jeff Lenning Post author
      Reply

      Welcome 🙂

  7. Robin Rascon
    Reply

    Hello – I’m digging this method; however, the named range does not appear to be dynamic as expected. It’s only looking at the first cell of the pivot and doesn’t expand when refreshed. My pivot starts at B4 with the Header at B3. Thoughts? Wondering if this method will not work in Excel 2013?
    =’Weekly Details’!$B$4:INDEX(‘Weekly Details’!$B:$B,MATCH(“*”,’Weekly Details’!$B:$B,-1),1)

    1. Jeff Lenning Post author
      Reply

      The MATCH * method works on text columns. If you are referencing numeric values instead, you’d want to update it to something like this instead:
      MATCH(MIN(B:B)-1,…
      Hope this helps!
      Thanks
      Jeff

      1. Robin Rascon
        Reply

        Excellent, I will give it a go! That must be it, because it did work on one that is a text column.

        1. Jeff Lenning Post author
          Reply

          Great 🙂

  8. Robin Rascon
    Reply

    Well, I couldn’t get that to work, so I created another column and formatted the date using TEXT(Week, “Mmm dd yy”) Seems to work ok now. = )

    1. Jeff Lenning Post author
      Reply

      Nice alternative…glad you got it going!

  9. craig
    Reply

    Hi Jeff,
    i was thinking about using a pivot table to create a unique list.
    Hopefully this is not too long winded but i wasn’t sure how to explain it properly otherwise (so sorry in advance).

    I have never coded before recently but have just started and was looking for a guide on how to create dependent data lists with a set up like mine below, eg.,

    Expenses Bills Insurance House Contents
    Expenses Bills Insurance House Building
    Expenses Bills Insurance Car Mine
    Expenses Bills Insurance Car Wife’s
    Income Mine
    Income Wife’s
    Income Other
    Expenses Shopping General
    Expenses Shopping Unecessary

    etc.
    I want it set up this way so i can add or remove categories without having to create a new validation list for each new category and making sure i add it to all the right columns (my categories go 5 deep so 1 main and 4 dependent).

    I could not find anyone with a set up like mine until i came across the following link http://quadexcel.com/create-dependent-drop-down-lists-containing-unique-distinct-values-in-excel/.

    It took me about 4 hours (i know next to nothing about formulas – and forgetting to press ctrl+shift+enter half the time and then not properly checking all my cell references…) to apply the formulas & modify for the extra columns i needed to apply them to. I was so happy when i was done but i found that it only worked as long as i did the selection from one cell (for each of the columns required) – i couldn’t have a validation list going down so i couldn’t use it to add data at the end of each column.

    I then had to use a macro to add from the second row down to copy it down to the bottom row of the data table. I got this to work 🙂 but i really wanted it to work from a userform, so with more research and help from this link, http://www.contextures.com/exceluserformdependentcomboboxes.html i found i could write the selection back to the spreadsheet for the array formulas to work.

    Sorry for the long winded comment. Here is my final formulas
    Main list
    =IFERROR(INDEX(Type_All,MATCH(0,COUNTIF($J$6:J6,Type_All),0)),””) + ctrl + shift + enter
    Second list
    =IFERROR(INDEX(SubCat1_All,MATCH(0,COUNTIF($K$6:K6,SubCat1_All)+(Type_All$K$1),0)),””) + ctrl + shift + enter
    Third list
    =IFERROR(INDEX(SubCat2_All,MATCH(0,COUNTIF($L$6:L6,SubCat2_All)+(Type_All$K$1)+(SubCat1_All$L$1),0)),””) + ctrl + shift + enter
    Fourth & Fifth list i just added the extra to the end
    The site i got the array formula from was only two lists deep, so with my next to no coding experience i was happy (though not about the four hours) to adjust it to my own lists.

    from the user form i have the following code,
    Private Sub input6_Change()

    Dim ws As Worksheet
    Set ws = Sheet2

    ws.Range(“k1”).Value = Me.Input6.Value

    End Sub

    i have done that for each of the combo boxes i have for the independent list – i have called them input6,7,8 etc so i can loop through the adding of data instead of adding each one line by line (i got this tip from http://www.onlinepclearning.com – an efficient coder, trevor easton). Please note the range reference is referenced in the relevant array formula so the lists update automatically and then show in the next combo box.

    I don’t know if that might be helpful to anyone else and it is a long winded way to go about it but now i have it set up i can just add my rows of categories and sort them (probably don’t need to sort them but i like them to show in alphabetical order in the drop down lists).

    There’s only two other things i would like to do if possible and that if is,
    1. remove the zero if from the last columns if they have no categories in them, i.e., if the lists for a particular category are only 3 deep, then the last two drop downs will show a zero in them.
    2. i would like to only have the items show in the drop down list and not a whole lot of blank lines below the last unique entry (because my range on the unique list is not dynamic it is a set range), maybe i can play with turning each unique list into a table…

    but i find that now my excel hangs and freezes and i can’t get the code to add to the bottom of the data table to work very well, it will work a couple of times and then if i play around with code for anything else not related, it will throw an error with the following line,
    Set aNewRow = rng.ListObject.ListRows.Add(AlwaysInsert:=True)

    the whole code for adding data is
    Private Sub CmdBtn3_Click() ‘Add button

    Dim ws As Worksheet
    Set ws = Sheet1
    Dim inps As Long
    Dim rng As Range
    Set rng = Sheet1.Range(“ExpensesIncome”)
    Dim aNewRow As ListRow
    Set aNewRow = rng.ListObject.ListRows.Add(AlwaysInsert:=True)

    For inps = 1 To 12
    With ws
    aNewRow.Range.Cells(1, inps).Value = Me.Controls(“Input” & inps).Value
    End With
    Next

    End Sub

    So maybe i have broken my excel with my fancy dependent drop down user form set up 🙁 oh well, it might take me weeks to sort that one out 🙂

    Thanks for your time and hopefully it is of help to someone, if you would like more info or better explanation you can email me direct

    Cheers
    Craig

  10. Qudsia
    Reply

    Works perfectly, thanks!
    Can you please explain how the Match portion of the formula is working? How does it give the last cell in the list for the named range?

Leave a Reply

Your email address will not be published. Required fields are marked *

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.