[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

Obtain the drop-down filter list in Excel

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
Steve_Brady
Asked:
Steve_Brady
  • 4
  • 2
  • 2
  • +1
3 Solutions
 
Rory ArchibaldCommented:
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
 
TerrySolanenCommented:
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
 
Steve_BradyAuthor Commented:
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
Hire Technology Freelancers with Gigs

Work with 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.

 
Steve_BradyAuthor Commented:
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
 
TerrySolanenCommented:
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
 
Steve_BradyAuthor Commented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
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
 
Steve_BradyAuthor Commented:
Thanks!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now