Solved

Obtain the drop-down filter list in Excel

Posted on 2011-03-17
10
261 Views
Last Modified: 2012-05-11
Hello,

Is there a way to isolate or obtain the drop-down filter list in Excel (2007)?

When using the standard Filter function, a small GUI button appears at the top of the column to be filtered.  Clicking the button causes a list to drop-down which displays every unique entry in that particular column.  However, unlike the column itself in which a given entry can be entered repeatedly, the drop-down list displays each unique entry only once.  

I can think of several situations in which having that type of list easily attainable, would be very helpful.  However, does anyone know a quick way to create it?

Thanks
0
Comment
Question by:Steve_Brady
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35161317
You cannot access that list, unfortunately. You have to iterate the column and create a unique list (using a Collection or Dictionary is simplest) yourself.
0
 
LVL 1

Expert Comment

by:TerrySolanen
ID: 35161456
Your question can be interpretted a few different ways.  I think rorya is looking at the question from a programmer's perspective.

Another way of looking at it is this scenario--you've just recieved a big workbook, with lots of new data.  You'd like to get a handle on what all the different possible values are.  You'd like to narrow a list of, say, 10K records down to the 50 or so unique values that make up the contents of the column.

If the above scenario is what you are thinking of, here's how you can do that.  These instructions are for Excel 2010, but it's very similar to Excel 2003...so I expect it's about the same in Excel 2007.

In your worksheet, from the Ribbon, click Data>Advanced.  A window called "Advanced Filter" should appear.  In the window, do this:
-- select the "Copy to another location"
--for the "List Range", select the column of data you are interested in getting unique values for
--Check "Unique records only"
--In the "Copy to" field, select the location you'd like to see the unique values listed.  (It must be on the same worksheet.)

The results of this should be a list of unique values from the column of data you specified.

Is that what you are after?
0
 

Author Comment

by:Steve_Brady
ID: 35161521
Actually, the solution just dawned on me:

1)  Copy the column from which the unique list is desired.

2)  Paste it in some empty, out-of-the-way column.

3)  Select it and click Data > Remove Duplicates.
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:Steve_Brady
ID: 35161547
Thanks for the responses.

TerrySolanen:
>>--Check "Unique records only"


That is a good trick to know but wouldn't it exclude any entries which are not unique?
0
 
LVL 1

Accepted Solution

by:
TerrySolanen earned 167 total points
ID: 35161627
Hi Steve,

Yes, the solution you outlined would work as well.  I migrated from Excel 2003 directly to Excel 2010, and I wasn't certain when they introduced the "Removed Duplicates" feature.

Both the solutions (the one you described; the one I described) will get you to the same result.

Using the "Remove Duplicates" feature allows you the flexibility of moving the list directly to another sheet, though.  


Regarding your follow-up question: Here's a simple example of the results the "Unique Records Only" method gives.  Let's say you have a list of 3 colors:
COLOR
yellow
red
red

The Unique list method would return
COLOR
yellow
red
0
 

Author Comment

by:Steve_Brady
ID: 35161643
rorya:
>>iterate the column and create a unique list (using a Collection or Dictionary is simplest)...


I am not familiar with either of those terms in the context of Excel.  Can you provide a brief explanation or suggestion regarding where to read about them?
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 167 total points
ID: 35164719
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 166 total points
ID: 35174823
Steve - would it be useful to have a feature where you can place a dropdown list (my example would be with a combo box active-x control) on the worksheet, link it to a range somewhere, and then have it show unique values when you dropped down the little filter icon on the combo box?  And also sustain that linkage if the data were to change?

Let me know - I got an idea for this utility but don't want to post if I would be just going off the deep end and you were just trying to create a quick unique list...

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35306215
Steve - your posting was the inspiration for this:


http:/A_5062.html


This article will help you do what you're looking for with a ComboBox.

I'm working on a followup that will do this for ListBox and Data Validation Lists as well - probably post as a Version 2.0 in a few weeks - PS - if you like this and find the article helpful, I can send you the Data Validation one earlier if you'd like to be an "early user"...

Anyway, enjoy!

Dave
0
 

Author Closing Comment

by:Steve_Brady
ID: 35420660
Thanks!
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question