Solved

Obtain the drop-down filter list in Excel

Posted on 2011-03-17
10
264 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 42

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 42

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

749 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