![]() ![]() There are a couple of options for accomplishing this step. We need to create a name that refers to the table that stores the query results. We are mostly done at this point, and all that remains is to use this list of choices in our drop-down. ![]() Our unique RepID list now appears in a table in our workbook, as shown below. Last, we need to return the results to Excel, so we use the Close & Load command. Now, we need to remove duplicates, so, we select the following command:įinally, we sort the list in ascending order by using the RepID drop-down. With the RepID column selected, we click the following command: So, let’s begin by removing the other columns. The results we want to return should be the RepID column, without duplicates, sorted in ascending order. This launches the Query Editor dialog, as shown below. Data > From Table (in the Get & Transform group).Then, we select any cell within the table and click the following command: 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.įirst, we navigate to the worksheet that contains our data table, shown below. Please note that the steps below are written with Excel 2016 for Windows. We’ll accomplish our objective by performing the following steps: We don’t want to use VBA or to have to update any formulas going forward. Let’s see how Power Query can help. Our solution should be fast and easy to maintain over time, even when new reps may appear in the data table. ![]() We want the drop-down list to contain a unique list of reps from the table. On another sheet, we want the user to be able to select a rep from a drop-down. We have a data table that contains RepID, Date, and Amount columns, as shown below. However, the Power Query feature that’s built-in to Excel 2016 makes this process easier. This may sound familiar as we previously accomplished this with a PivotTable. In this post, we’ll create a drop-down that contains a unique list of choices derived from a column that contains duplicate values. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |