Excel tutorials

Two conditions - Three columns - How to create three dynamic Validation list with unique data that does not contain blank cells

I wrote about 'Validation list' or drop-down menu in the tutorials at the end of this article. In this tutorial article, I will show how we can create three drop-down lists, two of which are dependent on the previous drop-down menus. The purpose of this tutorial is to select the final result in the three dynamic drop-down lists, which depends on the two data selected in the previous two cells, but so that we only have unique data in the list and without blank cells. Notice the situation in the picture below.

Instructions for creating dynamic dependent drop-down lists


In the image above, notice the database in the 'A:C' columns. We want to create three drop-down menus (three Validation lists)

In an 'E2' cell, we want to create the first drop-down list that will contain distinct unique data from the 'A' column but so that it does not display any empty cells in the drop-down list.

In the 'F2' cell we want to display a second drop-down menu that will depend on the data we have selected in the 'E2' cell. In the drop-down list, we want to display the distinct unique data/values contained in the 'B' column and match the condition from the 'E2' cell. We also don't want to show empty cells in the list (we want this list to be dynamic and changeable, depending on the number of items in the list)

In the 'G2' cell, we want to create another dependent drop-down list that will show data from the 'C' column that depends on the condition found in the 'F2' cell and also withouit blank cells in the list (we want this the list will be dynamic and changeable, depending on the number of items in the list).

Creating a dynamic list with distinct unique values for multiple conditions

In order to streamline their work we will create auxiliary columns (helper columns) from which we will pull results. Note the extra columns created in the image below.

  • In the 'J' column, we'll set a formula that will return all the distinct unique data from the 'A' column as a result. We have no conditions here.
  • In the 'K' column, we'll set a formula that will return all the distinct unique data from the 'B' column as a result if the condition in the 'E2' cell matches. So we have one condition.
  • In the 'L' column, we'll set a formula that will return all the distinct unique data from the 'C' column as a result if the conditions in 'F2' and 'E2' cells match. So we have two conditions.

Create auxiliary columns for dynamic drop-down menus


The first formula is in the 'J2' cell and it looks like this (copy it to the last row). This formula returns all distinct unique data from the 'A' column.
=IF(IFERROR(INDEX($A$2:$A$100;MATCH(0;INDEX(COUNTIF($J$1:J1;$A$2:$A$100););0));"")=0;"";IFERROR(INDEX($A$2:$A$100;MATCH(0;INDEX(COUNTIF($J$1:J1;$A$2:$A$100););0));""))

The second ARRAY formula in the 'K2' cell is as follows (copy it to the last row). This formula returns all distinct unique data from the 'B' column in the same Row that the condition in the 'E2' cell is located.
=IFERROR(INDEX(B$2:B$100;MATCH(0;IF(E$2=A$2:A$100;COUNTIF(K$1:K1;B$2:B$100);"");0));"")

The third ARRAY formula in the 'L2' cell is as follows (copy it to the last row). This formula returns all distinct unique data from the 'C' column in the same Row as the conditions from 'E2' and 'F2' cells.
=IFERROR(INDEX($C$2:$C$100;MATCH(0;COUNTIF(L1:$L$1;$C$2:$C$100)+IF($B$2:$B$100<>$F$2;1;0)+IF($A$2:$A$100<>$E$2;1;0);0));"")

Setting the formula for the Data Validation list and drop-down menus

The first drop-down menu
In the picture below you can see what the first drop-down menu looks like, which we set via the 'Validation List'. Notice that there are no blank items in the drop down box.

A drop-down list that does not show empty cells in Excel


In the 'E2' cell we set the formula for the first drop down menu. Select the cell and then click on 'Data Validation'. In the 'Source' field, set the formula:
=OFFSET($J$2;;;COUNTIF($J$2:$J$17;"?*"))

Creating a Drop Down menu via Data Validation

 

Second dependent drop-down menu (one condition)

In the picture below, you can see what the other dependent drop-down menu looks like, which we set trough the 'Validation List'. The items in this list from the 'B' column belong to the condition from the 'E2' cell found in the 'A' column. So, in the 'A' column for each cell in which the 'Truck' condition is located, in the same row in the 'B' column there is information: 'John', 'Mike' and 'Leo'. Also note that there are no blank items in the menu.

Dependent drop-down list with no empty cells if one condition matches

 

In the 'F2' cell, we set the formula for the second drop-down menu. Select the cell and then click on 'Data Validation'. In the 'Source' field, set the formula:
=OFFSET($K$2;;;COUNTIF($K$2:$K$11;"?*"))

Third dependent drop-down menu (two conditions)

In the picture below, see what the third validation list is 2nd dependent drop-down menu and looks like, which we set up using the 'Validation List'. The items/data in this list belong to the conditions of 'E2' and 'F2' cells and the result is in the 'C' column. Therefore, only those data in the same row that contains the data "Truck" in the 'A' column and the data 'John' in the 'B' column are shown. Also notice here that there are no blank items in the menu (so no empty cells from the 'L' column are displayed.

The drop-down menu depends on two criterion

 

In the 'G2' cell, set the formula for the third drop-down menu. Select the cell and then click on the 'Data Validation' button command. In the 'Source' field, set the formula:
=OFFSET($K$2;;;COUNTIF($K$2:$K$11;"?*"))

Ultimately, we created three drop-down lists, two of which are dependent drop-down menus. And it looks like the picture below.

Three drop-down menus but two dependent drop-down lists with distinct unique data with no empty cells

 

If you want to use 'Conditional Formatting' to highlight rows that match the conditions (as pictured above) then use this formula for the entire cell range 'A2: E23'.
=AND($E$2=$A2;$F$2=$B2)

Automatically delete an item from the drop-down menu if we change the previous data

During work, there is a need to change certain data. In this case, if you change the data in the 'E2' cell, the other data will not change automatically. So in the 'E2' cell if you replace the 'Truck' with the new 'Car' selection, the other data in the 'F2 and G2' cells will remain unchanged. This is a worrying situation and is not good as you may forget to change this information during operation. A VBA macro can help us here, which will automatically delete the data in the 'F2 and G2' cells if we change the data in the 'E2' cell. Copy this VBA macro below into the 'Worksheet modules' of your workbook. Right-click the Sheet Tab and then copy this VBA code below to 'Window Code'. Note, change the name of your worksheet in the source code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E2")) Is Nothing Then
Sheets("Sheet1").Range("F2:G2").Value = ""
End If
If Not Intersect(Target, Range("F2")) Is Nothing Then
Sheets("Sheet1").Range("G2").Value = ""
End If
End Sub

Note: If you are using this VBA code then be sure to save your workbook in XLS, XLSM or XLSB format.

Tutorials related to the drop-down menu or Validation Lists

Download the file (in ZIP format) in which I did this tutorial can be downloaded to your computer at the link below
Two dependent drop-down menus with unique data without blank cells.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.