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.
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.
On another sheet, we want the user to be able to select a rep from a drop-down.
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/
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.
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.
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.
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.
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:
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.
The resulting name, dd_reps, contains a list of the PivotTable results, even if the number of reps changes over time.
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.
The resulting drop-down contains a list of choices from the PivotTable, as shown below.
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!