Software optimize

How do I... return a unique list in Excel?


This information is also available as a PDF download.

Lists work their way into just about everything we do. Ordinarily, you might not think of worksheet data as a list, but that data can quickly turn into one, depending on your needs. That need becomes a bit more complicated if you want to reduce the data to a unique list. Fortunately, Excel's filter feature can create a quick list of existing data and return only unique items. You can create the list manually or automate the task using VBA.

Manually, the easy way

With just a few clicks, Excel's Data feature can create a unique list from a list of values. The short list of order information in Figure A is from the Access sample file Northwind.mdb -- specifically, it's from the Order Details table. Notice that column H contains a unique list of order numbers from column A. To create the unique list, follow this simple three-step process:

  1. Select the first cell in the source list. In this case, that's cell A1.
  2. Choose Filter from the Data menu. Then, select Advanced Filter from the submenu. Excel 2007 users will find the Filter options on the Data tab in the Sort And Filter group.
  3. In the Advanced Filter dialog box, click the Copy To Another Location option. Fill in the Copy To range. A single cell, such as H1 is adequate; Excel interprets a single cell as the top cell in the resulting list. Check the Unique Records Only option, as shown in Figure B, and then click OK.

Figure A: It's easy to create a unique list from a list of repeated values. Figure B: Click the Unique Records Only option in the Advanced Filter dialog box. If you need a quick list and it doesn't matter where the list is, use the built-in feature. However, if you want to use the list in some way, the quick way might not be adequate. Suppose you want to display the unique list in a combo box, as shown in Figure C. To do so, you could set the combo box control's Row Source property to the appropriate range. In this case, that's H2:H7, as shown in Figure D.

Figure C: This combo control contains a unique list based on the data in column A.

Figure D: Set the combo control's Row Source property.

If you know that the list will never change, this solution works. Execute it one time and move on. However, this approach isn't practical if the source data changes.

VBA, the automated way

A source list that changes creates a special problem. Creating a new list of unique items isn't hard, but you don't want to update the combo control's Row Source property every time the original list changes. In this case, it's best to automate the entire process. To do so, we'll use VBA's AdvancedFilter method.

First, create two ranges: the list's source data and a target range for the unique list. Select the source data; in this case, that's cells A2:A18. From the Insert menu, choose Name and then select Define. Excel will automatically assume the content of A1, the string OrderID, as the name. Click OK. Next, select cell H1 and name it UniqueList using the same process.

Next, you need a user form and a combo box. Press Alt+F11 to launch the Visual Basic Editor (VBE). From the Insert menu, choose UserForm. If necessary, click the Toolbox button to launch the Toolbox and drag a combo box control to the user form. Name the combo box control cboUniqueList. Save the user form as UserForm1.

Now, you're ready to enter the code that automates the list. Choose Module from the Insert menu. Then, enter the function in Listing A. This function creates a unique list from the data in the OrderID named range, populates cboUniqueList with that list, displays UserForm1, and then deletes the unique list from the sheet. Listing A
Function UniqueList()

'Populate control with

'unique list.

Range("OrderID").AdvancedFilter Action:=xlFilterCopy, _

CopyToRange:=Range("UniqueList"), Unique:=True

'Set combo control's Row Source property.

Range("UniqueList").Activate

UserForm1.cboUniqueList.RowSource = Selection.CurrentRegion.Address

'Display user form.

UserForm1.Show

Selection.CurrentRegion.Clear

End Function

The AdvancedFilter method automates the feature discussed in the previous section. This method uses the following syntax:

range.AdvancedFilter(action, criteriarange,copytorange, unique)
where range is a range object. Table A lists the method's other arguments. After creating the list, the code sets the combo box control's Row Source property to the unique list in column H and then opens the user form with a populated combo control (Figure C). Table A

Argument Explanation
action Required constant: xlFilterCopy or xlFilterInPlace. Both are self-explanatory. If you create the list in place, Excel doesn't delete items, it simply hides them.
criteriarange Optional variant that defines criteria used to filter the list.
copytorange Optional variant that specifies where VBA copies the list.
unique Optional variant that determines if the list contains only unique values. The default value is False.

A few noteworthy points

Because the code relies on the CurrentRegion property, be sure to locate the unique list in an out-of-the-way place where there's no chance that the resulting list will run into existing data. Avoid giving the unique list's range name more than one cell unless you know the exact size of the resulting list. The AdvancedFilter method's copytorange needs only one cell. If the source list grows, you'll need to update the named range's dimensions to include new items before running the function. A shrinking list doesn't present a problem. The code doesn't sort the list, but you could add that capability to the code. The sample code contains no error handling, so be sure to test it thoroughly within your application and accommodate potential errors.


Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@setel.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

6 comments
Rick_In-Tas
Rick_In-Tas

Thank you for the article.....I replicated all of the above in a spreadsheet.  How do you call the function?

mivatester
mivatester

Thank you for the article, it was very informative. Question: Are there ways to filter the unique list other then checking the unique records only box? For example, could I create a unique list based on cells that have a background or font of a certain color? How about if a cell contains a certain word or number?

write2sankari
write2sankari

I want to create a unique list in a combo box. I did what was told in this article. as you can see in the 3rd row, only two items should be displayed, d1, and d2, Projects Division projects divisions unique list p1 d2 d2 p2 d2 d2 p3 d2 d1 p4 d1 p5 d1

ja_loft
ja_loft

I have tried several of your aticles to print normaly and then decided to compare them printed as "PDFs." None would work with a brief statement that the key was wrong and I should try REPAIR. Can you help a "curious george" and test your PDF printing scheme? thanks, jak