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.
Easily select and highlight all unique values in a range in excel:
The Select Duplicate & Unique Cells utility of Kutools for Excel can help you easily select and highlight all unique values (include the first duplicates) or the unique values that appear only once, as well as duplicate values as you need as below screenshot shown.
Download Kutools for Excel now! (30-day free trail)
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 SubNote: 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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
