Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


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

Easily select and highlight all unique values from a column 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 except the first one, as well as duplicate values as you need as below screenshot shown.Download the full feature 60-day free trail of Kutools for Excel now!

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!

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 formula =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") (B2:B9 is the column data which you want to extract the unique values, D1 is the above cell where your formula is located) into the Formula Bar, and then press the Ctrl + Shift + Enter keys simultaneously. See screenshot:

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
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.

Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:

Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:

Click for free trial of Office Tab!

Office Tab for Excel

Related articles:

Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Alexis · 2 months ago
    Hi, thank you for your help.
    Now, how can I have my values also sorted alphabetically? (I do not want to use the filter on my master table)
    Should I use a COUNTIFS instead of COUNTIF?
    Please HELP
    • To post as a guest, your comment is unpublished.
      crystal · 25 days ago
      Hi Alexis,
      Sorry can't hep to sort the extracted value alphabetically at the same time with formula. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Charley · 5 months ago
    I am trying to drag the formula down past my actual data so that I can input different sized data sets and not have to adjust anything. However, the last row after my actual data ends always returns a "0". I am using the unique values for something else in an adjacent column, and the 0 causes the last value to repeated (When I delete the 0 the value is no longer repeated). Any idea how to fix this? Also I am using Office 365 Business
  • To post as a guest, your comment is unpublished.
    Steve Parker · 7 months ago
    If I do this for a thousand row excel sheet on the latest version of Excel on a Mac, it never returns. The first row works, but when I duplicate down, excel goes into a compute mode that has not returned values for over two hours now.

    Any thoughts on how to do this for large lists (up to 2k rows) that will return 50 or 60 unique values?

    I mocked this up in the "Numbers" app, and it's working perfectly there, taking only a couple minutes to calculate. It's just taking so long in Excel that I wonder if it will ever complete. I plan on letting it "run" overnight to see what will happen.
    • To post as a guest, your comment is unpublished.
      Josh · 3 months ago
      Check your Calculating options. It needs to be set to automatic. File > Options > Formulas > Calculating options > Workbook Calculation (Automatic selection)
  • To post as a guest, your comment is unpublished.
    Matt · 10 months ago
    How would you add a second variable? For instance, I want all the unique elements in one column that also share a similar value in another column. In your example, imagine a 3rd column titled "Department" that would have values like product, meat, etc. I realize those are all Produce, but hopefully you get my point. Would you modify the CountIF formula to a COUNTIFS or do you modify it in another way?
    • To post as a guest, your comment is unpublished.
      crystal · 6 months ago
      Hi Matt
      Please try this formula =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Yes","").
      Supposing the two compared lists are Column A and Column C, If the unique values stay only in Column A but not in Column C, it will be displayed Yes in column B; while if return nothing in Column B, it means corresponding value stays in both Column A and Column C.
      • To post as a guest, your comment is unpublished.
        jyotiba mali · 5 months ago
        Thanks for the reply.. but hot to pull out that unique value if it displayed YES.. could you please advice me the formula to pull the unique value in different column.
  • To post as a guest, your comment is unpublished.
    Zac · 1 years ago
    How would you add multiple criteria, such as if you only wanted to add to the dynamic list if the date was just 9/12?

    I'm trying "&" in the MATCH formula, but it's not working.

    For example, based on your example:
    =IFERROR(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"")
    This throws an error or creates duplicates.

    Alternatively, I've read that "+" might work, although I can't get it working. Or using SMALL.