In this post, we’ll create a drop-down that contains a unique list of choices derived from a column that contains duplicate values. This may sound familiar as we previously accomplished this with a PivotTable. However, the Power Query feature that’s built-in to Excel 2016 makes this process easier.
We have a data table that contains RepID, Date, and Amount columns, 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.
Our solution should be fast and easy to maintain over time, even when new reps may appear in the data table. We don’t want to use VBA or to have to update any formulas going forward. Let’s see how Power Query can help.
We’ll accomplish our objective by performing the following steps:
- Create the query
- Create a name
- Create the drop-down
Well, let’s get to it.
Please note that the steps below are written with Excel 2016 for Windows. If you are using a different version of Excel, Power Query may not be available or you may need to download the free Power Query Add-In.
Create the query
First, we navigate to the worksheet that contains our data table, shown below.
Then, we select any cell within the table and click the following command:
- Data > From Table (in the Get & Transform group)
This launches the Query Editor dialog, as shown below.
The results we want to return should be the RepID column, without duplicates, sorted in ascending order. So, let’s begin by removing the other columns.
With the RepID column selected, we click the following command:
- Manage Columns > Remove Other Columns
The updated query is shown below.
Now, we need to remove duplicates, so, we select the following command:
- Reduce Rows > Remove Duplicates
The updated query is shown below.
Finally, we sort the list in ascending order by using the RepID drop-down. The updated query is shown below.
Last, we need to return the results to Excel, so we use the Close & Load command. Our unique RepID list now appears in a table in our workbook, as shown below.
We are mostly done at this point, and all that remains is to use this list of choices in our drop-down.
Create the name
We need to create a name that refers to the table that stores the query results. There are a couple of options for accomplishing this step. One option is to select all of the data cells in the table, and then enter the desired name into the Name Box (that little box just to the left of the formula bar).
Another option is to use the Name Manager. In order to use the Name Manager, we first need to make a note of the table’s name. To do this, select any cell in the table and then look at the Table Tools > Table Name field. This will show you the current name and allow you to give it a different name if desired. For example, Excel automatically assigned the name Table1_2 to the results table. We could change it if we wanted, but, assuming we don’t, our next step is to open the Name Manager by clicking the following icon:
- Formulas > Name Manager
In the Name Manager dialog, we click the New button to create a new name. We enter the desired name, such as RepList. In the Refers to field, we enter an equal sign and then the table’s name, such as =Table1_2. This is shown below.
We click OK and now that our new name is created, we can set up the drop-down.
Create the drop-down
We select the input cell that should contain our drop-down, and then use the following command:
- Data > Data Validation
In the Data Validation dialog, we want to Allow a List. The Source is equal to our name =RepList, as shown below.
When we click OK, Excel inserts the drop-down, and now we can pick from our list of choices.
Whenever we update the table with new transactions, all we need to do is right-click our results table and select Refresh. Power Query will rebuild the results table, and the values will automatically flow into the drop-down list.
If you have any other fun Power Query ideas, please share by posting a comment below…thanks!
- Sample file: UniqueDVwithPowerQuery
- Previous post to do this with a PivotTable: http://www.excel-university.com/unique-data-validation-drop-down-from-duplicate-table-data/