How to dynamically extract a list of unique values from a column range in Excel?

For a column range which the values are changing regularly, and you always need to get all the unique values from the range no matter how it changed. How to make a dynamical list of unique values? This article will show you how to deal with it.

Dynamically extract a list of unique values from a column range with formula
Dynamically extract a list of unique values from a column range with VBA code

Dynamically extract a list of unique values from a column range with formula

As below screenshot shown, you need to dynamically extract a list of unique values from range B2:B9. Please try the following array formula.

1. Select a blank cell such as D2, enter the below formula into it and press the Ctrl + Shift + Enter keys simultaneously. (B2:B9 is the column data which you want to extract the unique values, D1 is the above cell where your formula is located)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

2. Keep selecting cell D2, then drag the Fill Handle down to get all unique values from the specified range.

Now all unique values in column range B2:B9 are extracted. When values in this range changed, the unique value list will be dynamically changed immediately.

Dynamically extract a list of unique values from a column range with VBA code

You can also extract a list of unique values dynamically from a column range with the following VBA code.

1. Press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the Module window.

VBA code: Extract a list of unique values from a range

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

Note: In the code, D2 is the cell you will locate the unique value list. You can change it as you need.

3. Go back to the worksheet, click Insert > Shapes > Rectangle. See screenshot:

4. Draw a rectangle in your worksheet, then enter some words you need to display on it. Then right click it and select Assign Macro from the right-clicking menu. In the Assign Macro dialog box, select the CreateUniqueList in the Macro name box, and then click the OK button. See screenshot:

5. Now click on the rectangle button, a Kutools for Excel dialog box pops up, please select the range contains unique values you need to extract, and then click the OK button.

From now on, you can repeat the above step 5 to update the unique value list automatically.

Related articles:

• How to create a dynamic monthly calendar in Excel?
• How to insert image or picture dynamically in cell based on cell value in Excel?
• How to create dynamic hyperlink to another sheet in Excel?