Distinct Values in Drop-Down List in Google Sheets

Are you tired of dealing with duplicate selections in your drop-down lists in Google Sheets? Don’t worry, I’ve got you covered! In this tutorial, I will explain how you can easily obtain distinct values in a drop-down list in Google Sheets, ensuring that each selection is unique and avoiding any duplicates.

Removing Used Items from Data Validation Drop-Down Lists in Google Sheets

Let’s start by taking a look at the screenshot below. In this example, column A contains drop-down menus that populate values from Column F. As you can see, I’ve already selected a few items in the drop-down lists in rows A1 to A8.

When I click the drop-down menu in cell A9, it only shows the values that are left to select. This avoids duplicate selections and ensures that each item can only be chosen once. Pretty neat, right?

So how do we achieve this distinct values in a drop-down list in Google Sheets? Let’s dive into the details.

How to Get Distinct Values in Drop-Down List in Google Sheets

Although column F contains the data to create the drop-down list, we won’t be using it directly. Instead, we will use a helper column, which in this case is column C.

Column C automatically populates distinct values based on your menu selections in column A. Take a look at the following image. As you can see, I’ve deleted all the drop-down selections in column A. Now the values in the helper column C and column F are the same.

When you select any fruit name in column A, that fruit’s name is removed from column C. This way, we can use column C, not column F, to create the drop-down list. The helper column helps us remove the used items and obtain distinct values in the drop-down.

Now let’s go through the steps to create the helper column C and get distinct values in the drop-down list in Google Sheets.

Generate Distinct Values in Google Sheets

The secret lies in the helper column C. I’ve applied a formula to generate distinct values in this column.

In Google Sheets, the simplest way to get distinct values from a list is by using the Query function. Here are the steps:

1. Pick a column (column F, for example) and enter the values (fruit names in this case) for your drop-down menu.
2. Choose a helper column (I’ve chosen column C) where distinct values will be populated automatically.
3. In cell C1, enter the following formula to generate distinct values:

=query( query( {F1:F;A1:A}, "Select Col1, count(Col1) where Col1<>'' group by Col1" ), "Select Col1 where Col2=1" )

Alternatively, you can use the following formula instead:

=ArrayFormula( if(countif(A1:A,F1:F)=0,F1:F, ) )

You can also use the Filter function to populate distinct values in cell C1:

=filter(F1:F,NOT(regexmatch(F1:F,"^"&textjoin("$|^",1,A:A)&"$")))

Make sure to adjust the cell references in the formulas according to your data.

That’s it! You have successfully generated distinct values in the helper column C.

Use Data Validation to Create Unique / Distinct / Drop-Down Lists

Now that we have generated distinct values in column C, let’s connect them to the drop-downs in column A using Data Validation. This will ensure that the drop-down lists only contain unique values.

Here are the steps:

1. Go to cell A1 and navigate to the menu Data > Data Validation. This will open the Data Validation settings window.
2. Set the data validation settings as shown in the image below. Pay attention to the cell references used. Then click on the “Save” button.
3. Copy the cell A1 drop-down to cell A2, A3, A4, and so on, depending on your data range.

Congratulations! You have successfully generated a drop-down menu that only contains distinct values.

Removing Used Items from Drop-Down That Share the Same List From Range

As you can see in the screenshot below, the active drop-down list is in cell A14. I’ve already selected different fruits from the data validation drop-down lists in the range A1:A13.

In cell A14, the drop-down list only shows the available fruits to select. This ensures that a user can only choose unique and distinct values from the drop-down. No more duplicate selections!

You might notice a warning in each cell where you’ve made a selection. Don’t worry about it. It says “Input must fall within specified range” when you hover your mouse over it. You can simply ignore this warning.

And there you have it! With these steps, you can easily obtain distinct values in drop-down lists in Google Sheets.

Remember, if you want to learn more about generating distinct lists in Google Sheets, check out my tutorial on How to Find Distinct Rows in Google Sheets Using Query.

Enjoy exploring the amazing possibilities of Google Sheets!

Example Sheet: [81121]

Resources:

• Data Validation – How Not to Allow Duplicates in Google Sheets
• The Best Data Validation Examples in Google Sheets
• Proper Way to Use Currency Formatting in Data Validation Drop-Down in Google Sheets
• A Drop-down Menu in Google Sheets to View Content from Any Sheets
• Google Sheets: How to Get an All Selection Option in a Drop-down
• Populate a Full Month’s Dates Based on a Drop-down in Google Sheets
• Create a Drop-Down to Filter Data From Rows and Columns
• Create a Drop-Down Menu From Multiple Ranges in Google Sheets
• How to Combine Multiple Sheets in Importrange and Control Via Drop-Down
• Multi-Row Dynamic Dependent Drop Down List in Google Sheets
• Auto-Populate Information Based on Dropdown Selection in Google Sheets
• Reject a List of Items in Data Validation in Google Sheets