Solved

Obtain the drop-down filter list in Excel

Posted on 2011-03-17
10
258 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now