MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Get An Array Of Unique Values From A List


April 07, 2021 - by Bill Jelen

Get An Array Of Unique Values From A List

Challenge: You want to extract all unique values from a column of text data that may contain several instances of a particular value. A procedure like this is useful when you need to populate a list box or combo box with unique values for user selection.

Solution: Assume that your spreadsheet contains a list of names in the range A2:A30 on Sheet1. Cell A1 contains the header Name.


You can manually solve this problem by selecting Data, Filter, Advanced Filter dialog.

In the Advanced Filter dialog, select Copy to Another Location, set List Range to Sheet1!$A$1:$A$30, leave Criteria Range blank, set Copy To to $H$1, select Unique Records Only, and click OK.

The list of unique names (with the header Name) is pasted at H1.

After it is pasted, you can sort the list in alphabetical order, if required.

Alternate Solution: This topic demonstrates two approaches for obtaining the unique values: one using the Collection object and the other using the Dictionary object. The two approaches are similar in mechanism in that they make use of the fact that a collection, as well as a dictionary, cannot contain duplicates.

The Collection Object Approach

The code for the Collection object approach is:

e9781615474011_i0137.jpg

This code creates a new collection, UniqColl, and cycles through all the values in the list of names, attempting to add each name to the collection. Notice that the statement:

e9781615474011_i0138.jpg

contains two references to cell.Value. This is because the first two arguments for the Add method are Value and Key. A collection cannot contain duplicate Key values. Error trapping during the execution of the For...Next loop is disabled, using On Error Resume Next, so whenever the code encounters a duplicate value (which, if already present in the collection, cannot be added to it), it simply skips to the next cell, without screeching to a halt with an error message.

When all the items are added to the collection, the code creates an array UniqArray of the same size as the collection UniqColl.Count and adds each item of the collection to this array. The array is needed to transfer the contents to the spreadsheet. Notice the use of the Transpose function when transferring the array to a column in the sheet; this is needed because UniqArray is a horizontal array.

Before transferring the unique list to the worksheet or control, it may be desirable to sort the data alphabetically. To do this, simply insert the following code after the comment ‘Optional sort routine can be inserted here:

e9781615474011_i0139.jpg

The Dictionary Object Approach

The code for the Dictionary object approach is:

e9781615474011_i0140.jpg
e9781615474011_i0141.jpg

This code works similarly to the previous routine. The points of difference are:

  • Instead of disabling error trapping, the Exists property of the Dictionary object is used to decide whether a value has already been added to the dictionary and needs to be skipped.
  • The dictionary contents are transferred to UniqArray in one go with:
    e9781615474011_i0142.jpg

Instead of transferring the unique items to the worksheet, you can fill a combo box with the values, using a statement like:

e9781615474011_i0143.jpg

Summary: You can extract a list of unique values from a large list that may contain multiple instances of a given value. The extracted values can be put into a worksheet range or used to fill values in a combo box or list box.

Sources: load only one instance(no duplicates), remove duplicates from combo box, and to find unique values from a column on the MrExcel Message Board.

Title Photo: Ricardo Gomez Angel on Unsplash


This article is an excerpt from Excel Gurus Gone Wild.



Bill Jelen is the author / co-author of:

MrExcel 2021 – Unmasking Excel

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.