Two ways to return a unique list in Excel
Thursday, April 16, 2009 12:28 PM
Filtering data to produce a unique list of entries is a common task in Excel. Here are two methods of getting the job done: manually and with a bit of VBA automation.
Microsoft Excel
Two ways to return a unique list in Excel
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.
Figure A
To create the unique list, follow this simple three-step process:
- Select the first cell in the source list. In this case, that's cell A1.
- 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.
- 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 B
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
Figure D
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.
Microsoft Word
Lost your top and bottom margins?
In Print Layout view, Word displays a “sheet of paper.” Each onscreen border represents the edge of a single sheet. Sometimes, the top and bottom margins seem to disappear.
This isn't a phantom gremlin; it's actually an obscure feature that's a bit too easy to engage. Word lets you hide the white space (including the headers and footers) at the top and bottom of a page, thereby fitting more content on the screen.
It's a helpful feature if you need it, but if you don't know it exists when your margins suddenly disappear, you might feel a bit lost. You won't know why the margins are gone or how to get them back.
When you move the mouse pointer to the top or bottom edge of the page, Word changes the mouse pointer to a small icon with two arrows pointing toward each other (up and down). If you click that icon, Word hides the white space at the top and bottom of the page. It only seems that Word removes the top and bottom margins.
The good news is that it's just as easy to reclaim the white space as it was to lose it. Hover the mouse pointer over the top or bottom edge of the paper and click the double-arrow icon again. The icon toggles the white space. Word 2007 has the same feature, but you must double-click the icon, which makes it a bit harder to accidentally engage the feature .
Word displays the white space by default, which is how most of us work. You can change that behavior if you like. From the Tools menu, choose Options and then click the View tab. Uncheck the White Space Between Pages (Print View Only) option in the Print And Web Layout Options section. Doing so will hide the white space by default. The icon will still work to toggle between display settings. In Word 2007, click the Office button and then click Word Options. Click Display in the left pane, check or uncheck Show White Space Between Pages In Print Layout View, and click OK.
Microsoft Access
Display a specific form when your Access 2007 database is opened
You don't want your users navigating your Access 2007 database on their own. Instead, when they open the database, you want them to access the data, reports, and queries through controls on the opening form--not the navigation pane.
Follow these steps:
- Open the database.
- Click the Office button and then click the Access Options button.
- Select Current Database from the left pane.
- Under Applications Options, click the Display Form drop-down list and select the form you want to display when the database is opened.
- Under Navigation, clear the Display Navigation Pane check box (Figure A) and click OK.
Figure A
Now, when users open your Access database, they will see only the selected form in the Display Form box. The navigation bar will not be visible.
There are currently no comments for this post.