Get An Array Of Unique Values From A List

April 07, 2021 - by Bill Jelen

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:

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:

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:

The Dictionary Object Approach

The code for the Dictionary object approach is:

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:

  

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

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.