Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Obtain the drop-down filter list in Excel

Posted on 2011-03-17
10
Medium Priority
?
273 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 668 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 668 total points
ID: 35164719
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 664 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

618 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